18 Performing User-Managed Database Flashback and Recovery

Performing Flashback Database with SQL*Plus

The SQL*Plus FLASHBACK DATABASE command performs the same function as the RMAN FLASHBACK DATABASE command: it returns the database to a prior state.

Note that using Flashback Database requires that you create a flash recovery area for your database and enable the collection of flashback logs. See Oracle Database Backup and Recovery Basics for more details about how the Flashback Database feature works, requirements for using Flashback Database, and how to enable collection of flashback logs required for Flashback Database. The requirements and preparations are the same whether you use RMAN or user-managed backup and recovery.

To perform the FLASHBACK DATABASE operation in SQL*Plus:

Restoring Datafiles with Operating System Utilities

If a media failure permanently damages one or more datafiles of a database, then you must restore backups of these datafiles before you can recover the damaged files. If you cannot restore a damaged datafile to its original location (for example, you must replace a disk, so you restore the files to an alternate disk), then you must indicate the new locations of these files to the control file.

If you are restoring a database file on a raw disk or partition, then the procedure is basically the same as when restoring to a file on a file system. However, be aware of the naming conventions for files on raw devices (which differ depending on the operating system), and use an operating system utility that supports raw devices.

To restore backup datafiles to their default location:

Restoring Archived Redo Logs with Operating System Utilities

All archived redo logs generated between the time a restored backup was created and the target recovery time are required for the pending recovery. The archived logs will eventually need to be on disk so that they are available to the database.

To restore necessary archived redo logs:

Restore Lost Copy of a Multiplexed Control File

Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Restore Control File from Backup After Loss of All Current Control Files

Use the following procedures to restore a backup control file if a permanent media failure has damaged all control files of a database and you have a backup of the control file. When a control file is inaccessible, you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, then you receive this error message:

ORA-00205: error in identifying control file, check alert log for more info 

You cannot mount and open the database until the control file is accessible again. If you restore a backup control file, then you must open RESETLOGS.

As indicated in the following table, the procedure for restoring the control file depends on whether the online redo logs are available.

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf',
              '/oracle/oradata/trgt/control01.dbf'

CONTROL_FILES='/good_disk/control01.dbf','/good_disk/control02.dbf'

Create New Control File After Losing All Current and Backup Control Files

If all control files have been lost in a permanent media failure, but all online redo log members remain intact, then you can recover the database after creating a new control file. The advantage of this tactic is that you are not required to open the database with the RESETLOGS option.

Depending on the existence and currency of a control file backup, you have the options listed in the following table for generating the text of the CREATE CONTROLFILE statement. Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose.

To create a new control file:

Preconditions of Performing User-Managed Recovery

To start any type of media recovery, you must adhere to the following restrictions:

• You must have administrator privileges.

• All recovery sessions must be compatible.

• One session cannot start complete media recovery while another performs incomplete media recovery.

• You cannot start media recovery if you are connected to the database through a shared server process.

Applying Logs Automatically with the RECOVER Command

Oracle recommends that you use the SQL*Plus RECOVER command rather than the ALTER DATABASE RECOVER statement to perform media recovery. In almost all cases, the SQL*Plus method is easier.

When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:

• Issuing SET AUTORECOVERY ON before issuing the RECOVER command

• Specifying the AUTOMATIC keyword as an option of the RECOVER command

In either case, no interaction is required when you issue the RECOVER command if the necessary files are in the correct locations with the correct names. The filenames used when you use automatic recovery are derived from the concatenated values of LOG_ARCHIVE_FORMAT with LOG_ARCHIVE_DEST_n, where n is the highest value among all enabled, local destinations.

For example, assume the following initialization parameter settings are in effect in the database instance:

LOG_ARCHIVE_DEST_1 = "LOCATION=/arc_dest/loc1/" 
LOG_ARCHIVE_DEST_2 = "LOCATION=/arc_dest/loc2/"
LOG_ARCHIVE_DEST_STATE_1 = DEFER 
LOG_ARCHIVE_DEST_STATE_2 = ENABLE 
LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

