Hot Backup Of Oracle Database With perl

Hot Backup of a Microsoft Oracle Server with Perl

By Gerry Patterson

Writing Your Own Backup Script

Ok, some people would never dream of doing the mechanical repairs on their own car or removing their own appendix. Similarly it might be argued. Why would anyone want to write their own backup script for Oracle? This is especially true for administrators who have grown up with Microsoft systems, and inherited a click and point mentality. There is a considerable amount of existing software that handles the job for you, including software from Oracle. However there are good reasons to write your own Oracle backup:

Education. Writing your own script increases your understanding of how the hot backup process works. This would have to be the most compelling reason to attempt writing and practice using backup scripts, even if you don't intend to use them on your production system. It is possible to schedule and plan for many things in IT. Disasters, however are always unexpected. That's what makes them disasters. And when disaster strikes you may encounter problems during the recovery which are just as unexpected. A disaster following on the heels of disaster can be a real disaster! And it may call for some remedial action. If you had written the procedures yourself, had practiced recovering from them and you understood them, you would be able to make informed decisions and be better equipped to navigate your way through a crisis. If you have only a cook book approach to recovery and your particular disaster has its' own unique ingredients, your destination may be USC (Up the Proverbial Creek in a barbed-wire canoe without paddles).
Control. Writing your own scripts gives you the maximum control over your backup. This makes it easier to change the backup to suit changing circumstances at your site.
Cost. Depending on which backup software you choose, you may have to pay a license and/or maintenance fee. In most cases, this will not be a priority. The cost savings are minimal and if your organisation can afford to pay for an Oracle system on Windows 2000 Server, you will not be greatly concerned about the the cost of backup software. Nevertheless, if you have written your own backup procedure, you may as well save the cost of buying backup software.

Backup Script

This backup script creates a zipped archive of the database which is transferred to tape in a nightly backup. The reason for taking this approach was for simplicity. Most sites do their backup from a single backup server, and it is difficult to get batch software. If your site has enough disk space (the cheapest of all IT resources these days) then you need only reserve enough space to hold the backup archive. Of course, there is a caveat. In order to be useful, the backup should finish before the tape archive procedure gets around to archiving the zip files. On the other hand if your backup software allows the running of batch files and has a command line utility that allows writing to tape then this job could be changed to write to tape. Alternatively you may just opt for Oracle's RMAN utility.

It is assumed that a site will use more than one procedure, so common paths and procedures are defined in the common script. This is where the backup directory, log directory, temporary directory and compression or archive commands are defined. This particular example uses the pkzip commands in order to create a zip archive. The winzip command line will work just as well, however you need to download the winzip command line support add-on in order to use the winzip batch commands. The hotarch script contains a function LogMsg() which writes a log string to a log file that has a name in the format SID_basename_yymmdd.log in the the folder logdir. Where SID is the oracle SID of the instance, basename is the program name (in this case hotarch) and yymmdd is the date.

The code for the "common" script is below:

# Directories used by backup/restore
$tmpdir = "E:\\temp";
$sqltmp = "$tmpdir\\temp.sql";

# hot #######################
$logdir = "E:\\Oracle\\log";
$arcdir = "F:\\oracle\\backup";

# cold ######################
$coldarc = "E:\\Oracle\\coldstore";

# Add config data for Orcacle Instances here ...
# WORK Instance
$pfile{WORK} = 'E:\oracle\ora81\DATABASE\initWORK.ora';
$ORADIM{WORK} = 'E:\oracle\ora81\bin\ORADIM.EXE';
$SVRMGRL{WORK} = 'E:\oracle\ora81\bin\SVRMGRL.EXE';
$SQLPLUS{WORK} = 'E:\oracle\ora81\bin\SQLPLUS.EXE';
# TEST Instance
$pfile{TEST} = 'E:\oracle\ora81\DATABASE\initTEST.ora';
$ORADIM{TEST} = 'E:\oracle\ora81\bin\ORADIM.EXE';
$SVRMGRL{TEST} = 'E:\oracle\ora81\bin\SVRMGRL.EXE';
$SQLPLUS{TEST} = 'E:\oracle\ora81\bin\SQLPLUS.EXE';
# pgts Instance
$pfile{pgts} = 'E:\oracle\ora81\DATABASE\initPGTS.ora';
$ORADIM{pgts} = 'E:\oracle\ora81\bin\ORADIM.EXE';
$SVRMGRL{pgts} = 'E:\oracle\ora81\bin\SVRMGRL.EXE';
$SQLPLUS{pgts} = 'E:\oracle\ora81\bin\SQLPLUS.EXE';

