30 Performing User-Managed Database Flashback and Recovery

Typically, you restore a file when a media failure or user error has damaged or deleted one or more data files. In a user-managed restore operation, you use an operating system utility to restore a backup of the file.

If a media failure affects data files, then the recovery procedure depends on:

• The archiving mode of the database: ARCHIVELOG or NOARCHIVELOG

• The type of media failure

• The files affected by the media failure (data files, control files, archived redo logs, and the server parameter file are all candidates for restore operations)

If either a permanent or temporary media failure affects any data files of a database operating in NOARCHIVELOG mode, then the database automatically shuts down. If the media failure is temporary, then correct the underlying problem and restart the database. Usually, crash recovery recovers all committed transactions from the online redo log. If the media failure is permanent, then recover the database as described in "Recovering a Database in NOARCHIVELOG Mode".

Table 30-1 explains the implications for media recovery when you lose files in a database that runs in ARCHIVELOG mode.

To perform media recovery, Oracle recommends that you use the RECOVER statement in SQL*Plus. You can also use the SQL statement ALTER DATABASE RECOVER, but the RECOVER statement is often simpler. 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.

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

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 file names 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.

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.

  This task is described in Resetting the Archived Log Destination.

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

  This task is described in Overriding the Archived Log Destination.

Storage Snapshot Optimization enables you to use third-party snapshots of the database, taken when the database is not in backup mode, to recover the database either to the current time or to a specified point in time after the snapshot was created. If the database was not placed in backup mode when the storage snapshot was created, then you can perform recovery using this snapshot only if the snapshot conforms to Oracle requirements described in "Making Backups with Third-Party Snapshot Technologies". In this case, you can take the same basic recovery steps as any other backup method, either using RMAN or SQL*Plus.

If a storage snapshot was created when the database was not in backup mode, you must specify the SNAPSHOT TIME option while using this snapshot to recover the database. The SNAPSHOT TIME option can be used in both the RMAN or SQL*Plus RECOVER command. The time specified using the SNAPSHOT TIME option must be a time that is immediately after the snapshot is complete. If you specify an incorrect time, then the database may be corrupt in a way that is not repairable.

Because the time clocks in the storage array, where the snapshot takes place, and the machine hosting the Oracle Database may not be perfectly synchronized, it is recommended that you add a few seconds to the time that you specify in the SNAPSHOT TIME option. This helps you avoid any possibility of leaving the files in an inconsistent state by recovering to a point before the snapshot was taken.

All times specified in the RECOVER command, including in the SNAPSHOT TIME clause, are assumed to be in the time zone of the Oracle Database host. However, the time clocks in the storage array may be in a different time zone from the Oracle Database host. If the storage array reports its snapshot times in a different time zone, then you must take that difference into account when specifying the time in the SNAPSHOT TIME option.

The examples in this section use the RECOVER DATABASE command to perform recovery using snapshots. You can use the RECOVER DATABASE command from RMAN or SQL*Plus. However, the UNTIL CANCEL clause is valid only in SQL*Plus.

To completely recover a database:

RECOVER DATABASE;

To recover a database using a particular snapshot:

This example recovers uses a snapshot taken on August 15 at 2:00 P.M. to recover the database. The UNTIL TIME clause can specify any time after the snapshot.

RECOVER DATABASE UNTIL TIME '10/15/2012 15:00:00' SNAPSHOT TIME '10/15/2012 14:00:00';

To perform a partial recovery using archived redo log files:

This example uses the log files from a snapshot taken on August 15 at 2:00 P.M.

RECOVER DATABASE UNTIL CANCEL SNAPSHOT TIME '10/15/2012 14:00:00';

If you start media recovery and must then interrupt it, then either enter CANCEL when prompted for a redo log file, or use your operating system's interrupt signal if you must terminate when recovering an individual data file, 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.

By default, Oracle Database uses parallel media recovery to improve performance of the roll forward phase of media recovery. In parallel recovery of media, the database uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient. The number of processes used is derived from the CPU_COUNT initialization parameter, which by default equals the number of CPUs on the system. For example, if parallel recovery is performed on a system where CPU_COUNT is 4, and only one data file is recovered, then four spawned processes read blocks from the archive logs and apply redo.

Typically, media recovery is limited by data block reads and writes. Parallel recovery attempts to use all of the available I/O bandwidth of the system to improve performance. Unless there is a system I/O bottleneck or poor asynchronous I/O support, parallel recovery is likely to improve performance of recovery.

To override the default behavior of performing parallel recovery, use the SQL*Plus RECOVER command with the NOPARALLEL option, or RECOVER PARALLEL 0. The RECOVERY_PARALLELISM initialization parameter controls instance or crash recovery only. Media recovery is not affected by the value used for RECOVERY_PARALLELISM.

1. If the database is open, query V$RECOVER_FILE to determine which data files must be recovered and why they must be recovered.

   If you are planning to perform complete recovery rather than point-in-time recovery, then you can recover only those data files that require recovery, rather than the whole database. For point-in-time recovery, you must restore and recover all data files, unless you perform TSPITR as described in Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) . You can also use Flashback Database, but this procedure affects all data files and returns the entire database to a past time.

   You can query the V$RECOVER_FILE view to list data files requiring recovery by data file number with their status and error information.

   SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME 
   FROM   V$RECOVER_FILE;
   

   Note:

   You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the data files. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.

   You can also perform useful joins by using the data file number and the V$DATAFILE and V$TABLESPACE views to get the data file and tablespace names.

   The ERROR column identifies the problem for each file requiring recovery.

2. Query the V$ARCHIVED_LOG and V$RECOVERY_LOG views to determine which archived redo log files are needed.

   V$ARCHIVED_LOG lists file names for all archived redo logs, whereas V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery. The latter view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.

   Note:

   V$RECOVERY_LOG is only populated when media recovery is required for a data file. Thus, this view is not useful for a planned recovery, such as recovery from a user error.

   If a data file requires recovery, but no backup of the data file exists, then you need all redo generated starting from the time when the data file was added to the database.

3. If all archived logs are available in the default location, then skip to the Step 4.

   If some archived logs must be restored, and if sufficient space is available, then restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1. The database locates the correct log automatically when required during media recovery. For example, you might enter a command such as the following on Linux or UNIX:

   % cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch
   

   If sufficient space is not available, then restore some or all of the required archived redo log files to an alternative location.

4. If the database is open, then shut it down. For example:

   SHUTDOWN IMMEDIATE
   

5. Inspect the media to determine the source of the problem.

   If the hardware problem that caused the media failure was temporary, and if the data was undamaged (for example, a disk or controller power failure occurred), then no media recovery is required: start the database and resume normal operations.

   If you cannot repair the problem, then proceed to the Step 6.

6. If the files are permanently damaged, then identify the most recent backups for the damaged files. Restore only the data files damaged by the media failure: do not restore undamaged data files or any online redo log files.

   For example, if ORACLE_HOME/oradata/trgt/users01.dbf is the only damaged file, then you may find that /backup/users01_10_24_02.dbf is the most recent backup of this file. If you do not have a backup of a specific data file, then you may be able to create an empty replacement file that can be recovered.

7. Use an operating system utility to restore the data files to their default location or to a new location. For example, a Linux or UNIX user restoring users01.dbf to its default location might enter:

   % cp /backup/users01_10_24_06.dbf $ORACLE_HOME/oradata/trgt/users01.dbf
   

   Use the following guidelines when determining where to restore data file backups:

   • If the hardware problem is repaired and you can restore the data files to their default locations, then restore the data files to their default locations and begin media recovery.

   • If the hardware problem persists and you cannot restore data files to their original locations, then restore the data files to an alternative storage device. Indicate the new location of these files in the control file with the ALTER DATABASE RENAME FILE statement. See Oracle Database Administrator's Guide.

   • If you are restoring a data file to a raw disk or partition, then the technique is basically the same as when you are restoring to a file on a file system. 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.

