Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 6 May 2012

Rman: Disaster Recovery from the Scratch

Tested: Oracle 9i database
OS: Windows 2003
DB Mode: Archivelog
Backup Used: RMAN Online Backup

Overview: This Example shows how an Oracle Database can be recovered or reconstructed by using only the RMAN backup files (from Disks) in case of complete server crash. It is essential for every DBA to plan and test his Backup - Recovery strategy in advance to anticipate future difficulties.  
If Your database is running in No Archive log mode and you have Daily/ Weekly RMAN backup configured. One day, your server crashed and you lose one of the disks or all the disks and you have no other option but to restore the whole database backup to point of last valid backup.
Recommendation: If you have your database is in Archivelog then you can recover up to the point of failure time. So it is recommended always keep your database is in Archivelog and properly take the backup of archive before deleting the physical archivelog link Converting Noarchivelog to Archivelog Mode
To test the validity of a RMAN backup a full restore and recovery of databases can be performed from scratch on the same or another server using RMAN backups which have been restored from the disk backups
This Example illustrate the procedure by detailing the steps required to restore the backup of a production database on the same server with same location or different server with same location
1.      The RMAN backups have been restored from disk backups to the same backup location on the new server as the production server where the backup was originally taken suppose the backup taken on the production server location: D:\BACKUP\RMAN_BACKUP\HOTBACKUP then create the same folder on the new server and copy the backup on the new server on the same location.
2.      Consider the identical directory structure of production has been created on the new server such as the database files (data, control files, redo log files), bdump, cdump, udump and adump locations etc.
3.      Controlfile autobackup must be enabled.
§         Restore the spfile from the autobackup
§         Restore the controlfile from the autobackup
§         Restore database
§         Recover the database
§         Open the database with resetlogs
Note: Use the command RECOVER DATABASE NOREDO; if you donot want to apply redolog or if your database in NOARCHIVELOG mode. In fact there is no need to recover the database if you have taken consistent (shutdown mode) backup.
Full database backup
Before start the actual recovery process for the test purpose take a full backup of your database for precaution.

Restore the SPFILE
Here i used the pfile because database is already started with spfile you need to use "startup force nomount"
Restore the Controlfile
RMAN> restore controlfile from "\\testdb\orcl3\c-691421794-20120506-01";
Restore Database
Recover Database
At this stage if you find the following type of error then do not be panic. You just need to open the database using Resetlogs
unable to find archive log
archive log thread=1 sequence=33
RMAN-00571: ========================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS 
RMAN-00571: ========================================
RMAN-03002: failure of recover command at 05/06/2012 14:45:21
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33 lowscn
Alternatively you can use Incomplete recovery (Cancel based) from the SQL prompt and open the database using Resetlogs option.
SQL>Recover database until cancel;
SQL>Recover database until cancel using backup controlfile;
Then oracle starts applying the necessary redologs to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.
Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the "CANCEL" command:
Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.
Open the Database
Note: Now open the database from SQLPLUS and check the database. Please do not forget to take the backup again (if you have used to open resetlogs) specially for oracle 9i.


  1. Very Nice blogs Sir..
    As have told you your blogs are best practice for me.

    I have one Q.

    I want to restore a specific backup (what i choose) using RMAN ?
    what to do

  2. Dear,
    You can restore with the latest tag. check out some example
    restore archivelog from tag=Sadhan_full_archive_bk;‎
    restore controlfile from '/backup/ctl_shaan_709394097_6_1';‎
    recover database from tag="weekly_backup";‎
    either you can put your backupset on separate location and catalog that backupset.
    hope it will help you.

  3. I've been researching disaster recovery services and came across your blog. Really informative, thanks!

  4. very nice blog sir, thank u sir.

  5. I found out the hard way just what not having a database disaster recovery plan can do. Thinking ahead is always the smart thing to do! Thankfully I found a great service to aid me in the process.

  6. Well written post. I appreciate your guidance for sharing about Disaster recovery . I really need to know about it. Great work!