News

Pythian Papers

The Quick and Easy RMAN Restore

by Marc Fielding

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:

  background_dump_dest='/home/oracle/admin/SID/bdump
  control_files=/u01/oradata/SID/control01.ctl, /u02/oradata/SID/control02.ctl
  core_dump_dest='/home/oracle/admin/SID/cdump
  db_block_size=8192 (must match the datafiles;  adjust if you get an error)
  db_files=1000
  db_name=name_of_db (from the controlfile)
  job_queue_processes=0
  log_archive_dest=/home/oracle/admin/SID/arch
  log_checkpoints_to_alert=true
  shared_pool_size=100000000
  timed_statistics=true
  user_dump_dest=/home/oracle/admin/SID/udump

• 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
  exit

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

• 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';
    }
    exit

  • 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';
  exit

• Open the database

  sqlplus "/ as sysdba"
  recover database using backup controlfile until cancel;
  cancel
  alter database open resetlogs;

• Add the locally-managed tempfiles from the commands you saved earlier



• You're done!

Site Map