CANCEL if you want to work!

Ohoo, this is quite nice! Usually, you wouldn't expect to make something to work unless you specify CANCEL, right? One interesting case is when you want to recreate the control file with the RESETLOGS option. The scenario is simple, you have an old backup which contains all your datafiles and archivelogs and, in addition, you was smart enough to backup the control file to trace. Now supposing you lost all current datafiles and the current redolog files. You have to restore and recover from your backup. The first thing is to restore your datafiles to the known location, to start the instance in the nomount state and to issue the CREATE CONTROLFILE command from the trace file. After the command is successfully executed your instance will be brought into the mount state using the fresh created control file. Now, the next step is to use the RECOVER DATABASE command. Bellow is a sample output:

SQL> startup nomount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              75499212 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
CREATE CONTROLFILE REUSE DATABASE "DDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_1_2pdco23c_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_1_2pdco49x_.log'
  ) SIZE 50M,
  GROUP 2 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_2_2pdco6j0_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_2_2pdco8ns_.log'
  ) SIZE 50M,
  GROUP 3 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_3_2pdcoby5_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_3_2pdcof0o_.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_undotbs1_2rlg71l3_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_sysaux_2rlg714v_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_users_2rlg71kk_.dbf'
CHARACTER SET WE8ISO8859P1
 ;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> recover database using backup controlfile;
ORA-00279: change 793731 generated at 12/22/2006 12:00:32 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf
ORA-00280: change 793731 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794266 generated at 12/22/2006 12:09:00 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf
ORA-00280: change 794266 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794268 generated at 12/22/2006 12:09:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf
ORA-00280: change 794268 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf'

Uppsy! File 1 needs media recovery? Why?
Because, when I have been asked for the last log archive I didn't specify CANCEL. From the Oracle server point of view, the recover process was unsuccessful. So, the correct approach is:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

So, explicit CANCEL does matter in this case!