Recovery of your Oracle Database with RMAN Oracle Tips by Burleson Consulting

Recovery of your Oracle Database with RMAN

When the chips are down, this is what its all about when you are a DBA. Being able to recover your database is priority number one, next to anything else. This section is about using the backups you made earlier in this chapter, and recovering your database with them.

Recovery is one of the most complex Oracle topics. This section addresses basic recovery scenarios. More complicated recovery issues such as incomplete recovery or point in time recovery are covered in Robert Freemans other titles, Oracle9i RMAN Backup and Recovery and Oracle Database 10g RMAN Backup and Recovery for more information on various aspects of RMAN.

Also see these additional RMAN backup tuning tips.

Recover Oracle using an Offline Backup with RMAN

Before you can restore your database using RMAN, the following needs to be properly in place (this assumes a full system loss):

* The operating system must be installed

* The Oracle software must be installed

* The RMAN backup files must be available

* You must create the file systems that the database files will be restored to.

Once these pieces are in place you can restore your database. In the event of a full system loss you will need to replace:

* The SPFILE

* The Control File

* The database datafiles

If the SPFILE or control file is intact, you will not need to replace that component. The following sections walk you through each restore step. We assume you have followed the backup procedures earlier in this chapter, including the automated backup of SPFILES and control files.

Restoring the Oracle SPFILE

Restoring the SPFILE is an automated process in many cases with RMAN. You simply need to configure RMAN with the proper database DBID (which you should have recorded when you created the database) and you can then restore the SPFILE from any auto backup.

To restore the spfile, you still need to start the database, but you can do so with a minimal database parameter file. All you need to do is start your database instance is set the db_name parameter in a manual parameter file. Then you can start the instance using the startup command with the pfile parameter as seen in this example:

Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initbooktst.ora

Once the instance is started, you can start RMAN and restore the SPFILE as seen in this example:

RMAN>restore spfile from autobackup;

If you are not using automated backups, you will need to use more sophisticated means of restoring your SPFILE, which we don't cover in this book.

Restoring the Oracle Control File

Restoring the control file is much like restoring the SPFILE. Again, you simply need to configure RMAN with the proper database DBID and you can then restore the control file from any auto backup.

To restore the control file, you still need to start the database, but you can do so with a minimal database parameter file. All you need to do is start your database instance is set the db_name parameter in a manual parameter file. Then you can start the instance using the startup command with the pfile parameter as seen in this example:

Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initbooktst.ora

Once the instance is started, you can start RMAN and restore the CONTROL FILE as seen in this example:

RMAN>restore controlfile from autobackup;

If you are not using automated backups, you will need to use more sophisticated means of restoring your CONTROL FILE, which we don't cover in this book.

Restoring and Recovering the Database

Once you have recovered the SPFILE and the control file, restoring the database can be pretty easy. Again, many things can occur to make the process more complex, but assuming that you have restored your RMAN backup sets in the same place, and that all of your file systems are built correctly, and named the same as before, your restore the database with the following command:

RMAN>recover database noredo;

If the online archived redo logs are available, then change the command to the following:

RMAN>restore database;

Once the database is restored, you can then recover the database. Simply use the RMAN recover database command as seen here:

RMAN>recover database;

Once you have issued this command, your database should be recovered to the point of the backup.

Recover your Database using an Online Backup with RMAN

If you backed up your ARCHIVELOG mode database with RMAN then you will be able to recover to the point of the last available archived redo log (or online redo log if those are still available). The cool thing is that the RMAN procedure is almost the same regardless of if the backup was in ARCHIVELOG mode or NOARCHIVELOG mode.

First, follow the directions above for restores of the SPFILE and the control file. Now, simply use the restore database and recover database commands as seen here:

RMAN>restore database;

RMAN>recover database;

Again, Oracle will restore your database. It will also automatically recover the archived redo logs for you, so you don't have to worry about it.

Other RMAN Recovery Options

There are a number of other recovery options available with RMAN. Many of these can be complex. In fact, we have a 600 page book dedicated just to RMAN operations alone. Operations such as point in time recovery, incomplete recovery, and many other operations are possible with RMAN. RMAN is a very powerful backup and recovery tool and it is worth taking the time to learn all about it.

Conclusion on RMAN Recovery

Backing up your database is absolutely imperative.  Without doing so, you place a major liability on your own job as well as your company's existence.  We have seen databases crash without a good backup, and it isn't pretty at all; its something you can easily lose your job over, and if it causes major problems, you can also lose your credibility in the database world.  If you've ever heard the old line "You'll never work in this town again!" then you obviously know that it was said to a DBA that did not use RMAN to backup their database before it crashed, causing an entire company to cease to exist.

Remember, you want to make your living off of your database.  You can't do this if your database has a chance of ever being lost without possible recovery.

Get creative with your backups.  Make sure you get everything, and then get it again.  Never leave a man behind; one lost archivelog, one lost datafile, and your database will never be the same.

Also, see my notes on the different backups types and the differences between incremental cumulative and incremental differential backups.