otak-otak | favorite evening food

December 1, 2011

perl script to automate restore, recover rman backup

Filed under: barman — ora62 @ 11:26 am

I created ‘simple-silly’ perl script to automate restore and recover of rman backup. this script will only need supply rman backup log.

how to execute:
1. to restore & recover until all archivelog in the backup.
./restore_arc.sh <full_path_rman_backup_log>

2. to restore & recover until all archivelog (until cancel).
./restore_arc_last.sh <full_path_rman_backup_log>

has been tested on linux env, you may use this script for your needs and please take your own risk :)

cat restore_arc.sh

#!/bin/bash
. /home/ujang/.bashrc

SCRPATH=/home/ujang/docs/orascript/backup-recovery/rman
LOGPATH=/home/ujang/docs/orascript/backup-recovery/rman
TAG=`date +%Y%m%d`
CMDFILE=${SCRPATH}/restore.txt
LOGFILE=${LOGPATH}/restore_${TAG}.log
RMANLOG=$1
cd $SCRPATH
export ORACLE_HOME=/apps/oracle/product/9.2
export ORACLE_SID=PROD
./restore.pl $RMANLOG spfile=NO lastarc=NO
##$ORACLE_HOME/bin/rman target / nocatalog cmdfile=$CMDFILE msglog $LOGFILE

cat restore_arc_last.sh

#!/bin/bash
. /home/ujang/.bashrc

SCRPATH=/home/ujang/docs/orascript/backup-recovery/rman
LOGPATH=/home/ujang/docs/orascript/backup-recovery/rman
TAG=`date +%Y%m%d`
CMDFILE=${SCRPATH}/restore.txt
LOGFILE=${LOGPATH}/restore_${TAG}.log
RMANLOG=$1
cd $SCRPATH
export ORACLE_HOME=/apps/oracle/product/9.2
export ORACLE_SID=PROD
./restore.pl $RMANLOG spfile=NO lastarc=YES
##$ORACLE_HOME/bin/rman target / nocatalog cmdfile=$CMDFILE msglog $LOGFILE

cat restore.pl

#!/usr/bin/perl
my @array = qq[input archive log thread];
my @match = (“sequence=”,”thread=”,”DBID=”,”piece handle=”,”CONTROL”,”SPFILE”);
my @seqid;
my $thread=0;
my $dbid=0;
my $ctlfile=”XXX”;
my $initfile=”XXX”;
my $orahome=”XXX”;
my $orasid=”X”;
foreach $key (keys(%ENV)) {
if ($orahome eq “XXX”) {
if ($key eq “ORACLE_HOME”) {
$orahome=$ENV{$key};
}
}
if ($orasid eq “X”) {
if ($key eq “ORACLE_SID”) {
$orasid=$ENV{$key};
}
}
}

if ($orahome eq “XXX” || $orasid eq “X”)
{
print “environment variable ORACLE_HOME dan ORACLE_SID harus diset!!\n”;
exit 0;
}

if ($ARGV[0] eq “help=yes” || $ARGV[0] eq “help=y” || $ARGV[0] eq “-h” || $ARGV[0] eq “–h”)
{
print “Cara penggunaan :\n”;
print “1. anda harus berada pada directory dimana file ini dijalankan, misalnya cd /oradata/rman/scr”;
print “2. ketik \n”;
print ” recover spfile=YES|NO\n”;
print ” misal: \n”;
print ” recover /oradata/rman/log/rman_bkp_20110504.log spfile=NO\n”;
exit 0;
}

my $rmanlog=$ARGV[0];
my $spfileloc=$ARGV[1];
my $lastarc=$ARGV[2];
my $spfileloc=substr($spfileloc,index($spfileloc,”=”,0)+1,3);
my $lastarc=substr($lastarc,index($lastarc,”=”,0)+1,3);

if (length($rmanlog)<=0)
{
print “anda harus passing parameter, gunakan restore.sh -h untuk bantuan\n”;
exit 0;
}

open(OLD, “<“, $rmanlog) or die “can’t open $rmanlog”;
while ()
{
my $line = $_;
if ($dbid == 0) {
my $seq = (split /$match[2]/, $line)[1];
$dbid=substr($seq,0,index($seq,”)”,0));
}
if ($ctlfile eq “XXX”) {
my $seq = (split /^$match[3]/, $line)[1];
if($seq =~ m/$match[4]/i) {
$ctlfile=substr($seq,0,index($seq,chr(32),0));
}
}

if ($initfile eq “XXX”) {
my $seq = (split /$match[3]/, $line)[1];
if($seq =~ m/$match[5]/i) {
$initfile=substr($seq,0,index($seq,chr(32),0));
}
}
if ($line =~ /^@array/) {
my $seq = (split /$match[0]/, $line)[1];
push @seqid, substr($seq,0,index($seq,chr(32),0));
if ($thread == 0) {
my $seq = (split /$match[1]/, $line)[1];
$thread=substr($seq,0,index($seq,chr(32),0));
}
}
}
@seqid=sort(@seqid);
my $seqmax = @seqid;
close(OLD);

system(“rm -f restore.txt”);

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year=$year+1900;
$new=”restore.txt”;
open(NEW, “>”, $new) or die “can’t open $new: $!”;
if ($spfileloc eq “YES” or $spfileloc eq “yes”) {
print NEW “host ‘mv $orahome/dbs/spfile$orasid\.ora $orahome/dbs/spfile$orasid\_$year$mon$mday$hour$min\.ora’;\n” or die “can’t write $new: $!”;
}
print NEW “set dbid=$dbid;\n” or die “can’t write $new: $!”;
print NEW “startup force nomount;\n” or die “can’t write $new: $!”;
if ($spfileloc eq “YES” or $spfileloc eq “yes”) {
print NEW “restore spfile to ‘$orahome/dbs/spfile$orasid\.ora’ from ‘$initfile’;\n” or die “can’t write $new: $!”;
}
print NEW “shutdown abort;\n” or die “can’t write $new: $!”;
print NEW “set dbid=$dbid;\n” or die “can’t write $new: $!”;
print NEW “startup nomount;\n” or die “can’t write $new: $!”;
print NEW “restore controlfile from ‘$ctlfile’;\n” or die “can’t write $new: $!”;
print NEW “alter database mount;\n” or die “can’t write $new: $!”;
print NEW “restore database;\n” or die “can’t write $new: $!”;
print NEW “restore archivelog from sequence $seqid[0] until sequence $seqid[$seqmax-1];\n” or die “can’t write $new: $!”;

if ($lastarc eq “YES” or $lastarc eq “yes”) {
print NEW “sql ‘RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;’;\n” or die “can’t write $new: $!”;
} else {
print NEW “recover database until logseq $seqid[$seqmax-1] thread $thread;\n” or die “can’t write $new: $!”;
}
print NEW “sql ‘alter database open resetlogs’;\n” or die “can’t write $new: $!”;
close(NEW) or die “can’t close $new: $!”;

Blog at WordPress.com.