8. Connect to the database with administrator privileges. Then start a new instance and mount, but do not open, the database. For example, enter:

   STARTUP MOUNT
   

9. If you restored one or more damaged data files to alternative locations, then update the control file of the database to reflect the new data file names. For example, to change the file name of the data file in tablespace users you might enter:

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

10. Obtain the data file names and statuses of all data files by checking the list of data files that normally accompanies the current control file or by querying the V$DATAFILE view. For example, enter:

    SELECT NAME,STATUS FROM V$DATAFILE;
    

11. Ensure that all data files requiring recovery are online. The only exceptions are data files in an offline tablespace that was taken offline normally or data files in a read-only tablespace. For example, to guarantee that a data file named /oracle/dbs/tbs_10.f is online, enter the following:

    ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;
    

    If a specified data file is already online, then the database ignores the statement. If you prefer, create a script to bring all data files online simultaneously, as in the following example:

    SPOOL onlineall.sql
    SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM V$DATAFILE;
    SPOOL OFF
    
    SQL> @onlineall
    

12. If you restored archived redo logs to an alternative location, then you can specify the location before media recovery with the LOGSOURCE parameter of the SET command in SQL*Plus. For example, if the logs are staged in /tmp, you can enter the following command:

    SET LOGSOURCE /tmp
    

    Alternatively, you can skip Step 12 and use the FROM parameter on the RECOVER command as in Step 13. For example, if the logs are staged in /tmp, you can enter the following command:

    RECOVER AUTOMATIC FROM '/tmp' DATABASE
    

    Note:

    Overriding the redo log source does not affect the archive redo log destination for online redo log groups being archived.

13. Issue a statement to recover the database, tablespace, or data file. For example, enter one of the following RECOVER commands:

    RECOVER AUTOMATIC DATABASE   # whole database
    RECOVER AUTOMATIC TABLESPACE users   # specific tablespace
    RECOVER AUTOMATIC DATAFILE '?/oradata/trgt/users01.dbf'; # specific data file
    

    If you choose not to automate the application of archived redo logs, then you must accept or reject each prompted log. If you automate recovery, then the database applies the logs automatically. Recovery continues until all required archived and online redo logs have been applied to the restored data files. The database notifies you when media recovery is complete:

    Media recovery complete.
    

    If no archived redo logs are required for complete media recovery, then the database applies all necessary online redo log files and terminates recovery.

14. After recovery terminates, open the database for use:

    ALTER DATABASE OPEN;
    

    See Also:

    "Overview of User-Managed Media Recovery" for more information about applying redo log filesOracle Database Reference