# zip commands:
# The following is pkzip (vers 2.5)
# Also included are wzzip/wzunzip, which use old style pkzip commands
# Uncomment/comment these lines accordingly
$zipadd = "pkzip -add";
# $zipadd = "wzzip -a";
$zipmv  = "pkzip -add -move";
# $zipmv  = "wzzip -m";
$unzip  = "pkzip -extr -over=all";
# $unzip  = "wzunzip -o";
$zipview = "pkzip -view";
# $zipview = "wzzip -v";

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

sub chk_env{
        die "ORACLE_SID differs from $orasid\n"
                unless ($orasid eq $ENV{ORACLE_SID});
        die "No pfile specified for $orasid\n"
                unless ($pfile{$orasid});
        die "No oradim specified for $orasid\n"
                unless ($oradim = $ORADIM{$orasid});
        die "No svrmgrl specified for $orasid\n"
                unless ( $svrmgrl = $SVRMGRL{$orasid});
        die "No sqlplus specified for $orasid\n"
                unless ( $sqlplus = $SQLPLUS{$orasid});
}

The code for the "hotarch" script is below:

#!/usr/bin/perl

# ------------------------------------------------------------------------
# hotarch -- hotarchive backup for Microsoft
#            copied from ye olde shell script
#            G. Patterson, Nov 2001

# Set the common parameters (contained in common.pl)
use File::Basename;
$MyPath = dirname($0);
require "$MyPath\\common.pl";

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

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];
}

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

sub stop_backup{
        # my $sqltmp = "$tmpdir\\stop_backup.sql";
        open SQL,">$sqltmp";
        print SQL "set verify off\nset trimspool on\nset pages 0
        set feedback off
        select distinct 'alter tablespace ' || TABLESPACE_NAME || ' end backup;'
        from dba_data_files where FILE_ID in (
                select file# from v_\$backup where status = 'ACTIVE'
                );\nexit\n";
        my $t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        open SQL,">$sqltmp";
        print SQL "$t\nexit\n";
        $t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
}

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

sub problem{
# Err Houston? ... Houston ... We have a problem ...
        LogMsg "ERROR: $_[0]";
        # make a last ditch effort to end backup
        stop_backup();
        unlink "$lockfile";
        unlink "$stopfile";
        die "$_[0]\n";
}

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

sub get_redo_info{
# get list of redo logs from v_$logfile
        # my $sqltmp = "$tmpdir\\get_redo_info.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "set verify off\nset trimspool on\nset pages 0\nset feedback off
        select member from v_\$logfile;\nexit\n";
        my @t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        foreach $x (@t){
                $x =~ s/\s+$//;
                push (@REDOLIST,$x) if (length($x) )
        }
}

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

