#!/usr/bin/perl

# ------------------------------------------------------------------------
# coldarch -- cold backup for Microsoft
#             G. Patterson, June 2002

# WARNING  ** This script SHUTS DOWN THE DATABASE! ********
#             This is the only warning!

# Set the common parameters (contained in common.pl)
use File::Basename;
# extract the path from $0
$MyPath = dirname($0);
# This file contains important site definitions
require "$MyPath\\common.pl";
# This determines how the database will be re-opened
$OPEN_TYPE = 0;		# 0 = Don't open, 1 = oradim, 2 = svrmgrl, 3 = both

# ------------------------------------------------------------------------

# Write a log message to the logfile
sub LogMsg{
	my @t = localtime(time);
	printf LOG "%04d-%02d-%02d %02d:%02d %s\n",
		$t[5]+1900,$t[4]+1,$t[3],$t[2],$t[1],$_[0];
}

# ------------------------------------------------------------------------

# We have a problem ... cleanup and exit
sub problem{
	LogMsg "ERROR: $_[0]";
	unlink "$lockfile";
	unlink "$stopfile";
	# hopefully we can start the database
	opendb();
	die "$_[0]\n";
}

# ------------------------------------------------------------------------

# Run the given SQL and spool to temp.LST, because we
# cannot redirect output from version 7 sqlplus in NT
sub run_sql {
	# first,  write the given SQL to a temp file
	open SQL,">$sqltmp" || die "cannot open $sqltmp";
	print SQL "set term off\n";
	print SQL "set echo off\n";
	print SQL "set trimspool on\n";
	print SQL "set pages 0\n";
	print SQL "set feedback off\n";
	print SQL "set verify off\n";
	print SQL "set lines 1024\n";
	print SQL "spool $tmpdir\\temp\n";
	print SQL "$_[0]\n";
	print SQL "exit\n";
	close SQL || die "$!";
	# now run it with the SQLPLUS utility
	system "$sqlplus -s $userid/$passwd\@$orasid \@$sqltmp";
	# read back the spool file and return the contents in list context
	open (TMP,"$tmpdir\\temp.LST") || problem "opening temp.LST";
	my @t = (<TMP>);
	close TMP || problem "closing temp.LST";
	return( @t);
}

# ------------------------------------------------------------------------

# Run the given SQL in svrmgrl
sub run_svrmgr {
	# construct an SQL file
	open SQL,">$sqltmp" || die "cannot open $sqltmp";
	print SQL "connect internal/$passwd\n";
	print SQL "$_[0]\n";
	print SQL "exit\n";
	close SQL || die "$!";
	# EXEC as a backticks block and return the output in list context
	my @t = `$svrmgrl \@$sqltmp`;
	return( @t);
}

# ------------------------------------------------------------------------

# get list of redo logs from v_$logfile
sub get_redo_info{
	# this should return a list of the redo logs
	my @t = run_sql "select member from v_\$logfile;";
	foreach $x (@t){
		# remove any crud from the end of the lines
		$x =~ s/\s+$//;
		push (@REDOLIST,$x) if (length($x) )
	}
}

# ------------------------------------------------------------------------

# shutdown the database and get a copy of SQL to create control file
sub shutdb{
	my $t; my $f; my $x; my $a;
	# run a BACKUP TO TRACE command
	my $backup_ctrl = "$coldarc\\" . uc($orasid) . "_CTRL.FILE.trc";
	my @t = run_svrmgr "alter system switch logfile;
		alter database backup controlfile to trace resetlogs;";
	@t = run_svrmgr "shutdown immediate;";
	LogMsg $t = `$oradim -shutdown -sid $orasid -usrpwd $passwd -shuttype inst,srvc -shutmode i`;
	# examine the files in USER_DUMP_DEST
	opendir (TRACE,$ORAPARM{user_dump_dest}) ||
		problem "Cannot open $ORAPARM{user_dump_dest}";
	$a = 99;
	# we want the most recent trace file ...
	while ($t = readdir TRACE){
		$x = $ORAPARM{user_dump_dest} . "\\" . $t;	
		next unless ( -f $x);
		next unless ( (-M $x ) < $a);
		$f = $x;
		$a = ( -M $x )
	}
	# save the trace file for later (the coldclone procedure)
	rename $f,$backup_ctrl || problem "Cannot move $f to $backup_ctrl";
}

# ------------------------------------------------------------------------

# retrieve parameter values from v$parameter
sub get_oraparm{
	# extract the parameters from v$parameter
	my @t = run_sql "col name format a40 trunc\ncol value format a512 trunc
		select name,value from v\$parameter;";
	for my $i ( 0 .. $#t){
		$t[$i] =~ s/\s+$//;
		# parse them into %ORAPARM (using parameter as a hash key)
		if ( $t[$i] =~ /^(\w+)/){
			$ORAPARM{$1} = $';
			$ORAPARM{$1} =~ s/^\s+//;
		}
	}
}

# ------------------------------------------------------------------------

# Get the list of datafiles to be backed up
sub get_datafiles{
	my @t = run_sql "select tablespace_name, file_name, blocks+1
		from dba_data_files where status = 'AVAILABLE'
		order by tablespace_name, file_name;";
	my $j = 0;
	for my $i ( 0 .. $#t){
		chomp($t[$i]);
		my @s = split ' ',$t[$i];
		if (@s == 3){
			$DATAFILE[$j] = \@s;
			my @b = split /\\/,$DATAFILE[$j][1];
			$DATAFILE[$j++][3] = $b[$#b];
		}
		elsif (@s > 3){
			# spit the dummy if filename contains embedded spaces
			problem "bad file: $s[1] $s[2]";
		}
	}
	problem "no data files available" unless (@DATAFILE > 0);
	LogMsg sprintf "$MyName found %d data files to backup",@DATAFILE+0;
}

# ------------------------------------------------------------------------

# Create a header file.
# This is a text file that contains information about the cold archive
# which can be used by scripts that restore from the cold archive.
sub write_header{
	my $x;
	# gather all the necessary information
	get_redo_info();
	get_datafiles();
	@CTRLFILES=split /, /,$ORAPARM{control_files};
	# clobber previous backup
	unlink glob "$coldarc\\*";
	open (HDR,">$coldarc\\0header") || die "Cannot open $coldarc\\0header\n";
	# store the information in the header file
	print HDR "$MyName started at $start_str
 ORACLE_SID: $orasid
ORACLE_HOME: $ENV{ORACLE_HOME}
 BLOCK_SIZE: $ORAPARM{db_block_size}
 PARAM_FILE: $pfile{$orasid}\n";
	if ($ORAPARM{ifile}){
		print HDR "    I FILES:\n";
		foreach $x(split (/, /,$ORAPARM{ifile})){
			print HDR "\t$x\n";
		}
	}
	print HDR " CTRL FILES:\n";
	foreach $x (@CTRLFILES){
		print HDR "\t$x\n";
	}
}

# ------------------------------------------------------------------------

# create SQL for a backup control (backup to trace)
sub backup_control_file{
	my $backup_ctrl = "$coldarc\\" . uc($orasid) . "_CTRL.FILE";
	# clobber any old versions of this file
	unlink "$backup_ctrl";
	my @t = run_sql "set feedback on
		alter database backup controlfile to '$backup_ctrl';";
	problem "Cannot backup controlfile"
		unless ( grep(/Database altered$/i,@t) );
}


# ------------------------------------------------------------------------

# use the $zipview command to verify that the backup is successful
# this reads through the contents of the zip archive and checks
# that the sizes match specified system size.
# NB: This may vary from site to site. If you have actually created
# a data file that is larger than the allocated size in the database
# this check should be disabled.
sub verify_backup{
	$blksz = $ORAPARM{db_block_size};
	for my $i ( 0 .. $#DATAFILE){
		my @t = `$zipview $coldarc\\$DATAFILE[$i][3].zip`;
		foreach my $x (@t){
			$x =~ s/\s+$//;
			if ($x =~ /$DATAFILE[$i][3]$/){
				$size = (split ' ',$x)[0];
				problem "suspect backup $DATAFILE[$i][3].zip"
				unless ($size == $DATAFILE[$i][2] * $blksz);
			}
		}
	}
}

# ------------------------------------------------------------------------

#make the output from the zipadd more pretty
sub pretty {
	my $t = $_[0];
	$t =~ s/Deflating     .*%\), done./Deflated/;
	return($t);
}

