Practical Apps DBA

Oracle Apps DBA’s Blog

How to recover and open the database if the archive log required for recovery is missing.

Posted by kalpit on April 1, 2008

Last week, I had one interesting recovery scenario at client side; we had to recover one of our development databases from old backup.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.

It was critical for us to recover database because of some project deadline.

Error:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.

Alert log shows below error

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340

To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.

About these ads

42 Responses to “How to recover and open the database if the archive log required for recovery is missing.”

  1. tom said

    Hi,
    Can you tell me how you managed to open the db after step 5?

    Cheers

    Tom

  2. jv Lubiez said

    Hi!

    The database has always the RBS SYSTEM. It’s enough to startup the database in manual mode.
    Then you can create a new Undo Tablespace, switch to Automatic undo management and drop the old undo tablespace.
    I had to do that after corruption in Rollback Segments.

    Best regards,
    Jean-Valentin LUBIEZ

  3. Thanks Jean-Valentin for answering !!

    regards
    Nav

  4. K.R.RAJU said

    It’s really good one.

    Thanks to share this info

    Best Regards.

    -Raju

  5. raheem said

    Its an good post surely an good experience shared with all of us

  6. sc said

    bhai tu great hai…..
    thanks a lot…

  7. Bhavin Patel said

    Navdeep, We had been in this situation and had tried this workaround. Amit Nigam(if you remember) was also with me during this task. Thanks for refreshing this senario again. I could document this now :)

    -Bhavin

  8. krishna said

    Hi,

    How would you found the “undo tablespace corruption” error?

    Is ORA-00600 related to undo tablespace issue?

    -
    krishna

  9. -chase said

    Great article, Thanks for posting!

  10. kirakast said

    Thanks
    We had been in this situation and just by following your documentation we recovered the db.
    And i documented this article for future refrences.And my suggestion will be same to document it.

    Regards
    Kirakast

  11. shiti said

    Hi,

    you have not posted anything after Apr08.

    Will you respond to my question on this site??

    if not can i get your Email-id to clear my doubts

    regards
    shiti

  12. Salah j. Mustafa said

    Thank very much.

  13. Dominica said

    Thanks for this posting. Good one.
    Yeah, sometimes, we really need to open the database inconsistent to look at “one table”‘s data. Occassionaly, I need to find out something from backup(like 2 weeks ago’s backup).

  14. google said

    Hey there this is google here.. lvoe the site by the way.. keep up the good work

  15. Mohanraj said

    Hi Navdeep,
    Thanks a lot for sharing the knowledge.
    It worked like a charm.

  16. shubhodaya said

    Hi Navdeep,

    I could not find _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
    Will you elaborate on this please .

    REgards

    Shubhod Salian

  17. Shrikant said

    Hi Navdeep,
    Thanks a lot for sharing the knowledge.
    I have One Question After Open th database did You found any data Loss??

  18. Huey McPherson said

    Just wanted to add another big thanks here. This article helped me bring my dev database back to life which saved probably a week of rebuilding it. THANKS!

  19. Tibui Gerard said

    Hello Kalpit, you are a life-saver, man. i had done everything possible to bring my database online. This worked just fine for me. Must i create a new undo tablespace and rebuild the database even if it doesn’t crash like yours did? Once again thanks a lot. wish i could repay you somehow for this valuable tip. i had done everthing – restored datafiles, applied logs, recovered in every possible way but my system01.dbf file couldn’t be consistent. thanks

  20. Selling High Priced Services said

    Thanks for a awesome post and interesting comments. I found this post while looking for some lyric updates. Thanks for sharing this article.

  21. Thankyou, very helpful in trying to recover my database.

  22. i also trying in this procedure but when i applied
    #alter database open
    its altered
    but instance going down thats why i cannt recreate undo tablespace even reset undo_management to “manual”
    please help me

    kamal uddin

  23. sumanth said

    Its great job……

  24. nobody said

    Why i found another site very similar content with this one ?
    who’s faking experience here ?

  25. [...] How to recover and open the database if the archive log required for recovery is missing. [...]

  26. venkat said

    Perfect solution .. & it works…

    –Venkat

  27. bespoke access database design…

    [...]How to recover and open the database if the archive log required for recovery is missing. « Practical Apps DBA[...]…

  28. saleh abdoulaye said

    hi! Everyone. Plse I need yor help . My database cannot be mounted and opened. Error: control file is not identified. Plse can someone help me to resolve this problem.thanks.

  29. Adam Gorge said

    I have also faced same situation when i was trying to repair corrupt Oracle Database. I was able to re-create controlfile. During recovery process, it asked for Archive logs. I followed all the steps mentioned in your blog post. But still no luck. In last, I tried Stellar Phoenix Oracle database recovery software. The software helped me to repair corrupt Oracle database. Thanks Stellar!!

  30. fabio said

    thanks for sharing this useful artcle

  31. I do not know whether it’s just me or if perhaps everyone else encountering problems with your site. It appears as though some of the written text in your posts are running off the screen. Can someone else please provide feedback and let me know if this is happening to them as well? This might be a problem with my browser because I’ve had this
    happen before. Cheers

  32. sumit Mehta said

    Hello,

    I am new to oracle
    I tried to follow the above given steps but again its the same error.

    Plz suggest from starting (if possible provide the screen shot for the same.)

    Thanks
    Sumit Mehta

  33. sumit Mehta said

    Hello Friends,

    getting the same error after doing the abbove steps

    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: ‘E:\ORACLE\ABC\SAPDATA1\SYSTEM_1\SYSTEM.DATA1′

    Plz suggest ASAP

    Thanks
    Sumit Mehta

  34. “How to recover and open the database if the archive log required for recovery is missing.

    Practical Apps DBA” honestly makes myself imagine
    a small bit more. I admired each and every particular part
    of this post. Thanks for your time -Raymond

  35. IKBN SABAH said

    Everyone loves it when people come together and share ideas.
    Great blog, continue the good work!

  36. Saroj Mahanta said

    Awesome. Long live Navdeep… I faced the similar situation in one of our client office in Israel. And i was able to recover the database, following your tips. God bless you for sharing your knowledge. World is really a beautiful place to live because of such nice people.

    Thanks Again,
    Saroj Mahanta
    saroj.mahanta@gmail.com

  37. Amit said

    Awesome sir ji :)

  38. Muhammad Irfan said

    Hi,

    I am getting the following error when trying open database with resetlogs. I have tried both auto and manual setting for undo management:

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced
    ORA-00704: bootstrap process failure
    ORA-00704: bootstrap process failure
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01555: snapshot too old: rollback segment number 427 with name
    “_SYSSMU427_3746975833$” too small
    Process ID: 8442
    Session ID: 860 Serial number: 3

  39. avisek karmakar said

    I have one senario…my dev database crahed on 10 AM in the morning..I successfully restore n recover database from latest backup..but unfortunatly I dint take backup after restoring…

    the database again crash on 2 PM on the same day…

    then able to restore db until 10 AM, but how will I apply logs which are generated between 10 AM to 2 PM..

    please suggest me…

  40. Naseer Arif said

    Thanks, it helps me :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: