Recovery Without Hurdles

A backup is no good if it cannot be restored. In fact, a backup cannot be considered successful if it cannot be restored. So how can you make sure that a backup can be restored? This question can be tackled by ensuring that you understand the various recovery scenarios ahead of time and document what steps you need to take to recover the object in question for that particular scenario. We do not list all the scenarios here; rather, we provide an overview of what needs to be done for backup as well as restore and recovery. Detailed information for backup strategies and recovery options is available in the Oracle Database 10g Backup and Recovery Basics manual, as well as in the Oracle Database 10g Backup and Recovery Advanced User's Guide.

Backup Strategies

The recovery and restore of databases is greatly influenced by the way you back up the database. Each type of data recovery will require that you take certain types of backup steps. You will need to cater to failures from user error, data file block corruption, and media failure, as well as plan for situations such as the complete loss of a data center. How quickly you can resume normal operation of your database is a function of what kinds of restore and recovery techniques you include in your planning. Each restore and recovery technique will impose requirements on your backup strategy, including which features of the Oracle database you use to take, store, and manage your backups.

We described a few features of RMAN such as fast incremental backups with block tracking, incrementally updated backups, and so on in the previous sections. These will help you decide which backup method is right for you. More information about the various types of backups is detailed in the manuals noted in the previous section. Overall, you will have to decide on issues such as the use of ARCHIVELOG mode, choosing a backup retention period, using the flash recovery area, archiving of older backups, establishing a backup period, and related tuning parameters. You can optionally implement backing up of often-used tablespaces versus entire databases, use of NOLOGGING, and related backups. If you were using RMAN previously and had not implemented incremental backups because such backups scanned the whole data file, you might want to reconsider using block tracking to mitigate this issue.

Additionally, when you want to use an MML, you will need to decide which software vendor to use and their relative merits, costs, and hardware support. When backing up directly to tape, note that tape drives are much slower than disk-based backups and hence require careful planning and testing in order to meet backup and restore window requirements.

Recovery Scenarios

RMAN recovery consists of two operations: RESTORE, which retrieves files from RMAN backups based on the contents of the RMAN repository, and RECOVER, which performs complete or point-in-time media recovery using available data files and redo logs. Simple restore and recover operations can be performed using the OEM Database Control. For more advanced scenarios, you should document the various recovery scenarios and the exact actions that need to be performed in each case. Optionally, you can prepare scripts that can be used from the command line in each of these cases. This preparation is wise considering that restores and recoveries are usually performed at unexpected times and under pressure.

The list of recovery scenarios and the overall actions are listed here. Note that before performing the actual recovery, you can use the RESTORE restore_level VALIDATE and PREVIEW commands to make sure that you have all the required files.

• Whole database recovery. Use the RESTORE DATABASE and RECOVER DATABASE commands on the whole database. Note that the database must not be open when restoring or recovering the entire database.

• Recovering current tablespaces. Use the RESTORE TABLESPACE and RECOVER TABLESPACE commands on individual tablespaces when the database is open. Only the tablespace that needs recovery should be taken offline. These operations restore and then recover the tablespace. You will need to bring the recovered tablespace online manually.

• Recovering current data files. Use the RESTORE DATAFILE and RECOVER DATAFILE commands on individual current data files when the database is open. Take the data file that needs recovery offline, restore and recover the data file, and bring the data file online.

• Recovering individual data blocks. RMAN can recover individual corrupted data file blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files, or results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks using the BLOCKRECOVER CORRUPTION LIST command. You can also recover individual blocks using the BLOCKRECOVER DATAFILE file# BLOCK block# command.

• Performing tablespace Point-In-Time Recovery (TSPITR). You should first determine whether you can use the FLASHBACK TABLE or FLASHBACK DROP as described in Chapter 17 to revert the dropped or modified tables prior to considering TSPITR. If not, then you can use an RMAN-managed auxiliary database instance or another of your own database instances to perform this TSPITR.

• Performing database Point-In-Time Recovery (DBPITR). You should first determine whether you can use the FLASHBACK DATABASE as described in Chapter 17 to take the database back to the required time. If this is not possible, then you will have to use the SET UNTIL command followed by restore and recovery of all data files. You can also perform this recovery to a previous incarnation of the database.

• Performing recovery with a backup control file. RMAN can perform this recovery with and without a catalog, but note that you will have to perform a RESETLOGS operation as you would have to when all the control files are lost.

• Restoring the database to a new host. RMAN provides a DUPLICATE command to restore the database on another host for creating clones. Although this is not a strict recovery scenario, we mention this on account of its common occurrence.

• Disaster recovery. This operation is similar to restoring the database on a new host. However, you will not have any prior information about existing data files, tablespaces, and other files, or control information such as the catalog or OEM repositories. The key, of course, is having up-to-date documentation about the backups and databases themselves. Note that you can use the SET DBID DB ID_number command to set the DB ID back to the original one after you have performed recovery.

The exact commands and other information are available in the manuals mentioned in the previous section.