# ------------------------------------------------------------------------

# backup the control_file, redo logs, pfiles and ifiles
sub backup_config(){
	my $x; my $b;
	my $dest = "$coldarc\\config.$orasid.zip";
	# first copy the pfile to the archive directory
	my $t = `copy $pfile{$orasid} $coldarc`;
	$t =~ s/\s+$//;
	problem "pfile: $t" unless ($t =~ /1 file\(s\) copied/);
	# add the control_file to the zip archive
	LogMsg pretty $t = `$zipadd $dest $CTRLFILES[0]`;
	foreach my $x (@REDOLIST){
		LogMsg pretty $t = `$zipadd $dest $x`;	
	}
	# copy each of the ifiles to the archive directory
	foreach $x(split (/, /,$ORAPARM{ifile})){
		$b = basename( $x);
		problem "Duplicate ifile: $b" if ( -f "$coldarc\\$b");
		$t = `copy $x $coldarc`;
		$t =~ s/\s+$//;
		problem "ifile: $t" unless ($t =~ /1 file\(s\) copied/);
	}
}

# ------------------------------------------------------------------------

# reopen database -- called from problem(), so we must not call problem()
sub opendb{
	LogMsg "WARNING: Database is shutdown!" unless $OPEN_TYPE;
	LogMsg $t =
	`$oradim -startup -sid $orasid -usrpwd $passwd -starttype srvc,inst`
		if ( $OPEN_TYPE & 1);
	run_svrmgr "startup" if ( $OPEN_TYPE & 2);
}

# ------------------------------------------------------------------------

# Main ... start here:
# which program am I?
$MyName = basename $0,"\.pl";
# sanity check for command line
die "usage: $MyName intrnl_passwd db_name\n" unless (@ARGV == 2);
$userid = "sys";
$passwd = $ARGV[0];
$orasid = $ARGV[1];
# check the environment
chk_env();
# init strings for logfile
@start = localtime(time);
$start_str = sprintf "%04d-%02d-%02d %02d:%02d:%02d",$start[5]+1900,
		$start[4]+1,$start[3],$start[2],$start[1],$start[0];
$logfile = sprintf "%s\\%s_%s_%02d%02d%02d.log",$logdir,$orasid,$MyName,
		$start[5]%100,$start[4]+1,$start[3];
$lockfile = "$logdir\\coldarch.$orasid.lock";
$stopfile = "$logdir\\coldarch.$orasid.stop";
die "$MyName is already running\n" if ((-f $lockfile) && (-M $lockfile < 1));
# create the lockfile
open (LOCK,">$lockfile") || die "Cannot write to $lockfile";
print LOCK "$MyName started $start_str\n";
close (LOCK) || die "$!";
open(LOG,">>$logfile") || die "error opening $logfile\n";
get_oraparm();
problem "$ORAPARM{db_name} does not match $orasid"
	unless (uc($ORAPARM{db_name}) eq uc($orasid));
$coldarc = "$coldarc\\$ORAPARM{db_name}";
# setup the header
write_header();
# shutdown the database, backup control_file, pfile, ifiles
shutdb();
# write information about datafiles to the HEADER
print HDR "\n DATA FILES:\n";
for my $i ( 0 .. $#DATAFILE){
	# loop through the datafiles
	problem "$MyName Stopped!" if ( -f $stopfile);
	sleep(10);
	LogMsg "copying $DATAFILE[$i][1], $DATAFILE[$i][2] blocks";
	print HDR "\t$DATAFILE[$i][1] $DATAFILE[$i][2] blocks\n";
	LogMsg pretty $t = `$zipadd $coldarc\\$DATAFILE[$i][3].zip $DATAFILE[$i][1]`;
}
backup_config();
opendb();
LogMsg "$MyName completed normal";
unlink "$lockfile";