Oracle Cancel-Based Recovery
A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER
command (a SQL*Plus command that is used to recover a database). The
UNTIL CANCEL clause specifies that the recovery process will continue
until the user manually cancels the recovery process issuing the CANCEL
command.
In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.
A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.
1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
2. Start a new instance and mount the database:
3. Begin cancel-based recovery by issuing the following command:
4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle 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.
5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
6. 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, enter:
In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.
A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.
| Recovery Scenario | Preferred Recovery Method |
| Some important table is dropped | Oracle Time-based Recovery |
| Some bad data is committed in a table | Oracle Time-based Recovery |
| Lost archive log results in failure of complete recovery | Oracle Cancel-based Recovery |
| Backup control file does not know anything about the arhivelogs | Oracle Cancel-based Recovery |
| All unarchived Redo Logs and datafiles are lost | Oracle Cancel-based Recovery |
| Recovery is needed up to a specific archived log file | Oracle Cancel-based Recovery |
| Recovery through Resetlogs when media failure occurs before backup completion. | Oracle Change-based Recovery |
| A Tablespace is dropped | Recovery with a backup control file |
1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
sqlplus '/ AS SYSDBA'
2. Start a new instance and mount the database:
STARTUP MOUNT
3. Begin cancel-based recovery by issuing the following command:
RECOVER DATABASE UNTIL CANCELIf 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
4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle 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.
5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
CANCELOracle returns a message indicating whether recovery is successful. Note that if you cancel recovery 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 for the file. 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.
6. 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, enter:
ALTER DATABASE OPEN RESETLOGS;
Comments