Here’s the test, which I did on one of our lab systems…

Setup

nap01:~/oradata/jt10g$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 9 12:55:07 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> col member format a40
SQL> select l.status, member
  2  from v$logfile inner join v$log l using (group#);

STATUS           MEMBER
---------------- ----------------------------------------
INACTIVE         /u04/oracle/oradata/jt10g/redo03.log
CURRENT          /u04/oracle/oradata/jt10g/redo02.log
INACTIVE         /u04/oracle/oradata/jt10g/redo01.log

SQL> alter database backup controlfile
  2  to '/u04/oracle/oradata/jt10g/backup.ctl';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

1. We have a valid backup of the controlfile.

2. I issued a logfile switch since Joel Patterson specifically asked about it in the email discussion.

3. The database had a clean, normal shutdown.

Now let’s simulate the loss of controlfiles only and walk through the recovery:

nap01:~/oradata/jt10g$ rm control*.ctl
nap01:~/oradata/jt10g$ sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1980712 bytes
Variable Size             180356824 bytes
Database Buffers          440401920 bytes
Redo Buffers                6406144 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> shutdown abort;
ORACLE instance shut down.

Recovery

At this point we have lost all of our control files. Rather than just do the RESETLOGS right away, first we’ll try one or two other ideas.

First let’s just restore the backup controlfile and try to open the database with no recovery at all.

nap01:~/oradata/jt10g$ cp backup.ctl control01.ctl
nap01:~/oradata/jt10g$ cp backup.ctl control02.ctl
nap01:~/oradata/jt10g$ cp backup.ctl control03.ctl
nap01:~/oradata/jt10g$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 9 13:22:24 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1980712 bytes
Variable Size             180356824 bytes
Database Buffers          440401920 bytes
Redo Buffers                6406144 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>

Interesting. We know that the database itself doesn’t need recovery; in fact we just have a controlfile that’s less than five minutes out of date. So let’s just try opening NORESETLOGS.

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Oracle requires us to run the recovery command even though we had a clean shutdown. Huh? There are no archived logs to apply! Well I guess we can start by doing a CANCEL-based recovery and just canceling right away (since there are no archived logs to apply).

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 775140 generated at 05/09/2007 07:00:38 needed for thread 1
ORA-00289: suggestion :
/u04/oracle/product/10.2.0/db_1/dbs/arch1_5_621627183.dbf
ORA-00280: change 775140 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u04/oracle/oradata/jt10g/system01.dbf'

ORA-01112: media recovery not started

Well it claims that we can’t open the database but let’s just give it a try.

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> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u04/oracle/oradata/jt10g/system01.dbf'

There you are. Looks like Oracle requires a RESETLOGS once you’ve even started that recovery process with the backup control file. This appears to be a rare case where you can do a complete recovery but Oracle still requires a RESETLOGS.

But even the RESETLOGS won’t work yet – we need to do some recovery first.

Things To Look Out For and Secret DBA Tricks

Well let’s finish up. First off, Oracle actually needs to look at the ONLINE logs. By the way, there’s one thing to be careful of here… check it out:

SQL> col member format a40
SQL> select l.status, member
  2  from v$logfile inner join v$log l using (group#);

STATUS           MEMBER
---------------- ----------------------------------------
INACTIVE         /u04/oracle/oradata/jt10g/redo01.log
CURRENT          /u04/oracle/oradata/jt10g/redo02.log
INACTIVE         /u04/oracle/oradata/jt10g/redo03.log

That information is actually WRONG. Why? Because we did a log switch right before we shutdown. This view is showing you the BACKUP controlfile. So the file redo02.log was CURRENT when we took the backup. However the file redo03.log is actually the one we want. This view will also have old values for log sequence numbers… so just be careful.

Last Monday (when we were originally doing this at our “tech night”) another guy at IT Convergence showed us another great Secret DBA Trick™ to confirm what file you need. Look above and note that Oracle will tell you exactly what sequence number it’s looking for. You can check the sequence number for any logfile on unix like this:

nap01:~/oradata/jt10g$ strings redo01.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000004, SCN 0x0000000b05b5-0x0000000bd34f
nap01:~/oradata/jt10g$ strings redo02.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000005, SCN 0x0000000bd34f-0x0000000bf612
nap01:~/oradata/jt10g$ strings redo03.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000006, SCN 0x0000000bf612-0xffffffffffff

So you can see that Oracle is required to do manual recovery using an online log. Usually Oracle does this automatically – but in this specific scenario you have to do it manually!

In the spirit of experimentation we’ll first apply just log sequence 5 and try to open. If that doesn’t work then we’ll try log sequence 6. We will also still try to avoid the RESETLOGS if possible.

nap01:~/oradata/jt10g$ sqlplus / as sysdba

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 775140 generated at 05/09/2007 07:00:38 needed for thread 1
ORA-00289: suggestion :
/u04/oracle/product/10.2.0/db_1/dbs/arch1_5_621627183.dbf
ORA-00280: change 775140 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u04/oracle/oradata/jt10g/redo02.log
ORA-00279: change 783890 generated at 05/09/2007 13:11:59 needed for thread 1
ORA-00289: suggestion :
/u04/oracle/product/10.2.0/db_1/dbs/arch1_6_621627183.dbf
ORA-00280: change 783890 for thread 1 is in sequence #6
ORA-00278: log file '/u04/oracle/oradata/jt10g/redo02.log' no longer needed for
this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u04/oracle/oradata/jt10g/system01.dbf'

ORA-01112: media recovery not started

Looks like we still need to apply the last log.

SQL> recover database using backup controlfile;
ORA-00279: change 783890 generated at 05/09/2007 13:11:59 needed for thread 1
ORA-00289: suggestion :
/u04/oracle/product/10.2.0/db_1/dbs/arch1_6_621627183.dbf
ORA-00280: change 783890 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u04/oracle/oradata/jt10g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

Well there you have it. RESETLOGS is required.

SQL> alter database open resetlogs;

Database altered.

And after all of that work… we’re back online! New incarnation, but no data loss. And a neat new trick for checking logfile sequence numbers.