Oracle7 Server Administrator's Guide
Performing Incomplete Media Recovery
This section descrines the steps necessary to complete the different types of incomplete media recovery operations, and includes the following topics:
- Performing Cancel-Based Recovery
- Performing Time-Based Recovery
- Performing Change-Based Recovery
See Also: Do not rely solely on this section to understand the procedures necessary to recover from a media failure. Also see "Examples of Media Failures and Appropriate Recovery Procedures" ![[*]](jump.gif)
for a detailed list of the different types of problems that media failures can cause, and the appropriate methods of recovery from each type of problem.
Changing the System Time on a Running Database
If your database is affected by seasonal time changes (for example, daylight savings time), you may experience a problem if a time appears twice in the redo log and you want to recover to the second, or later time. To deal with time changes, perform cancel-based or change-based recovery to the point in time where the clock is set back, then continue with the time-based recovery to the exact time.
Performing Cancel-Based Recovery
This section describes how to perform cancel-based recovery.:
To Perform 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.
Note: If a database control file cannot function or be replaced with a control file backup, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.
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.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If 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, recovery will try to update the headers of the read-only files.
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.
Opening the Database After Successful Cancel-Based Recovery
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
Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.
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. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- 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.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)
After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).
If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" .
For more information about creating datafiles, see "Restoring Damaged Datafiles" .
To relocate or rename datafiles, see "Renaming and Relocating Datafiles" , as necessary.
For more information about listing datafiles, see "Listing Database Files Before Backup" .
For more information about applying redo logs, see "Applying Redo Log Files" .
Performing Time-Based Recovery
When you are performing time-based, incomplete media recovery, and you are recovering with a backup control file and have read-only tablespaces, contact Oracle Support before attempting this recovery procedure.
To Perfrom 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.
Note: If a database control file cannot function or be replaced with a control file backup because the hardware problem causing the media failure persists, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.
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.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If 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.
6. Start Server Manager and connect to Oracle with administrator privileges.
7. Start a new instance and mount the database. This operation can be performed with 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. For example, to guarantee that a datafile named USERS1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
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), indicate this in the dialog box or command used to start recovery. If a specified datafile is already online, Oracle ignores the statement.
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.
Opening the Database After Successful Time-Based Recovery
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
Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.
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. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- 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.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)
After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).
If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" .
For more information about creating datafiles, see "Restoring Damaged Datafiles" .
To relocate or rename datafiles, see "Renaming and Relocating Datafiles" , as necessary.
For more information about listing datafiles, see "Listing Database Files Before Backup" .
For more information about applying redo logs, see "Applying Redo Log Files" .
Performing Change-Based Recovery
This section describes how to perform change-based recovery.
To Perform 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.
Note: If a database control file cannot function or be replaced with a control file backup, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.
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.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If 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, recovery will try to update the headers of the read-only files.
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. To get the names of all datafiles to recover, check the list of datafiles that normally accompany the control file being used or query the V$DATAFILE view. Then, use the ALTER DATABASE command with the DATAFILE ONLINE option to make sure that all datafiles of the database are online. For example, to guarantee that a datafile named USERS1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
If a specified datafile is already online, Oracle ignores the statement.
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), specify the USING BACKUP CONTROLFILE parameter in the dialog box or command used to start recovery.
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.
Opening the Database After Successful Change-Based Recovery
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
Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.
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. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- 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.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)
After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).
If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" .
For more information about creating datafiles, see "Restoring Damaged Datafiles" .
To relocate or rename datafiles, see "Renaming and Relocating Datafiles" , as necessary.
For more information about listing datafiles, see "Listing Database Files Before Backup" .
For more information about applying redo logs, see "Applying Redo Log Files" .