Performing Recovery

---

Here's one of our favorite sayings: every DBA has a backup strategy-few of them, however, have a recovery strategy. Our advice here is not to be the cliché. Make sure you understand your recovery options, and that they are tested once in a while. Maintaining availability through the failure event requires that you have the knowledge and trust in your backups to take immediate action that is not a knee-jerk reaction that leads to further downtime due to the recovery itself. The key, as always, is not to initiate a full restore of the database in a blind panic.

Database Recovery: Restore and Recover

Recovering from some sort of failure (database corruption, hardware loss, and so forth) typically involves two distinct steps for an Oracle database: restore and recovery. Datafile restore refers to the act of getting a copy of the lost or broken file back in place from the backup. Recovery refers to the process of applying archived redo log changes to the restored file.

As we've said a few times throughout this chapter, the goal is to avoid a full database restore. When you encounter an error, it is critical that you come to terms with the full extent of the problem, and be mindful of overreacting. Think first to restore a single datafile back for recovery. You can always bring the file or group of files manifesting the problem first and recover them, and then investigate for other possible problem files.

This approach is assisted in a huge way by the backup approach we have espoused so far: incrementally updated datafile copies that live in the FRA. Because the files exist on disk, and are independent of each other (that is, not multiplexed into a single backup piece), you can perform restore and recovery piecemeal without slowing down the overall performance.

Stream through a day's worth of archivelogs two or three times, however, and you will probably realize that a piecemeal approach does have its downside. So trying to diagnose the entire problem prior to recovery is extremely useful.

If you are taking backupset-type backups directly to tape, restoring a single datafile from the backupset is going to be extremely expensive. RMAN must restore the entire backup piece from tape before it can extract just the blocks for the single datafile. So multiplexing multiple files into the same backupset can be very costly at the time of recovery. Better, then, to set filesperset=1, which will slow down the backup but provide the most flexibility during restore.

Full Database Recovery

Okay, there are times when it's absolutely required. In those cases, the code is simple and straightforward. Note that the following code for restoring the database assumes that you have the current controlfile and the target database is mounted. If there is no controlfile, you should see the section below, 'Recovery from Complete Loss (No Controlfile).'

restore database; recover database;

If you need to specify a point in time in the past to recover to:

run {set until time = '04-NOV-03 12:00:00'; restore database; recover database; }

Datafile or Tablespace Recovery

The most common form of recovery should be datafile recovery. To perform datafile recovery, you must offline the datafile prior to restoration. You can do this from the SQL prompt, or from within RMAN (shown next). These recovery commands assume the database is still open and running.

rman>sql 'alter database datafile 8 offline';

Then, from the RMAN prompt, you issue your restore and recover command. To save yourself typing, you can refer to the files by their file number instead of name.

sql>select file#, name from v$datafile; rman>connect target / rman> run { restore datafile 8;  recover datafile 8;  sql 'alter database datafile 8 online';}

The same basic structure is used for tablespaces:

rman> run { sql 'alter tablespace users offline'; restore tablespace users; recover tablespace users; sql 'alter tablespace users online'; }

Recovery from Complete Loss (No Controlfile)

If the controlfile has been lost along with the rest of the database, you have a few different options, depending on what your controlfile backup strategy is. If you took our advice and configured RMAN for controlfile autobackup, then you have a few steps to run through.

HA Workshop: Recovering from a Complete Loss

Workshop Notes

This workshop guides you through the restoration and recovery of an Oracle database that has been completely lost. This example shows how to recover using RMAN when there is no RMAN catalog and no controlfile detailing our backups, so we must employ the controlfile autobackup feature. What is not covered here is the reinstallation of the Oracle software itself. If your complete loss included the ORACLE_HOME for your Oracle Database 10g install, you would also need to reinstall the software, and apply any patches that existed, prior to following this procedure.

Step 1.  Determine the DBID of the lost database.

This can be done by checking the location where you have been putting your controlfile autobackups. If you use a flashback recovery area, check the FRA directory structure for <sid>->autobackup->date->autobackup_name.

pwd /u02/flash_recovery_area/beta10/autobackup/2003_11_04 ls O1_MF_S_509209441_ZTJ5228D_.BKP

In this filename, the fourth number string is your database ID, 509209441.

Step 2.  Start up nomount a default instance for your database.

ORACLE_SID=beta10;export ORACLE_SID sqlplus "/ as sysdba" sql> startup force nomount

This opens an instance with default System Global Area (SGA) parameters.

Step 3.  Restore your spfile.

rman target / rman> restore spfile from autobackup; rman> shutdown immediate; rman> startup nomount;

Step 4.  Restore the controlfile, and then mount the database.

rman> restore controlfile from autobackup; rman>alter database mount; 

Step 5.  Restore the datafiles, and perform media recovery.

rman> restore database; rman> recover database; rman> alter database open resetlogs;;

Recovery Using the Incrementally Updated Datafile Copy

There is no extra step required to use the incrementally updated datafile copies for restore operations. They will be used automatically because their incremental update time will show them to be the most recent copies of the files. Cool, huh?

Block Media Recovery

So we get to the one feature of RMAN that is absolutely too cool for words: block media recovery (BMR). BMR refers to RMAN's ability to restore from backup a single data block, and then perform media recovery on that single block. This is huge, people. Ora-1578? No problem. Plug in the file number and block number from the 1578 error, and RMAN will go to the last backup, get the one block showing corruption, restore it, and then scroll through the archivelogs to see if any of the redo need be applied to the block. Here's the kicker: the file that has the corrupt block remains available through the recovery process. Wait, it gets better. The segment that contains the corrupt block is available during the recovery process. In other words, you can continue to use the table that has a corrupt block, as long as you don't try to select from the bad block. Talk about high availability! It doesn't get any better than this.

Usage is simple. Take your average ora-1578 error:

ORA-1578: ORACLE data block corrupted (file # 7, block # 1234)

Note the file number and block number, and plug them into the RMAN command:

blockrecover datafile 7 block 1234;

You can specify multiple blocks:

blockrecover    datafile 7 block 1234    datafile 10 block 3265;

---