Lessons from RMAN restore and recovery

I was on call last week and had to recover a production database to a particular point in time to recover some data that was lost.  I had to leave the production database intact but use a development server to restore a copy of production as of the needed date and time so the development team could extract the needed data.  Before I forget what I did last week I wanted to document some of the lessons I learned.

Open severity 1 ticket with Oracle support

It was nice to have Oracle support holding my hand even though my pride would have had me try to go it alone.  But, we are paying for support so I might as well use it and they were helpful.  They pointed me to document 1338193.1 “How to Move/Restore DB to New Host and File System using RMAN”.  They also told me not to use our recovery catalog but to only use a control file since we are cloning production to another host instead of recovering it in place.

Recover production on dev host as same name

The recovered database will have the same name as production after the recovery is complete.  So, when I logged in as oracle on my development box (hp-ux) I had to set ORACLE_SID to the production SID:

export ORACLE_SID=PRDDBxx

But, everything else was the same in terms of the environment that was already setup for the development databases.

Create prod copy spfile from dev spfile

I wanted to use a lot of the dev database settings, i.e. memory settings, for our production copy so I created the new production copy’s spfile from the existing development DB spfile.  I did a create pfile from spfile while connected to the dev database.   Then I edited the three parameters db_name, db_unique_name, and service_names in the pfile to have PRDDBxx instead of DEVDBxx but left the rest alone.  Next I created the prod copy spfile using create spfile from pfile pointing to the edited pfile while connected to the new prod copy database.

Create password file for prod copy

I used the same SYS password for the copy of production as I had used for the dev database it was replacing using orapwd.  It was something like this:

/opt/oracle/product/db/11.2.0.3/bin/orapwd file=/opt/oracle/product/db/11.2.0.3/dbs/orapwPRDDBxx password=xyz force=y

Now I think you can do a startup nomount, but not a startup mount because you have the spfile and pwd file but not the control files that the spfile points to.

Copy production control file to development control file locations

No need to try to restore the control file from RMAN backups.  Just do

alter database backup controlfile to ‘xxx’;

while connected to the production database.  Then ftp the resulting file to the new host and copy the file to the locations pointed to by the control_files parameter in new spfile.  Now you can do a startup mount on the new copy of PRDDBxx on the new host.

Get tape system parameters from prod backup logs

I saved off the output for the production full hot backup and log backups so I had any information I needed for the recovery.  Mainly what I needed was the parameters for the channels like this:

allocate channel ‘dev_0’ type ‘sbt_tape’
parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDBxx,OB2BARLIST=VTL_prdhost_PRDDBxx_ora1)’;

This includes the library that our backup system uses and information about the backup that we will be restoring from.  I just copied these parameters from the production backup script.

Rename datafiles and tempfiles to fit in new  locations on target

set newname for datafile 1 to  ‘/var/opt/oracle/db01/DEVDBxx/dbf/system01.dbf’;

set newname for tempfile 1 to  ‘/var/opt/oracle/db06/DEVDBxx/dbf/temp01.dbf’;

I documented the script I used to build these commands in my previous post.

Figure out how to rename redo logs

I was able to restore the redo logs to the dev db server in the same directory path as production.  But, if there wasn’t enough room I would would have to figure out how to put them somewhere else.  I guess that after you did a startup mount on your control files you could so something to re-point the logs.  But, I just ended up creating the directory that we had on production on dev and I had enough space free to use that filesystem.  I.e.

cd /var/opt/oracle/log01
mkdir PRDDBxx
cd PRDDBxx
mkdir log

Set archive log destination or delete logs after their use

I didn’t do either of these things but got lucky.  The recovery brings in all the archive logs you need and leaves them in the archive log destination, even after applying them for the recovery.  The default archive log destination was $ORACLE_HOME/dbs and there was enough space free to hold all the needed logs.  I should have set the archive log destination in the spfile before doing the recovery.  Also, it looks like you can use the DELETE ARCHIVELOG option of the RECOVER RMAN command and then RMAN won’t fill up your archivelog destination because it will delete the archive logs as soon as it uses them.  But, I haven’t verified this.  I just got lucky that I had enough space.

Use nid command to rename new database

I renamed the recovered production database like this:

nid target=sys/xyz dbname=DEVDBxx

This is a lot easier than what I’m used to doing which is to recreate the control file by backing it up to trace and editing the SQL to have the new DB name.

Order of switch commands

Not sure if this matters but this is where I put the switch commands in my RMAN script:

restore database;
switch datafile all;
switch tempfile all;
recover database;

I wasn’t sure if it mattered if they came before or after the recover command but this order worked for me.

Work with backup admin to free devices

This is another thing I didn’t do that probably would have resulted in a faster restore.  I was using several channels but it appeared that only one virtual tape device at a time was being used.  Presumably all the other devices were doing backups and one or more backups could have been delayed or cancelled to free up devices for the restore.

My final script looked something like this:

rman target / <<EOF

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDB01,OB2BARLIST=VTL_prodhost_PRDDB01_ora1)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDB01,OB2BARLIST=VTL_prodhost_PRDDB01_ora1)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDB01,OB2BARLIST=VTL_prodhost_PRDDB01_ora1)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDB01,OB2BARLIST=VTL_prodhost_PRDDB01_ora1)';
allocate channel 'dev_4' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PRDDB01,OB2BARLIST=VTL_prodhost_PRDDB01_ora1)';
set until time "to_date('Jun 18 2013 08:00:00','Mon DD YYYY HH24:MI:SS')";
set newname for datafile 1 to  '/var/opt/oracle/db01/DEVDB01/dbf/system01.dbf';
...
set newname for datafile 150 to  '/var/opt/oracle/db09/DEVDB01/dbf/psimage_5.dbf';
set newname for tempfile 1 to  '/var/opt/oracle/db06/DEVDB01/dbf/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

exit
EOF

I ran this nohup as a script:

nohup ./recover.sh > recover.out &

– Bobby