sub get_log_info{
# issue "archive log list" command from svrmgrl -- use sys as sysdba
        # my $sqltmp = "$tmpdir\\get_log_info.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "connect $userid/$passwd\@$orasid as sysdba
        archive log list\nexit\n";
        my @t = `svrmgrl \@$sqltmp`;
        # this is why we use sys as sysdba -- internal connects even if shutdown
        die grep (/ORA-/,@t) if ( grep /ORA-/,@t);
        for my $i ( 0 .. $#t){
                if ( $t[$i] =~ /^Connected\./){
                        for ( $i++;$i<=$#t-1;$i++){
                                chomp( $t[$i]);
                                if ( $t[$i] =~ /\s\s+/){
                                        $log_list{$`} = $';
                                }
                        }
                        return;
                }
        }
        problem "cannot retrieve archive log details";
}

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

sub get_oraparm{
# retrieve parameter values from v$parameter
        # my $sqltmp = "$tmpdir\\get_oraparm.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "set echo off\nset lines 600\nset trimspool on\nset pages 0\nset feedback off\ncol name format a40 trunc\ncol value format a512 trunc
        select name,value from v\$parameter;\nexit\n";
        my @t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        for my $i ( 0 .. $#t){
                $t[$i] =~ s/\s+$//;
                if ( $t[$i] =~ /^(\w+)/){
                        $ORAPARM{$1} = $';
                        $ORAPARM{$1} =~ s/^\s+//;
                }
        }
}

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

sub backup_mode{
# alter tablespace begin/end backup
        # my $sqltmp = "$tmpdir\\backup_mode.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "alter tablespace $_[0] $_[1] backup;\nexit\n";
        my $t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        $t =~ s/\s+$//;
        $t =~ s/\.$//;
        problem "cannot alter tablespace: $_[0]" unless
                ($t =~ /Tablespace altered$/i);
}

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

sub get_datafiles{
# Get the list of datafiles to be backed up
        # my $sqltmp = "$tmpdir\\get_datafiles.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "set pages 0\nset trimspool on\nset feedback off\nset lines 1024
        select tablespace_name, file_name, blocks+1
        from dba_data_files where status = 'AVAILABLE'
        order by tablespace_name, file_name;\nexit\n";
        my @t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        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;
}

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

sub write_header{
        get_redo_info();
        get_datafiles();
        @CTRLFILES=split /, /,$ORAPARM{control_files};
        open (HDR,">$arcdir\\0header") || die "Cannot open $arcdir\\0header\n";
        print HDR "$MyName started at $start_str
ORACLE_SID: $orasid
ORACLE_HOME: $ENV{ORACLE_HOME}
BLOCK_SIZE: $ORAPARM{db_block_size}
START_SEQNO: " . $log_list{"Current log sequence"}; print HDR "\n
CTRL FILES:\n";
        foreach $x (@CTRLFILES){
                print HDR "\t$x\n";
        }
}

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

sub backup_control_file{
# create a backup copy of the control file
        my $backup_ctrl = "$arcdir\\" . uc($orasid) . "_CTRL.FILE";
        unlink "$backup_ctrl";
        # my $sqltmp = "$tmpdir\\backup_control_file.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "alter database backup controlfile to '$backup_ctrl';\nexit\n";
        my $t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        $t =~ s/\s+$//;
        $t =~ s/\.$//;
        problem "Cannot backup controlfile" unless ($t =~ /Database altered$/i);
}

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

sub switch_log{
# switch_log files ... then wait for the archiver to catch up
        # my $sqltmp = "$tmpdir\\switch_log.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "alter system switch logfile;\nexit\n";
        my $t = `sqlplus -s $userid/$passwd\@$orasid \@$sqltmp`;
        $t =~ s/\s+$//;
        $t =~ s/\.$//;
        problem "Cannot switch log file" unless ($t =~ /System altered$/i);
        for ( get_log_info(),$c = $log_list{"Current log sequence"};
                 $log_list{"Next log sequence to archive"} < $c;
                 get_log_info()){
                 sleep 20;
        }
}

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

sub copy_log_files{
        my $i;
        my $next_log = $log_list{"Next log sequence to archive"};
        my $arczip = "$arcdir\\archivelog";
        unlink "$arczip";
        print HDR "\n  LOG FILES:\n";
        for ($i = $start_log_seq; $i < $next_log; $i++){
                @copy_log = glob sprintf $arch_fmt_str,$i;
                problem "Too many files: @copy_log\n" unless (@copy_log == 1);
                problem "No file: $copy_log[0]" unless ( -s $copy_log[0]);
                problem "$MyName Stopped!" if ( -f $stopfile);
                print HDR "\t$copy_log[0]\n";
                $t = `$zipadd $arczip $copy_log[0]`;
        }
}

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

sub verify_backup{
        $blksz = $ORAPARM{db_block_size};
        for my $i ( 0 .. $#DATAFILE){
                my @t = `$zipview $arcdir\\$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);
                        }
                }
        }
}

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

# Main ... start here:
$MyName = basename $0,"\.pl";
die "usage: $MyName sys_passwd db_name\n" unless (@ARGV == 2);
$userid = "sys";
$passwd = $ARGV[0];
$orasid = $ARGV[1];
@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%02d%02d%02d.log",$logdir,$orasid,$MyName,
                $start[5]%100,$start[4]+1,$start[3];
$lockfile = "$logdir\\hotarch.$orasid.lock";
$stopfile = "$logdir\\hotarch.$orasid.stop";
die "$MyName is already running\n" if ((-f $lockfile) && (-M $lockfile < 1);
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_log_info();
get_oraparm();
problem "$ORAPARM{db_name} does not match $orasid"
        unless (uc($ORAPARM{db_name}) eq uc($orasid));
$arcdir = "$arcdir\\$ORAPARM{db_name}";
problem "No Archive Mode"
        unless ($log_list{"Database log mode"} eq "Archive Mode");
problem "Archive disabled"
        unless ($log_list{"Automatic archival"} eq "Enabled");
problem "No archive log destination"
        unless ($ORAPARM{log_archive_dest});
problem "No init.ora file"
        unless (-s $ORAPARM{ifile});
problem "Cannot fathom format: $ORAPARM{log_archive_format}"
        unless ($ORAPARM{log_archive_format} =~ /^\%\%ORACLE_SID\%\%/ &&
                $ORAPARM{log_archive_format} =~ /\%S\.(\w+)$/i);
$arch_fmt_str = "$ORAPARM{log_archive_dest}\\" . uc($orasid) . '*' . '%05d.' . $1;
$start_log_seq = $log_list{"Current log sequence"};
stop_backup(); # just in case previous hotarch died an untidy death
write_header();
print HDR "\n DATA FILES:\n";
for my $i ( 0 .. $#DATAFILE){
        problem "$MyName Stopped!" if ( -f $stopfile);
        LogMsg "copying $DATAFILE[$i][1], $DATAFILE[$i][2] blocks";
        print HDR "\t$DATAFILE[$i][1] $DATAFILE[$i][2] blocks\n";
        backup_mode "$DATAFILE[$i][0]","BEGIN";
        $t = `ocopy $DATAFILE[$i][1] $tmpdir`;
        backup_mode "$DATAFILE[$i][0]","END";
        problem "$MyName Stopped!" if ( -f $stopfile);
        $t = `$zipmv $arcdir\\$DATAFILE[$i][3].zip $tmpdir\\$DATAFILE[$i][3]`;
}
backup_control_file();
$t = `copy $ORAPARM{ifile} $arcdir`;
$t =~ s/\s+$//;
problem $t unless ($t =~ /1 file$s$ copied/);
switch_log();
copy_log_files();
verify_backup();
LogMsg "$MyName completed normal";
unlink "$lockfile";

The code for the "hotrest" script is below:

#!/usr/bin/perl

# ------------------------------------------------------------------------
# hotrest -- restore from hotarch
#            G. Patterson, Nov 2001

# Set the common parameters (contained in common.pl)
use File::Basename;
$MyPath = dirname($0);
require "$MyPath\\common.pl";
use Time::Local;

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

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];
}

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