15. After archived logs are applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example:

    % rm /tmp/*.arc
    

    See Also:

    "Overview of User-Managed Media Recovery" for an overview of log application during media recovery

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

1. Follow Step 1 through Step 3 in "Performing Closed Database Recovery".
2. If the database is open, then take all tablespaces containing damaged data files offline. For example, if the tablespaces USERS and TOOLS contain damaged data files, then execute the following SQL statements:

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

   If you specify TEMPORARY, then Oracle Database creates a checkpoint for all online data files in the tablespace. Files that are offline when you issue this statement may require media recovery before you bring the tablespace back online. If you specify IMMEDIATE, then you must perform media recovery on the tablespace before bringing it back online.

3. Inspect the media to determine the source of the problem.

   As explained in "Running the DBVERIFY Utility", you can use the DBVERIFY utility to run an integrity check on offline data files.

   If the hardware problem that caused the media failure was temporary, and if the data was undamaged, then no media recovery is required. You can bring the offline tablespaces online and resume normal operations. If you cannot repair the problem, or if DBVERIFY reports corrupt blocks, then proceed to the Step 4.

4. If files are permanently damaged, then use operating system commands to restore the most recent backup files of only the data files damaged by the media failure. For example, to restore users01.dbf you might use the cp command on Linux or UNIX as follows:

   % cp /disk2/backup/users01.dbf $ORACLE_HOME/oradata/trgt/users01.dbf
   

   If the hardware problem is fixed and the data files can be restored to their original locations, then do so. Otherwise, restore the data files to an alternative storage device. Do not restore undamaged data files, online redo logs, or control files.

   Note:

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

5. If you restored one or more damaged data files to alternative locations, then update the control file of the database to reflect the new data file names. For example, to change the file name of the data file in tablespace users you might enter:

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

6. If you restored archived redo logs to an alternative location, then you can specify the location before media recovery with the LOGSOURCE parameter of the SET command in SQL*Plus. For example, if the logs are staged in /tmp, you can enter the following command:

   SET LOGSOURCE /tmp
   

   Alternatively, you can skip Step 6 and use the FROM parameter on the RECOVER command as in Step 7. For example, if the logs are staged in /tmp, you can enter the following command:

   RECOVER AUTOMATIC FROM '/tmp' TABLESPACE users, tools;
   

   Note:

   Overriding the redo log source does not affect the archive redo log destination for online redo log groups being archived.

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

   RECOVER AUTOMATIC TABLESPACE users, tools;
   

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

   Recovery continues until all required archived logs have been applied to the data files. The online redo logs are then automatically applied to the restored data files 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.

8. 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;
   

1. Open a SQL client such as SQL*Plus.
2. Connect to the root as a user with the ALTER PLUGGABLE DATABASE system privilege.
3. Follow the procedures in "Performing Closed Database Recovery".

   If you do not want to recover a particular PDB, take its files offline.

1. Follow Step1 through Step 8 in "Performing Closed Database Recovery".
2. Begin cancel-based recovery by issuing the following command in SQL*Plus:

   RECOVER DATABASE UNTIL CANCEL
   

   Note:

   If you fail to specify the UNTIL clause on the RECOVER command, then the database assumes a complete recovery and does not open until all redo is applied.

   The database applies the necessary redo log files to reconstruct the restored data files. The database supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. If the control file is a backup, then you must supply the names of the online redo logs if you want to apply the changes in these logs.

3. Continue applying redo log files until the last log has been applied to the restored data files, then cancel recovery by executing the following command:

   CANCEL
   

   The database indicates whether recovery is successful. If you cancel before all the data files have been recovered to a consistent SCN and then try to open the database, then you get an ORA-1113 error if more recovery is necessary. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a data file was not restored before starting incomplete recovery.

4. Open the database with the RESETLOGS option. You must always reset the logs after incomplete recovery or recovery with a backup control file. For example:

   ALTER DATABASE OPEN RESETLOGS;
   

   If you attempt to use 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.

   See Also:

   "About User-Managed Media Recovery Problems" for descriptions of situations that can cause ALTER DATABASE OPEN RESETLOGS to fail

5. After opening the database with the RESETLOGS option, check the alert log.

   Note:

   The easiest way to locate trace files and the alert log is to run the following SQL query: SELECT NAME, VALUE FROM V$DIAG_INFO.

   When 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 log:

   RESETLOGS after complete recovery through change scn
   

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

   RESETLOGS after incomplete recovery UNTIL CHANGE scn
   

   Also check the alert log to determine whether the database detected inconsistencies between the data dictionary and the control file. Table 30-3 describes two possible scenarios.

   ---

   Table 30-3 Inconsistencies Between Data Dictionary and Control File

   Data File Listed in Control File	Data File Listed in the Data Dictionary	Result
   Yes	No	References to the unlisted data file are removed from the control file. A message in the alert log indicates what was found.
   No	Yes	The database creates a placeholder entry in the control file under MISSINGnnnnn (where nnnnn is the file number in decimal). MISSINGnnnnn is flagged in the control file as offline and requiring media recovery. You can make the data file corresponding to MISSINGnnnnn accessible by using ALTER DATABASE RENAME FILE for MISSINGnnnnn so that it points to the data file. If you do not have a backup of this data file, then drop the tablespace.

   ---

1. Follows Step 1 through Step 8 in "Performing Closed Database Recovery".
2. Issue the RECOVER DATABASE UNTIL statement to begin recovery. If recovering to an SCN, then specify as a decimal number without quotation marks. For example, to recover through SCN 10034 issue:

   RECOVER DATABASE UNTIL CHANGE 10034;
   

   If recovering to a time, then the time is always specified using the following format, delimited by single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'. The following statement recovers the database up to a specified time:

   RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30'
   

3. Apply the necessary redo log files to recover the restored data files. The database automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.

   Note:

   Unless recovery is automated, the database supplies the name from LOG_ARCHIVE_DEST_1 and asks you to stop or proceed with after each log. If the control file is a backup, then after the archived logs are applied you must supply the names of the online logs.

4. Follow Steps 4 and 5 in "Performing Cancel-Based Incomplete Recovery".

Table 30-4 describes potential problems that can occur during media recovery.

The symptoms of media recovery problems are usually external or internal errors signaled during recovery. For example, an external error indicates that a redo block or a data block has failed checksum verification checks. Internal errors can be caused by either bugs in the database or errors arising from the underlying operating system and hardware.

If media recovery encounters a problem while recovering a database backup, then whether it is a stuck recovery problem or a problem during redo application, the database always stops and leaves the data files undergoing recovery in a consistent state, that is, at a consistent SCN preceding the failure. You can then do one of the following:

• Open the database read-only to investigate the problem.

• Open the database with the RESETLOGS option, if the requirements for opening RESETLOGS have been met. The RESETLOGS restrictions apply to opening the physical standby database as well, because a standby database is updated by a form of media recovery.

In general, opening the database read-only or opening with the RESETLOGS option requires all online data files to be recovered to the same SCN. If this requirement is not met, then the database may signal ORA-1113 or other errors when you attempt to open it. Some common causes of ORA-1113 are described in Table 30-4.

The basic methodology for responding to media recovery problems occurs in the following phases:

1. Try to identify the cause of the problem. Run a trial recovery if needed.

2. If the problem is related to missing redo logs or if you suspect that there is a redo log, memory, or data block corruption, then try to resolve the problem using the methods described in Table 30-5.

3. If you cannot resolve the problem using the methods described in Table 30-5, then do one of the following:

   • Open the database with the RESETLOGS option if you are recovering a whole database backup. If you have performed serial media recovery, then the database contains all the changes up to but not including the changes at the SCN where the corruption occurred. No changes from this SCN onward are in the recovered part of the database. If you have restored online backups, then opening RESETLOGS succeeds only if you have recovered through all the ALTER ... END BACKUP operations in the redo stream.

   • Proceed with recovery by allowing media recovery to corrupt data blocks. After media recovery completes, try performing block media recovery using RMAN.

   • Call Oracle Support Services as a last resort.

     See Also:

     "Performing Disaster Recovery" to learn about block media recovery

1. Examine the alert.log to see whether the error messages give general information about the nature of the problem. For example, does the alert_SID.log indicate any checksum failures? Does the alert_SID.log indicate that media recovery may have to corrupt data blocks to continue?
2. Check the trace file generated by the Oracle Database during recovery. It may contain additional error information.

1. Ensure that all normal recovery preconditions are met. For example, if the database is open, then take tablespaces offline before attempting recovery.
2. Run the RECOVER command as in the following example:

   RECOVER DATABASE ALLOW 5 CORRUPTION
   

When problems such as stuck recovery occur, you have a difficult choice. If the block is relatively unimportant, and if the problem is isolated, then it is better to corrupt the block. But if the problem is not isolated, then it may be better to open the database with the RESETLOGS option.

Because of this situation, Oracle Database supports trial recovery. A trial recovery applies redo in a way similar to normal media recovery, but it never writes its changes to disk and it always rolls back its changes. Trial recovery occurs only in memory.