Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Performing User-Managed Database Point-in-Time Recovery

Preparing for Incomplete Recovery

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

To prepare for incomplete recovery:

  1. If you are uncertain about performing incomplete recovery, then back up the whole database—all datafiles, a control file, and the parameter files—as a precautionary measure in case an error occurs during the recovery procedure.

  2. If the database is still open and incomplete media recovery is necessary, then terminate the instance:

    SHUTDOWN ABORT
    
    
  3. If a media failure occurred, correct the hardware problem that caused the failure. If the hardware problem cannot be repaired quickly, then proceed with database recovery by restoring damaged files to an alternative storage device.

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.

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

    • 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.
  2. 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.

Note:

Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, the recovery will try to update the headers of the read-only files.
  1. Start SQL*Plus and connect to the database with administrator privilege, then start a new instance and mount the database:

    STARTUP MOUNT
    
    
  2. If one or more damaged datafiles were restored to alternative locations in step 2, then indicate the new locations of these files to the control file of the associated database. For example, enter:

    ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO 
                               '/disk2/users01.dbf';
    
    
  3. Obtain the datafile names and statuses of all datafiles by checking the list of datafiles that normally accompanies the current control file or querying the V$DATAFILE view. For example, enter:

    SELECT NAME,STATUS FROM V$DATAFILE;
    
    
  4. Ensure that all datafiles of the database are online. All datafiles of the database requiring recovery must be online unless a tablespace was taken offline with the NORMAL option or is a read-only tablespace. For example, to guarantee that a datafile named ?/oradata/trgt/users01.dbf is online, enter the following:

    ALTER DATABASE DATAFILE '?/oradata/trgt/users01.dbf' ONLINE;
    
    

    If a specified datafile is already online, the statement has no effect. If you prefer, create a script to bring all datafiles online at once as in the following:

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

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:

  1. Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".

  2. Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.

  3. Perform media recovery on the restored database backup as described in the following procedure.

To perform cancel-based recovery:

  1. Start SQL*Plus and connect to the database with administrator privileges, then start a new instance and mount the database:

    STARTUP MOUNT
    
    
  2. Begin cancel-based recovery by issuing the following command:

    RECOVER DATABASE UNTIL CANCEL
    
    

    If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
    

    Note:

    If you fail to specify the UNTIL clause on the RECOVER command, then the database assumes a complete recovery and will not open until all redo is applied.
  3. The database applies the necessary redo log files to reconstruct the restored datafiles. 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. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

    Note:

    If you use a Real Application Clusters (RAC) configuration, and you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent log files in this thread.
  4. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

    CANCEL
    
    

    The database indicates whether recovery is successful. If you cancel before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary. As explained in "Determining Which Datafiles Require Recovery", you can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

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

Performing Time-Based or Change-Based Incomplete Recovery

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

  1. Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".

  2. Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.

  3. Perform media recovery with the following procedure.

To perform change-based or time-based recovery:

  1. Issue the RECOVER DATABASE UNTIL statement to begin recovery. If recovering to an SCN, 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, 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'
    
    
  2. Apply the necessary redo log files to recover the restored datafiles. 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.
  3. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example:

    ALTER DATABASE OPEN RESETLOGS;