Skip to Content

<p>Can there be a more inconvenient time to perform database recovery than the holiday season?<br />I guess not, but there are times when Murhphy’s law is again proven true thanks to some…uhm…shortcomings regarding the usability of the Oracle DB software.<br />This scenario is based on one of the cases I encountered during my support duties just recently:</p>h4. Starting point

<p>A more or less common problem, the DB has (for whatever reason) crashed during an online backup. You will then face the problem that is described in note #4162:</p><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>SQL> startup

ORACLE instance started.

Total System Global Area  494927872 bytes

Fixed Size                  1268268 bytes

Variable Size             201328084 bytes

Database Buffers          289406976 bytes

Redo Buffers                2924544 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/oracle/102_32/sapdata1/system_1/system.data1’

</pre><p>Why does the file needs media recovery anyway? The DB has just crashed, so crash recovery consisting of instance recovery and subsequent transaction recovery should be enough, right? Well, while the database was in backup mode, the checkpoint SCN in the file header is frozen and during startup the DB has to come to the conclusion that the file is ‘old’ and has to be brought to the current state via recovery.</p>h4. The drama unfolds

<p>Unfortunatly, Oracle does not exactly tell us this important piece of information. And esspecially if you do not know about the circumstances (the running backup) and the exact meaning of the recovery commands, the following can happen:</p><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>SQL> recover database using backup controlfile;

ORA-00279: change 21977107 generated at 12/31/2009 20:30:18 needed for thread 1

ORA-00289: suggestion : /oracle/102_32/oraarch/OT1arch1_19_706943979.dbf

ORA-00280: change 21977107 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log ‘/oracle/102_32/oraarch/OT1arch1_19_706943979.dbf’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

</pre><p>Using the addition ‘using backup controlfile’ has a dramatic consequence, it actually changes the controlfile header:</p><p>Before:</p><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>File Number=0, Blksiz=16384, File Type=1 CONTROL

</pre><p>After:</p><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL

</pre><p>This means we now have a backup controlfile and this consequently means we can only open the database with RESETLOGS. <br />When we now recognize our mistake, it is too late:</p><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>SQL> alter database end backup;

Database altered.

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 resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/oracle/102_32/sapdata1/system_1/system.data1’

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

</pre><p>And now we are in a vicious circle:</p><ul><li><div>we can not open the database with NORESETLOGS because we have a backup controlfile</div></li><li><div>we can not open the database with RESETLOGS because the DB has crashed and we need the online redo logs for instance recovery</div></li></ul>h4. The happy ending

<p>We have to get rid of the backup controlfile by recreating the controlfile. <br />This is pretty simple:</p><ul><li>alter database backup controlfile to trace;</li><li>execute the path in the created tracefile that contains<br />      CREATE CONTROLFILE REUSE DATABASE…NORESETLOGS</li></ul>h4. Once again the golden rules

<ul><li>Whenever you are not exactly sure how to proceed, use brtools. They should tell right from the start about the backup mode.</li><li>Use manual recovery commands with utmost caution</li></ul>

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Harshavardhan Ganjigatti
    Hi Micheal,

    I am unable to create the control file. I am also getting the exact error what you faced. Can you please tell me what exactly you did to resolve this.

    Thanks & regards,
    Harsha

    (0) 

Leave a Reply