sub problem{
# Err Houston? ... Houston ... We have a problem ...
        LogMsg "ERROR: $_[0]";
        die "$_[0]\n";
}

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

sub check_database_down{
# database must be DOWN!
        # my $sqltmp = "$tmpdir\\check_database_down.sql";
        open SQL,">$sqltmp" || die "cannot open $sqltmp\n";
        print SQL "connect $userid/$passwd\@$orasid\nexit\n";
        my @t = `svrmgrl \@$sqltmp`;
        unlink $sqltmp;
        problem "Cannot $MyName a running database. Shut It Down First!"
                unless grep (/ORA-01034:/,@t);
}

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

sub get_oraparm{
# retrieve parameter values from arcdir\\0header
        open (HDR,"$arcdir\\0header") || die "Cannot open $arcdir\\0header\n";
        while (<HDR>){
                s/\s+$//;
                s/^\s+//;
                if (/\w+ started at /){
                        $start_str = $';
                        @start = split(' ',$start_str);
                        @start = reverse (split( /-/,$start[0]),
                                        split(/:/,$start[1]));
                        $start[4]--;
                        $start[5] -= 1900;
                }
                elsif (/(\w+) FILES:$/){
                        $get_data = lc($1) . "_files";
                }
                elsif (/^$/){
                        undef($get_data);
                }
                elsif (/^(.+): /){
                        $oraparm{$1} = $';
                }
                elsif ($get_data){
                        push(@$get_data,$_);
                }
                else{
                        print "$_";
                        problem "problem with 0header";
                }
        }
}

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

