Tuesday, October 6, 2009

The Quick and Easy RMAN Restore

This document is for those who have an RMAN backup and want to restore it without the headaches of setting up a recovery catalog, crosschecking backupset pieces, and all the other hoops RMAN usually puts you through. It is a convenient way to restore a database in a disaster situation, or even to clone a database from a regular RMAN backup.

What you need:
An Oracle install of the same version as the original backup was run
A full RMAN backup on a locally-accessible disk
A database controlfile from the time of the backup (usually part of the RMAN backup itself)
The steps:
Make sure ORACLE_HOME is set to match the oracle install
Obtain a database controlfile from the time of the backup. It might be in one of the following places
If there is an RMAN backup log, look for a line like:
copying current controlfile
output filename=/backupdir/SID_j4huif0_1_1.bak
tag=TAG20050716T102243 recid=4814 stamp=563797369
channel ch1: datafile copy complete, elapsed time: 00:00:07
Look for a file in the RMAN backup directory with a name like ctl or control
Look for a file in the RMAN backup directory that's significantly smaller than the rest (under 5m at least)

Verify the controlfile
strings controlfile_name | head -20
strings controlfile_name | grep "/" | head -20
You should see the database name near the top of the file, and the names of some of the datafiles in the database. If not, you may have the wrong file; look through the entire file, and if you can't find it you'll need to restore the long way.
Restore a backup of initSID.ora to $ORACLE_HOME/dbs, if available. If not, here's a dummy one:
control_files=/u01/oradata/SID/control01.ctl, /u02/oradata/SID/control02.ctl
db_block_size=8192 (must match the datafiles;  adjust if you get an error)
db_name=name_of_db (from the controlfile)
Copy the controlfile from the RMAN backup to the locations in the control_files line init.ora
Attempt to start up the database and mount the controlfile
sqlplus "/ as sysdba"
startup mount
If you have errors in your init.ora or problems with the controlfile, you will see errors from Oracle at this point.
Get a list of locally-managed tempfiles (if any). You'll need these later
sqlplus "/ as sysdba"
show parameter user_dump_dest
alter database backup controlfile to trace;
cd user_dump_dest (from the sqlplus output)
ls -t1 | head -1 | xargs grep TEMPFILE | sort | uniq

Relocate the RMAN backup to the same location as the original. Symbolic links are OK here. If you don't know the location, skip to the next step and you will receive an error message with the expected location.
Kick off the restore
rman target / nocatalog
allocate channel ch1 type disk;
run {
restore database;

You now have a restored copy of your RMAN backup. You now need to apply archivelogs to make the backup consistent and recover the database to a point in time. If the database was closed during the original backup (a cold backup), this step is optional
If archivelogs are backed up by RMAN, use RMAN to restore them:
rman target / nocatalog
allocate channel ch1 type disk;
run {
restore archivelog from time = 'date-of-start-of-backup';

If archivelogs are backed up outside RMAN, use whatever tool used to back them up to restore them to the log_archive_dest specified in init.ora above. Only archivelogs written to since the beginning of the original backup are required.

Select a point in time to recover the database to. This point in time must be between the end of the original backup and the end of available archivelogs. Apply the accumulated archivelogs
sqlplus "/ as sysdba"
recover database using backup controlfile until time 'point-in-time';
Open the database
sqlplus "/ as sysdba"
recover database using backup controlfile until cancel;
alter database open resetlogs;
Add the locally-managed tempfiles from the commands you saved earlier
You're done!

No comments:

Post a Comment