In this case, SQL*Plus automatically suggests the filename /arc_dest/loc2/arch_%t_%s_%r.arc (where %t is the thread, %s is the sequence and %r is the resetlogs ID).

If you run SET AUTORECOVERY OFF, which is the default option, then you must enter the filenames manually, or accept the suggested default filename by pressing the Enter key.

Recovering When Archived Logs Are in the Default Location

Recovering when the archived logs are in their default location is the simplest case. As a log is needed, the database suggests the filename. If you are running nonautomatic media recovery with SQL*Plus, then the output is displayed in this format:

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

For example, SQL*Plus displays output similar to the following:

ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc
ORA-00280: change 53577 for thread 1 is in sequence #802
Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

Similar messages are returned when you use an ALTER DATABASE ... RECOVER statement. However, no prompt is displayed.

The database constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file. The following are possible settings:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/'
LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

SELECT NAME FROM V$ARCHIVED_LOG;

NAME
----------------------------------------
/oracle/oradata/trgt/arch/arcr_1_467.arc
/oracle/oradata/trgt/arch/arcr_1_468.arc
/oracle/oradata/trgt/arch/arcr_1_469.arc

Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1 destination, and if the value for LOG_ARCHIVE_FORMAT is never altered, then the database can suggest and apply log files to complete media recovery automatically.

Recovering When Archived Logs Are in a Nondefault Location

Performing media recovery when archived logs are not in their default location adds an extra step. You have the following mutually exclusive options:

• Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.

• Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command

Resetting the Archived Log Destination

You can edit the initialization parameter file or issue ALTER SYSTEM statements to change the default location of the archived redo logs.

To change the default archived log location before recovery:

Overriding the Archived Log Destination

In some cases, you may want to override the current setting for the archiving destination parameter as a source for redo log files.

To recover archived logs in a nondefault location with SET LOGSOURCE:

Responding to Unsuccessful Application of Redo Logs

If you are using SQL*Plus's recovery options (not SQL statements), then each time the database successfully applies a redo log file, the following message is returned:

Log applied.

You are then prompted for the next log in the sequence or, if the most recently applied log is the last required log, terminates recovery.

If the suggested file is incorrect or you provide an incorrect filename, then the database returns an error message. For example, you may see something like:

ORA-00308: cannot open archived log "/oracle/oradata/trgt/arch/arcr_1_811.arc"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery cannot continue until the required redo log is applied. If the database returns an error message after supplying a log filename, then the following responses are possible.

Interrupting User-Managed Media Recovery

If you start media recovery and must then interrupt it, for example, because a recovery operation must end for the night and resume the next morning, then take either of the following actions:

• Enter the word CANCEL when prompted for a redo log file.

• Use your operating system's interrupt signal if you must terminate when recovering an individual datafile, or when automated recovery is in progress.

After recovery is canceled, you can resume it later with the RECOVER command. Recovery resumes where it left off when it was canceled.

Performing Closed Database Recovery

This section describes steps to perform complete recovery while the database is not open. You can recover either all damaged datafiles in one operation, or perform individual recovery of each damaged datafile in separate operations.

Perform the media recovery in the following stages:

Performing Datafile Recovery in an Open Database

It is possible for a media failure to occur while the database remains open, leaving the undamaged datafiles online and available for use. Damaged datafiles—but not the tablespaces that contain them—are automatically taken offline if the database writer is unable to write to them. Queries that cannot read damaged files return errors, but the datafiles are not taken offline because of the failed queries. For example, you may run a query and see output such as:

ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 11: '/oracle/oradata/trgt/cwmlite02.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

The procedure in this section cannot be used to perform complete media recovery on the datafiles of the SYSTEM tablespace while the database is open. If the media failure damages datafiles of the SYSTEM tablespace, then the database automatically shuts down.

Perform media recovery in these stages:

