>
Oracle, Technical

Controlfile Recovery Requires RESETLOGS

In response to a small discussion on the oracle-l mailing list last week I thought I’d put together a quick demo of exactly what I was referring to in my email. Basically I was discussing how even when you do a normal shutdown – leaving your database in a consistent state that doesn’t require recovery – if you lose your controlfiles then you still will need to execute the “recovery” commands and then open resetlogs.

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: {=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: {=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: {=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: {=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.

About these ads

About Jeremy Schneider

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider

Discussion

24 thoughts on “Controlfile Recovery Requires RESETLOGS

  1. See my email reply to ORACLE-L on the recovery
    methods if you have done a Shutdown Immediate and still have the Online Redo Logs.
    You do not need RECOVER and RESETLOGS if you
    CREATE CONTROLFILE.
    Hemant

    Posted by Hemant K Chitale | May 10, 2007, 8:46 am
  2. That’s a good point; I didn’t mention that recreating the controlfile does not require RESETLOGS. I just now put together another post with an example – thanks for pointing this out!

    Posted by Jeremy | May 15, 2007, 12:24 pm
  3. thanks man, you saved my butt!

    Posted by Snowy | November 13, 2007, 1:24 pm
  4. Thank you very much for sharing this!

    Posted by Mariano | December 6, 2007, 6:16 am
  5. Many Thanks,

    You are a star !!!!

    Regards

    BoBa

    Posted by Slobodan Vujinovic | December 12, 2007, 8:23 am
  6. Hello,
    Thank you very much for your article.
    Very well written! Thanks for the secret tips, that’s what save my day.
    Regards,
    Nur

    Posted by Nur Lakhani | January 18, 2008, 2:25 am
  7. thanks alot
    i very like your article u write simple
    u solved my problems

    thanks& regards
    Madhvesh

    Posted by madhvesh | February 5, 2008, 1:35 am
  8. thanks for the great article. my question, can we activate standby database without applying the last log?

    SVRMGR> recover standby database;
    ORA-00279: change 296695408 generated at 03/28/2008 22:39:20 needed for thread 1
    ORA-00289: suggestion : /export/oradata/MIBS/archive/archlog73920.ARC
    ORA-00280: change 296695408 for thread 1 is in sequence #73920
    Specify log: {=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log ‘/export/oradata/MIBS/archive/archlog73920.ARC’
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    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: ‘/export/oradata/MIBS/datafile/system01′
    SVRMGR> alter database activate standby database;
    alter database activate standby database
    *
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: ‘/export/oradata/MIBS/datafile/system01′

    I need to activate my standby db and should not apply the last log because the last log will truncate all tables in it. pls help, since it is urgently to recover the db.
    thanks.

    Posted by nordin | March 31, 2008, 10:54 pm
  9. THANK U VERY MUCH FOR SUCH GUIDE LINE IN RESPECT OF ALL RELATED ANSWER

    Posted by pradnyakar | October 17, 2008, 1:46 am
  10. Simply Excellent.. genius…… Thanks for the article.

    Regards

    Posted by Ashish Kohli | December 9, 2008, 1:03 am
  11. That is absolutely genius work…………

    Posted by sameer | January 20, 2009, 1:35 am
  12. really exellent.
    this error i get so many times.but i dont no how to recover and open with resetlog option.
    Regards
    ravireddy

    Posted by ravinder reddy | March 27, 2009, 7:24 am
  13. Thanks for this excellent and helpful Article to solve this kind of problems

    Posted by chakravarthy | April 25, 2009, 1:38 am
  14. Thank You for the trick. I was pulling my hair to resolve this while doing hot backup and restore test. I found this technique of applying redo logs and resolved the issue finally.
    Thanks again.

    Posted by Ramesh V | October 14, 2009, 1:42 pm
  15. Thank you!! You saved me with this. I had been working with oracle for 5 hours trying to get the application to start. I followed your steps and resolved the problem.

    Posted by Michael B | February 9, 2010, 1:03 pm
  16. I sugestion that works.
    After you startup mount with your backup controlfile, do the following

    1) alter database backup controlfile to trace;
    2) go to the generated trace and edit it, leaving the comand
    “create controlfile NORESETLOGS”.. until its ends and save it in another file

    sample here “x.sql”
    ——————–

    CREATE CONTROLFILE REUSE DATABASE “TESTE10″ NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 (
    ‘+DB_RAID5/teste10/onlinelog/group_1.829.721993913′,
    ‘+DB_RAID5/teste10/onlinelog/group_1.830.721993915′
    ) SIZE 100M,
    GROUP 2 (
    ‘+DB_RAID5/teste10/onlinelog/group_2.831.721993917′,
    ‘+DB_RAID5/teste10/onlinelog/group_2.832.721993919′
    ) SIZE 100M
    — STANDBY LOGFILE
    DATAFILE
    ‘+DB_RAID5/teste10/datafile/system.833.721993921′,
    ‘+DB_RAID5/teste10/datafile/sys_undots.834.721993925′,
    ‘+DB_RAID5/teste10/datafile/sysaux.835.721993925′
    CHARACTER SET WE8ISO8859P1;

    3) execute x.sql
    4) recover database
    5) alter database open

    And it is done.. works..

    Posted by Marcus Minervino | June 18, 2010, 9:13 am
  17. Wonderful information provided about recovery of control file with and without resetlogs. Thanks buddy keep it up.

    Posted by Jack Nickolson | June 20, 2010, 11:06 pm
  18. It worked nicely.. Great info..

    Posted by Vijayan | October 26, 2010, 11:46 am
  19. Thanks a lot.

    Excellent article about recovery of control file with and without resetlogs.

    I tried this solution and it worked successfully.

    Great !!! -:)

    Posted by Suresh Borse | November 24, 2010, 5:23 am
  20. Thank you, you saved my day!!!

    Posted by Roberto | June 7, 2011, 3:00 am
  21. Excellent .

    Thanks-JP

    Posted by Jyotirmay Pathak | June 30, 2011, 6:26 am

Trackbacks/Pingbacks

  1. Pingback: Controlfile Recovery WITHOUT Resetlogs : Ardent Performance Computing - May 15, 2007

  2. Pingback: Problem: file is more recent than controlfile - old controlfile « Khayer’s Weblog - April 15, 2009

  3. Pingback: 恢复控制文件 « Eversmily's Blog - March 7, 2011

Disclaimer

(a) The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 896 other followers

%d bloggers like this: