首页 > Linux操作系统 > Linux操作系统 > [zhuan]Great Tips for Oracle Cancel-Based Recovery!!

[zhuan]Great Tips for Oracle Cancel-Based Recovery!!

原创 Linux操作系统 作者:highclous 时间:2011-09-17 15:40:12 0 删除 编辑

It is very important to backup and recover your Oracle databases from time to time so as to protect your data from corruptions, hardware failures, and data failures. Although Oracle provides a number of great features for protecting your data but you should make sure that you have a valid and up to date backup of your database each time.

Being database administrator you must have experienced a number of scenarios where you need to backup your Oracle databases. Which type of recovery you should attempt depends on the situation. For example consider a situation where someone dropped a table and one of one of the online redo logs is missing and is not archived and the table needs to be recovered. Another case is where your backup control file does not know anything about the arhivelogs that got created after your last backup. Similarly another scenario can be where you have lost all logs pass a specific sequence say X and you want to control which archived log terminates recovery. Or a scenario where one of the archived redo log files required for the complete recovery is corrupt or missing and the only recovery option is to recover up to the missing archived redo log file. And the list goes on and on…..

Oracle Cancel-Based Recovery:

Oracle Cancel-Based Recovery is basically a user managed incomplete recovery where you use the UNTIL CANCEL clause to perform. recovery until the user manually cancels the recovery process. Oracle Cancel-Based Recovery is usually performed when there is a requirement to recover up to a particular archived redo log file. All recovery scenarios we discussed above need Oracle Cancel-Based Recovery.

The Oracle Cancel-Based Recovery process prompts user with the suggested archived redo log files' names and is stopped when the user specifies CANCEL instead of specifying an archived redo log file's name. If the user does not specify CANCEL then the recovery process will automatically stop when all redo has been applied to the datafiles.

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

Steps for performing Oracle Cancel-Based Recovery:

Follow below steps for performing Oracle Cancel-Based Recovery.

Create Connection:

First of all you will need to connect to Oracle database with administrator privileges.

% sqlplus '/ AS SYSDBA'

Mount Database:

Now you will have to start a new instance and mount your database.


Start Oracle Cancel-Based Recovery:

Now you will start Oracle Cancel-Based Recovery process with RECOVER DATABASE UNTIL CANCEL command. You will have to specify the USING BACKUP CONTROLFILE option if you are using backup control file with this incomplete recovery. If you are unable to specify the UNTIL clause then your database will not be open the database until a complete recovery is done.


Apply Redo Log Files:

Oracle reconstructs the restored datafiles by applying necessary redo log files to the restored datafiles. 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. Continue applying redo log files until the last log has been applied to the restored datafiles.

Cancel Recovery:

Now run CANCEL command to cancel the recovery process. When you issue the CANCEL command then Oracle returns a message that will indicate if your recovery is successful. The Recovery process may fail with ORA-1113 error if more recovery is necessary for a file and you cancel the recovery process and then try to open the database. This error is caused because not all datafiles have been recovered to a consistent SCN. Oracle V$RECOVER_FILE view can be queried so as to determine whether more recovery is needed or to determine if a backup of a datafile is not restored prior to starting incomplete recovery.

Open Database in RESETLOGS Mode:

Remember the online logs must be reset after you perform. an incomplete recovery or you perform. recovery with a backup control file. So finally you will need to open database in RESETLOGS mode.

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量