1. Prepare the database for recovery by making sure it is open and taking the tablespaces requiring recovery offline, as described in "Preparing for Open Database Recovery".

   Restore the necessary files in the affected tablespaces as described in "Restoring Backups of the Inaccessible Datafiles".

   Recover the affected tablespaces as described in "Recovering Offline Tablespaces in an Open Database".

   See Also:

   • "Determining Which Datafiles Require Recovery"

   Preparing for Open Database Recovery

   In this stage, you take affected tablespaces offline and inspect the media device that is causing the problem.

   To prepare for datafile recovery when the database is open:

   If the database is open when you discover that recovery is required, take all tablespaces containing damaged datafiles offline. For example, if tablespace users and tools contain damaged datafiles, enter:

   ALTER TABLESPACE users OFFLINE TEMPORARY;
   ALTER TABLESPACE tools OFFLINE TEMPORARY;
   

   Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, proceed with database recovery by restoring damaged files to an alternative storage device.

   Restoring Backups of the Inaccessible Datafiles

   In this stage, you restore all necessary backups in the offline tablespaces.

   To restore datafiles in an open database:

   If files are permanently damaged, then restore the most recent backup files of only the datafiles damaged by the media failure. Do not restore undamaged datafiles, online redo logs, or control files. If the hardware problem is fixed and the datafiles can be restored to their original locations, then do so. Otherwise, restore the datafiles to an alternative storage device.

   Note:

   In some circumstances, if you do not have a backup of a specific datafile, you can use ALTER DATABASE CREATE DATAFILE to create an empty replacement file that is recoverable.

   If you restored one or more damaged datafiles to alternative locations, update the control file of the database to reflect the new datafile names. For example, to change the filename of the datafile in tablespace users you might enter:

   ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO
                              '/disk2/users01.dbf';
   

   See Also:

   Oracle Database SQL Reference for more information about ALTER DATABASE RENAME FILE

   Recovering Offline Tablespaces in an Open Database

   In the final stage, you recover the datafiles in the offline tablespaces.

   To recover offline tablespaces in an open database:

   Connect to the database with administrator privileges, and start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces using one step. For example, recover users and tools:

   RECOVER TABLESPACE users, tools;
   

   The database begins the roll forward phase of media recovery by applying the necessary redo logs (archived and online) to reconstruct the restored datafiles. Unless the applying of files is automated with RECOVER AUTOMATIC or SET AUTORECOVERY ON, the database prompts for each required redo log file.

   Recovery continues until all required archived logs have been applied to the datafiles. The online redo logs are then automatically applied to the restored datafiles to complete media recovery. If no archived redo logs are required for complete media recovery, then the database does not prompt for any. Instead, all necessary online redo logs are applied, and media recovery is complete.

   When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online. For example, to bring tablespaces users and tools online, issue the following statements:

   ALTER TABLESPACE users ONLINE;
   ALTER TABLESPACE tools ONLINE;
   

   See Also:

   Oracle Database Administrator's Guide for more information about creating datafiles

Preparing for Incomplete Recovery

In this phase, you examine the source of the media problem.

To prepare for incomplete recovery:

Restoring Datafiles Before Performing Incomplete Recovery

In this phase, you restore a whole database backup.

To restore the files necessary for cancel-based recovery and bring them online:

1. If the current control files do not match the physical structure of the database at the intended time of recovery, then restore a backup control file as described in "Restore Control File from Backup After Loss of All Current Control Files". The restored control file should reflect the database's physical file structure at the point at which incomplete media recovery should finish. To determine which control file backup to use:

   • Review the list of files that corresponds to the current control file and each control file backup to determine the correct control file to use.

2. If necessary, replace all current control files of the database with the correct control file backup.

3. Alternatively, create a new control file to replace the missing one.

   Note:

   If you are unable to restore a control file backup to one of the CONTROL_FILES locations, then edit the initialization parameter file so that this CONTROL_FILES location is removed.

   Restore backups of all datafiles of the database. All backups used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to January 2 at 2:00 p.m., then restore all datafiles with backups completed before this time. Follow these guidelines:

   If . . .	Then . . .
   You do not have a backup of a datafile	Create an empty replacement file that can be recovered as described in "Restoring Backups of the Damaged or Missing Files".
   A datafile was added after the intended time of recovery	Do not restore a backup of this file because it will no longer be used for the database after recovery completes.
   The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations	Restore the files as described in "Restoring Datafiles and Archived Redo Logs" and skip step 4 of this procedure.
   A hardware problem persists	Restore damaged datafiles to an alternative storage device.

   
   

