Performing Incomplete Database Recovery

Incomplete recovery is also known as database point-in-time recovery.

DBPITR与数据库完全恢复过程相似，只是需要指定恢复到的时间、SCN或使用CANCEL选项

Cancel-based recovery prompts you with the suggested file names of archived redo logs. Recovery stops when you specify CANCEL instead of a file name or when all redo has been applied to the data files. Cancel-based recovery is the best technique to control which archived log terminates recovery.

（一）Performing Cancel-Based Incomplete Recovery

This procedure assumes the following:

1. The current control file is available.
2. You have backups of all needed data files.

To perform cancel-based recovery:

1. Determine which data file need recovery using V$RECOVER_FILE

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

FROM V$RECOVER_FILE;

1. Determine which archivelogs are needed using V$RECOVERY_LOG and V$ARCHIVED_LOG

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. V$RECOVERY_LOG view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.

如果需要使用归档日志备份，则在恢复前需要先还原归档日志

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

SHUTDOWN IMMEDIATE

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

1. 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.

Note: 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.

1. Use an operating system utility to restore the data files:

% 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:

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

1. 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

1. Begin cancel-based recovery by issuing the following command in SQL*Plus:

RECOVER DATABASE UNTIL CANCEL

数据库会按提示是停止还是应用归档，如果使用备份控制文件要指定应用的归档日志

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.

1. 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

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.

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

1. after opening the database with the RESETLOGS option, check the alert log.

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.

这种情况是通过dba_data_files与v$datafile查看是否一致？

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.

（二）Performing Time-Based or Change-Based Incomplete Recovery

This procedure assumes the following:

1. The current control file is available.
2. You have backups of all needed data files.

To perform change-based or time-based recovery:

1. Determine which data file need recovery using V$RECOVER_FILE

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

FROM V$RECOVER_FILE;

1. Determine which archivelogs are needed using V$RECOVERY_LOG and V$ARCHIVED_LOG

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. V$RECOVERY_LOG view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.

如果需要使用归档日志备份，则在恢复前需要先还原归档日志

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

SHUTDOWN IMMEDIATE

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

1. 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.

Note: 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.

1. Use an operating system utility to restore the data files:

% 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:

1. 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.

1. 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.

1. 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

1. Issue the RECOVER DATABASE UNTIL statement to begin recovery. If recovering to an SCN, then specify as a decimal number without quotation marks:

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':

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

1. 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.

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

1. after opening the database with the RESETLOGS option, check the alert log.

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.