RMAN cancel based recovery !!!

source :
 http://dba-oracle.com/bk_recover_database_until_cancel_tips.htm
 http://www.dba-oracle.com/t_oracle_open_resetlogs_tips.htm


Problem:  How do I recover until cancel with Oracle when doing a roll forward?

Can someone explain why it is necessary to perform "recover database until cancel" before using "alter database open resetlogs" when you are restoring a cold backup without a backup of the redo log files ?

I perform the following steps, which I thought would be very simple, however I get an error on opening the database.

SHUTDOWN IMMEDIATE

Restore all backup datafiles and controlfiles (OS copy)

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS

ORA-01139: RESETLOGS option only valid after an incomplete database recovery
So I tried using: 

RECOVER DATABASE UNTIL CANCEL

ALTER DATABASE OPEN RESETLOGS

And this works, the database is opened and the new redo logs are created.

I got a trace of my controlfile and found the following..

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVEMODE .....
I think it is no NORESETLOGS that is causing the problem as if I re-create the control file with RESETLOGS specified it works fine.

Solution:  The Oracle documentation notes:

"In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specifyCANCEL instead of a filename or when all redo has been applied to the datafiles.

Cancel-based recovery is better than change-based or time-based recovery if you want to control which archived log terminates recovery. For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied.

You should perform cancel-based media recovery in these stages:

1. Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".
2. Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.
3. Perform media recovery on the restored database backup as described in the following procedure.

To perform cancel-based recovery:

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 CANCEL
   
   If 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

   Note:If you fail to specify the UNTIL clause on the RECOVERcommand, then you will not be able to open the database until a complete recovery is done.

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find fromLOG_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.

Note:If you use an Oracle Real Application Clusters configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only compute the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. After the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent log files in this thread.

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:
   CANCEL
   
   Oracle 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-1113error if more recovery is necessary for the file. You can queryV$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;"

Oracle RMAN Simplified Recovery Through OPEN RESETLOGS

Following an incomplete point-in-time recovery or recovery without a backup control file, it will be necessary to open the database with the OPEN RESETLOGS option as follows:

SQL> alter database open resetlogs;

The OPEN RESETLOGS operation creates a new incarnation of the database, resets the log sequence to 1 and online redo logs are given a new time stamp and SCN.  Prior to Oracle 10g, the newly generated redo log files could not be used with the backups taken in the past.  Therefore, it was historically important to take an immediate backup as all previous backups became invalid.  Using OPEN RESETLOGS can negate the need for that backup.

Without the use of OPEN RESETLOGS, when using the RMAN catalog for future backups, the following command had to be issued to make the RMAN catalog aware of the new incarnation of the database.

RMAN> reset database;

As of Oracle 10g, it is no longer necessary to back up the database following an incomplete recovery and OPEN RESETLOGS operations. 

The OPEN RESETLOGS feature is also applicable for the following two scenarios:

• Performing a recovery using a backup control file and opening the database with the RESETLOGS operation. 
• Reinstantiation of  the old primary database following a failover

When the new database incarnation is created following the use of OPEN RESETLOGS and the log sequence reset, orphaned backups are created.  These orphaned backups can be used by RMAN to restore the database to a point in time not in the current incarnation path.
It is possible for RMAN to restore backups from direct ancestor incarnations and recover up to the current time as long as a continuous path of archived logs exists from the earliest backups to the desired point of recovery.  This holds true even across OPEN RESETLOGS operations.
 Under certain circumstances RMAN can also perform restore and recovery with orphaned backups via the restoration of a control file from an appropriate incarnation.