I was on call again last week and again had to do a database recovery using RMAN. I thought I might as well document some lessons learned even though it went quite smoothly.
First, here are the steps I took. I’ve modified the commands and script so that our real usernames, passwords, database names, and host names are obscured, but otherwise it is exactly what I ran.
Environment wise we are on 11.2.0.3 Oracle database running on HP-UX 11.31 on 64 bit Itanium.
First I had to build the Unix script that I would run to do the recovery:
rman target / catalog CATUSER/CATPASSWORD@CATDB <<EOF
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_4' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
set until time "to_date('Feb 20 2014 21:38:10','Mon DD YYYY HH24:MI:SS')";
restore database;
recover database;
}
exit
EOF
In my previous recovery scenario that I blogged about I didn’t use the catalog because I was recovering to a different database just to retrieve data from before the failure I was trying to recover from. In this case I was recovering the production database in place to our Thursday night backup that we took just before embarking on a PeopleTools upgrade. So, first thing I had to do was look up the user, password, and connect string for the catalog. Then I just tested connecting without doing anything.
rman target / catalog CATUSER/CATPASSWORD@CATDB exit
Next I had to figure out the allocate channel commands. In our case we have a graphical front end to our data protector backup system and I was able to extract the log from Thursday night’s backup. I copied the allocate channel commands unchanged from the log to my script.
The hardest part of setting up this script was knowing what time to use in the set until time command. I decided to use the last time that appeared on the backup output. I knew that the application was down and so not much updating was occurring so I wasn’t worried about going past the earliest possible point that I could use. I knew that our upgrade didn’t start until after the backup finished so I reasoned that the last backup message would be safe. But, the backup program spits out messages in a different time zone than our database is in. (I’m in Arizona, but my database is on Chicago time…). So, getting the timezone right was key otherwise I could be off by an hour. The date and time in the last backup message looked like this in Arizona time:
Time: 2/20/2014 8:38:10 PM
I had to translate it to
Feb 20 2014 21:38:10
This got it into central time and 24 hour format.
Lastly, I considered whether we had enough space for all the archive logs that would be retrieved for the recovery. Since we were recovering from just after a hot backup and with the application down I decided that it couldn’t be more than one or two archive logs. As it turns out the archive logs were on the disk when the recovery ran so they didn’t even need to be restored from tape.
To actually run the script I first manually put the database in mount mode:
sqlplus / as sysdba shutdown immediate startup mount
Then I ran the recovery nohup in the background:
nohup ./recover.sh > recover.out &
Lastly, after carefully reviewing the output from the recovery to make sure it looked right I opened the database resetting logs:
sqlplus / as sysdba alter database open resetlogs;
It went very smoothly. It was a little nerve-racking recovering the production system in place, but we did make two different backups of it before doing the recovery so presumably we could have gone back to the way it was before the recovery.
– Bobby