Performing Cancel-Based Incomplete Recovery

In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specify CANCEL instead of a filename or when all redo has been applied to the datafiles.

Cancel-based recovery is better than change-based or time-based recovery if you want to control which archived log terminates recovery. For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied.

You should perform cancel-based media recovery in these stages:

To perform cancel-based recovery:

Performing Time-Based or Change-Based Incomplete Recovery

This section describes how to perform the time-based media recovery procedure in the following stages:

To perform change-based or time-based recovery:

About Opening with the RESETLOGS Option

The RESETLOGS option is always required after incomplete media recovery or recovery using a backup control file. Resetting the redo log does the following:

• Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.

• Creates the online redo log files if they do not currently exist.

• Reinitializes the control file metadata about online redo logs and redo threads.

• Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

Because the database will not apply an archived log to a datafile unless the RESETLOGS SCN and time stamps match, the RESETLOGS prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.

In prior releases, it was recommended that you back up the database immediately after the RESETLOGS. Because you can now easily recover a pre-RESETLOGS backup like any other backup, making a new database backup is optional. In order to perform recovery through resetlogs you must have all archived logs generated since the last backup and at least one control file (current, backup, or created).

Figure 18-1 shows the case of a database that can only be recovered to log sequence 2500 because an archived redo log is missing. When the online redo log is at sequence 4000, the database crashes. You restore the sequence 1000 backup and prepare for complete recovery. Unfortunately, one of your archived logs is corrupted. The log before the missing log contains sequence 2500, so you recover to this log sequence and open RESETLOGS. As part of the RESETLOGS, the database archives the current online logs (sequence 4000 and 4001) and resets the log sequence to 1.

You generate changes in the new incarnation of the database, eventually reaching log sequence 4000. The changes between sequence 2500 and sequence 4000 for the new incarnation of the database are different from the changes between sequence 2500 and sequence 4000 for the old incarnation. You cannot apply logs generated after 2500 in the old incarnation to the new incarnation, but you can apply the logs generated before sequence 2500 in the old incarnation to the new incarnation. The logs from after sequence 2500 are said to be orphaned in the new incarnation because they are unusable for recovery in that incarnation.

Figure 18-1 Creating a New Database Incarnation

Description of "Figure 18-1 Creating a New Database Incarnation"

Executing the ALTER DATABASE OPEN Statements

To preserve the log sequence number when opening a database after media recovery, execute either of the following statements:

ALTER DATABASE OPEN NORESETLOGS;
ALTER DATABASE OPEN;

To reset the log sequence number when opening a database after recovery and thereby create a new incarnation of the database, execute the following statement:

ALTER DATABASE OPEN RESETLOGS;

If you open with the RESETLOGS option, the database returns different messages depending on whether recovery was complete or incomplete. If the recovery was complete, then the following message appears in the alert_SID.log file:

RESETLOGS after complete recovery through change scn

If the recovery was incomplete, then this message is reported in the alert_SID.log file, where scn refers to the end point of incomplete recovery:

RESETLOGS after incomplete recovery UNTIL CHANGE scn

If you attempt to OPEN RESETLOGS when you should not, or if you neglect to reset the log when you should, then the database returns an error and does not open the database. Correct the problem and try again.

Checking the Alert Log After a RESETLOGS Operation

After opening the database with the RESETLOGS option, check the alert_SID.log to see whether the database detected inconsistencies between the data dictionary and the control file, for example, a datafile that the data dictionary includes but which is not listed in the new control file. The following table describes two possible scenarios.

Restoring a NOARCHIVELOG Database to its Default Location

In this scenario, the media failure is repaired so that you are able to restore all database files to their original location.

To restore the most recent whole database backup to the default location:

Restoring a NOARCHIVELOG Database to a New Location

In this scenario, you restore the database files to an alternative location because the original location is damaged by a media failure.

To restore the most recent whole database backup to a new location: