Incomplete Media Recovery

From Oracle FAQ
Jump to: navigation, search

There are three basic media recovery commands, which differ only in the way the set of files being recovered is determined. They all use the same criteria for determining if files can be recovered. Media recovery signals an error if it cannot get the lock for a file it is attempting to recover. This prevents two recovery sessions from recovering the same file. It also prevents media recovery of a file that is in use. You should be familiar with all media recovery commands before performing media recovery.

  • RECOVER DATABASE Command - RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. If all instances were cleanly shutdown, and no backups were restored, RECOVER DATABASE indicates a no recovery required error. It also fails if any instances have the database open (since they have the datafile locks). To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.
  • RECOVER TABLESPACE Command - RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. To translate the tablespace names into datafile names, the database must be mounted and open. The tablespaces must be offline to perform the recovery. An error is indicated if none of the files require recovery.
  • RECOVER DATAFILE Command - RECOVER DATAFILE lists the datafiles to be recovered. The database can be open or closed, provided the media recovery locks can be acquired. If the database is open in any instance, then datafile recovery can only recover offline files.

InComplete Media Recovery can be classified into three categories:

  • Performing Cancel-Based Recovery
    • 1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
    • 2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
    • 3. If a media failure occurred, correct the hardware problem that caused the media failure.
    • 4. If the current control files do not match the physical structure of the database at the intended time of recovery (for example, if a datafile was added after the point in time to which you intend to recover), then restore a backup of the control file that reflects the databases physical file structure (contains the names of datafiles and online redo log files) at the point at which incomplete media recovery is intended to finish. Review the list of files that correspond to the current control file as well as 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. You can, alternatively, create a new control file to replace the missing one.
    • 5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38. If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
    • 6. Start Server Manager and connect to Oracle with administrator privileges.
    • 7. Start a new instance and mount the database. You can perform this operation using the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
    • 8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
    • 9. If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored in Step 4), indicate this in the dialog box or command used to start recovery (that is, specify the USING BACKUP CONTROLFILE parameter).
    • 10. Use Server Manager Apply Recovery Archives dialog box, or an equivalent RECOVER DATABASE UNTIL CANCEL statement to begin cancel-based recovery.
    • 11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs. Oracle continues to apply redo log files.
    • 12. Continue applying redo log files until the most recent, undamaged redo log file has been applied to the restored datafiles.
    • 13. Enter "CANCEL" to cancel recovery after Oracle has applied the redo log file just prior to the damaged file. Cancel-based recovery is now complete. Oracle returns a message indicating whether recovery is successful.
    • 14. The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option.
      • Resetting the redo log
        • discards any redo information that was not applied during recovery, ensuring that it will never be applied
        • reinitializes the control file information about online redo logs and redo threads
        • clears the contents of the online redo logs
        • creates the online redo log files if they do not currently exist
        • resets the log sequence number to 1
      • NoReset Logs - To preserve the log sequence number when opening a database after recovery, use the SQL command ALTER DATABASE with the OPEN NORESETLOGS option.
  • Performing Time-Based Recovery -
    • 1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
    • 2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
    • 3. If a media failure occurred, correct the hardware problem that caused the media failure.
    • 4. If the current control files do not match the physical structure of the database at the intended time of recovery (for example, if a datafile was added after the point in time to which you intend to recover), then restore a backup of the control file that reflects the databases physical file structure (contains the names of datafiles and online redo log files) at the point at which incomplete media recovery is intended to finish. Review the list of files that correspond to the current control file as well as 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. You can, alternatively, create a new control file to replace the missing one.
    • 5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38. If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
    • 6. Start Server Manager and connect to Oracle with administrator privileges.
    • 7. Start a new instance and mount the database. You can perform this operation using the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
    • 8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
    • 9. All datafiles of the database must be online unless an offline tablespace was taken offline normally. To get the names of all datafiles to recover, check the list of datafiles that normally accompanies the control file being used or query the V$DATAFILE view. Then, use the ALTER DATABASE command and the DATAFILE ONLINE option to make sure that all datafiles of the database are online.
    • 10. Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: YYYY-MM-DD:HH24:MI:SS.
    • 11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs. Oracle continues to apply redo log files.
    • 12. Continue applying redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.
    • 13. The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option.
    • Use the following rules when deciding to specify RESETLOGS or NORESETLOGS:
      • Reset the log sequence number if you used a backup of the control file in recovery, no matter what type of recovery was performed (complete or incomplete).
      • Reset the log sequence number if the recovery was actually incomplete. For example, you must have specified a previous time or SCN, not one in the future.
      • Do not reset logs if recovery was complete (unless you used a backup control file). This applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway.
      • Do not reset logs if you are using the archived logs of this database for a standby database. If the log must be reset, then you will have to re-create your standby database.
  • Performing Change-Based Recovery -
    • 1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
    • 2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
    • 3. If a media failure occurred, correct the hardware problem that caused the media failure.
    • 4. If the current control files do not match the physical structure of the database at the intended time of recovery (for example, if a datafile was added after the point in time to which you intend to recover), then restore a backup of the control file that reflects the databases physical file structure (contains the names of datafiles and online redo log files) at the point at which incomplete media recovery is intended to finish. Review the list of files that correspond to the current control file as well as 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. You can, alternatively, create a new control file to replace the missing one.
    • 5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38. If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
    • 6. Start Server Manager and connect to Oracle with administrator privileges.
    • 7. Start a new instance and mount the database. You can perform this operation using the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
    • 8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
    • 9. All datafiles of the database must be online unless an offline tablespace was taken offline normally. To get the names of all datafiles to recover, check the list of datafiles that normally accompanies the control file being used or query the V$DATAFILE view. Then, use the ALTER DATABASE command and the DATAFILE ONLINE option to make sure that all datafiles of the database are online.
    • 10. Issue the RECOVER DATABASE UNTIL CHANGE statement to begin change-based recovery. The SCN is specified as a decimal number without quotation marks.
    • 11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs. Oracle continues to apply redo log files.
    • 12. Continue applying redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.
    • 13. The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option.
    • Use the following rules when deciding to specify RESETLOGS or NORESETLOGS:
      • Reset the log sequence number if you used a backup of the control file in recovery, no matter what type of recovery was performed (complete or incomplete).
      • Reset the log sequence number if the recovery was actually incomplete. For example, you must have specified a previous time or SCN, not one in the future.
      • Do not reset logs if recovery was complete (unless you used a backup control file). This applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway.
      • Do not reset logs if you are using the archived logs of this database for a standby database. If the log must be reset, then you will have to re-create your standby database.
Retrieved from "http://orafaq.com/wiki/index.php?title=Incomplete_Media_Recovery&oldid=11333"