RECOVER DATABASE starts with an update

There have been a few questions on my blog about the RECOVER DATABASE command :
Suppose I dont need incomplete recovery but I issued "recover database using backup controlfile"
I notice that I cannot issue normal recover command anymore.
Does this statement modifies something?

UPDATE : I've realised that there were some incorrect references to an "incomplete recovery" in the first version of this blog post. I have marked UPDATE wherever I changed this post.

UPDATE : This is a test that demonstrates that "something is modified".

Note, however, that once I issue RECOVER DATABASE USING BACKUP CONTROLFILE, Oracle knows that it will have to re-synchronise the controlfile -- which is only done by a RESETLOGS.

Here is a very simple test :

I STARTUP MOUNT and check the timestamps of datafiles and controlfiles :

ora10204>date
Sat May 29 19:49:34 SGT 2010
ora10204>ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 29 19:49:44 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> !date
Sat May 29 19:50:03 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control01.ctl

SQL> 

I find that the MOUNT updates the controlfile but not the datafiles.

I then issue a RECOVER DATABASE USING BACKUP CONTROLFILE :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


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

From another terminal, I list the datafiles and controlfiles :

[root@linux64 ORT24FS]# date
Sat May 29 19:51:01 SGT 2010
[root@linux64 ORT24FS]# pwd
/oracle_fs/Databases/ORT24FS
[root@linux64 ORT24FS]# ls -ltr
total 3069876
-rw-r----- 1 ora10204 dba  131080192 May 29 19:25 temp01.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:33 redo01.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:34 redo02.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:40 redo03.dbf
-rw-r----- 1 ora10204 dba 1340874752 May 29 19:50 users01.dbf
-rw-r----- 1 ora10204 dba  104865792 May 29 19:50 undotbs.dbf
-rw-r----- 1 ora10204 dba  985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba  304095232 May 29 19:50 sysaux01.dbf
-rw-r----- 1 ora10204 dba  104865792 May 29 19:50 example01.dbf
-rw-r----- 1 ora10204 dba    7389184 May 29 19:51 control02.ctl
-rw-r----- 1 ora10204 dba    7389184 May 29 19:51 control01.ctl
[root@linux64 ORT24FS]# 

Aah ! The RECOVER DATABASE command has updated all the datafiles. (the controlfile is always updated by a heartbeat every 3 seconds -- so it's timestamp may continue to be updated even if I don't issue any further commands -- but that is a different matter).

Returning to the RECOVER DATABASE, I CANCEL it and check timestamps :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> !date
Sat May 29 19:52:16 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:50 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control01.ctl

SQL> 

So, the datafiles are not updated now.

Of course, I cannot OPEN the database :

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> 

UPDATE : "an incomplete recovery" corrected to "use an older controlfile" in the line below.
So, remember : A USING BACKUP CONTROLFILE, once initiated, is an attempt to use an older controlfile and must always be succeeded by an OPEN RESETLOGS.



As a continuation of the exercise, I complete the RECOVERy, applying what was my CURRENT online RedoLog file (redo03.dbf) :

SQL> shutdown;       
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


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: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
alter database openMedia recovery cancelled.
SQL>  resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/Databases/ORT24FS/redo03.dbf
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> shutdown;    
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs; 

UPDATE : This paragraph rewritten :
What is normally an INCOMPLETE RECOVERY is done as COMPLETE RECOVERY because I have applied the last CURRENT Online Redo Log as well. The RESETLOGS is required because I initiated a "USING BACKUP CONTROLFILE".


.
.
.