2.2 Planning Data Recovery Strategy

Your data recovery strategy should include responses to any number of database failure scenarios. The key to an effective, efficient strategy is envisioning failure modes, matching Oracle database recovery techniques and tools to the failure modes in which they are useful, and then making sure you incorporate the necessary backup types to support those recovery techniques.

To help match failure modes to recovery techniques that can help resolve them, refer to the following sections:

Planning Responses to User Error: Point-in-Time Recovery and Flashback Features
Planning a Response to Media Failure: Restore and Media Recovery
Planning a Response to Datafile Block Corruption: Block Media Recovery

2.2.1 Planning Responses to User Error: Point-in-Time Recovery and Flashback Features

A user or application may make unwanted changes to your database, such as erroneous updates, deleting the contents of a table, or dropping database objects. An adequate backup and recovery strategy uses the many features of the Oracle database to let you return your database to the desired state, with the minimum possible impact upon database availability, and minimal DBA effort.

Depending on the situations you anticipate, consider incorporating each of the following options into your strategy for repsonding to data loss, and then set up your database to make these options possible.

2.2.1.1 Flashback Database

Using Flashback Database enables you to return your whole database to a previous state without restoring old copies of your datafiles from backup, as long as you have enabled logging for flashback database in advance.

You can turn on flashback logging to allow a return to an arbitrary SCN as far in the past as the available flashback logs permit, or you can create guaranteed restore points at specific SCNs, such as before major database updates, which ensure that Flashback Database can be used to return the database to its state at a specific moment.

You must have a flash recovery area configured for logging for flashback database or guaranteed restore points.

2.2.1.2 Creating Normal and Guaranteed Restore Points

As noted, guaranteed restore points ensure that you can return your database to a specific previous time using Flashback Database. Normal restore points do not provide protection for your data, but they are a convenience because by creating one, you can avoid having to record the SCN of the database before an operation from which you wish to recover using point-in-time recovery or Flashback Table, or having to investigate after the operation to determine the correct SCN.

No special setup is required for using normal restore points, though you must plan on creating restore points before they are needed.

2.2.1.3 Database Point-in-Time Recovery

You can perform point-in-time recovery, bringing one tablespace or the whole database back to its state before the time of the error. In either case, you need backups from before the time of the error, plus the redo logs from the time of the backup to the time of the error.

2.2.1.4 Importing Lost Objects from Logical Backup

If you have performed a logical backup by exporting the contents of the affected tables, sometimes you can import the data back into the table. This technique presumes that you are regularly exporting logical backups of your data, and that any changes between exports are unimportant.

Note:

Oracle's Flashback Technology provides faster and less disruptive alternatives to media recovery in many circumstances.

Oracle Flashback Database is a physical-level recovery mechanism similar to media recovery, but generally faster and not requiring the restore of data from backup.
Oracle Flashback Table and Oracle Flashback Drop work at the logical level, undoing unwanted changes to tables, including reversing the effects of DROP TABLE statements.
Oracle Flashback Query and Oracle Flashback Version Query are useful in viewing past contents of tables and investigating how and when logical corruptions affected your database.

Information about these features is collected in Chapter 7, "Performing Flashback and Database Point-in-Time Recovery". This document will allude to such features where they can be helpful and provide pointers for more information. Familiarize yourself with these features before planning your backup and recovery strategy, because you may find that they can be quite valuable and require limited advanced planning.

2.2.2 Planning a Response to Media Failure: Restore and Media Recovery

A media failure occurs when a problem external to the database prevents Oracle from reading from or writing to a file during database operations. Typical media failures include physical failures, such as head crashes, and the overwriting, deletion or corruption of a database file. Media failures are less common than user or application errors, but your backup and recovery strategy should prepare for them.

The type of media failure determines the recovery technique to use. For example, the strategy you use to recover from a corrupted datafile is different from the strategy for recovering from the loss of the control file.

2.2.2.1 Example: Online Redo Log Recovery

The method of recovery from loss of all members of an online log group depends on a number of factors, such as:

The state of the database (open, crashed, closed consistently, and so on)
Whether the lost redo log group was current
Whether the lost redo log group was archived

For example:

If you lose the current group, and the database is not closed consistently (either it is open, or it has crashed), then you will have to restore an old backup and perform point-in-time recovery, followed by OPEN RESETLOGS. You will lose all transactions that were in the lost log. You should take a new full database backup immediately after the OPEN RESETLOGS. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.
If you lose the current redo log group, and if the database is closed consistently, then you can perform OPEN RESETLOGS with no transaction loss. However, you should take a new full database backup. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.
If you lose a noncurrent redo log group, then you can use the ALTER DATABASE CLEAR LOGFILE statement to re-create all members in the group. No transactions are lost. If the lost redo log group was archived before it was lost, then nothing further is required. Otherwise, you should immediately take a new full backup of your database. Backups from before the log was lost will not be recoverable because of the lost log.

2.2.3 Planning a Response to Datafile Block Corruption: Block Media Recovery

If a small number of blocks within one or more datafiles are corrupt, you can perform block media recovery instead of restoring the datafiles from backup and performing complete media recovery of those files. The Recovery Manager BLOCKRECOVER command can be used to restore and recover specified data blocks while the database is open and the corrupted datafile is online.