# Main ... start here:
$MyName = basename $0,"\.pl";
die "usage: $MyName sys_passwd db_name\n" unless (@ARGV == 2);
$userid = "sys";
$passwd = $ARGV[0];
$orasid = $ARGV[1];
@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%02d%02d%02d.log",$logdir,$orasid,$MyName,
                $start[5]%100,$start[4]+1,$start[3];
open(LOG,">>$logfile") || die "error opening $logfile\n";
check_database_down();
$arcdir = "$arcdir\\$orasid";
get_oraparm();
problem "$oraparm{ORACLE_SID} does not match $orasid"
        unless (uc($oraparm{ORACLE_SID}) eq uc($orasid));
LogMsg "commencing hotrest";
print "overwriting control files\n";
unlink "$arcdir\\$MyName.tmp.zip";
foreach $x (@ctrl_files){
        $p = dirname( $x);
        chdir $p || die "cannot chdir to $p";
        $b = basename( $x);
        system "$zipadd $arcdir\\$MyName.tmp $b";
        $cmd = "copy $arcdir\\" . uc($orasid) . "_CTRL.FILE $x";
        system "$cmd";
}
print "overwriting datafiles -- it is too late to change your mind\n";
foreach $x (@data_files){
        ($n,$blks,$junk) = split(' ', $x);
        $p = dirname( $n);
        chdir $p || die "cannot chdir to $p";
        $b = basename( $n);
        LogMsg "unzipping $b ...";
        system "$unzip $arcdir\\$b";
        $size = ( -s $n);
        problem "datafile size" unless ( $size == $blks * $oraparm{BLOCK_SIZE})
}
print "moving existing logs to $arcdir\\$MyName.tmp\n";
$p = dirname( $log_files[0]);
chdir $p || die "cannot chdir to $p";
$cmd = "$zipmv $arcdir\\$MyName.tmp " . uc($orasid) . '*';
system "$cmd";
print "restoring log files\n";
system "$unzip $arcdir\\archivelog";
print "$MyName complete -- media recovery required\n";
LogMsg "$MyName completed normal";

In order to restore from a backup you will need to:

Make sure that the archive files are in the archive destination
Shutdown the database
Run the command hotrest. (you must supply system password and oracle_sid on command line). This script will move any existing control files and log files into a temporary zip file. Check the script to see the locations

Perform a standard media recover as follows:

	svrmgrl
	connect internal   (supply the sys password)
	startup mount
	recover database until cancel using backup controlfile
	...
	(Now apply the archive log files ... if you want to while away some
	extra time, you can do this manually. However most times you will
	find it much easier to type 'auto' and let the system apply the
	logfiles till it runs out -- you can ignore the final error message)
	...
	alter database open resetlogs
	quit

Note: These scripts assume certain naming conventions for the archive log files. If your site uses naming conventions that are not compatible with the scripts (e.g. thread number in log file etc). You will have to modify the scripts accordingly. Just read the code and check your parameters.