Tuesday, July 1, 2014

ORA-01194: file 1 needs more recovery to be consistent

Method 1:

1) Start the database in mount state

SQL> startup mount;

2) Recover the database.

SQL> recover database;

If you come across below error

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\SYSTEM01.DBF’

then, do the following

1)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc
ORA-00310: archived log contains sequence 10; sequence 14 required
ORA-00334: archived log:
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc'

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:
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

2)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc
ORA-00310: archived log contains sequence 11; sequence 14 required
ORA-00334: archived log:
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc'

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:
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

3)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875owh_.arc
ORA-00279: change 769526 generated at 03/16/2013 12:48:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_15_%u_.arc
ORA-00280: change 769526 for thread 1 is in sequence #15
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875
owh_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

3) Open the database in resetlog mode

SQL> alter database open resetlogs;

4) Check the status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
------------------------   -------------
DUPDB                  OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
----------    ------------------
DUPDB     READ WRITE


Method 2:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> CREATE UNDO TABLESPACE undo1 datafile '<ora_data_path>undo1_1.dbf' size 200m autoextend on maxsize unlimited;

Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;
System altered.

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shutdown immediate

SQL> startup

93 comments:

  1. Thanks for the post. Method 2 works for me.

    ReplyDelete
    Replies
    1. Thanks for your help. Method 2 work for me. after spending some time to resolve the issue on my own I didn't succeed so I had to go Google the ora number. Thanks.

      Delete
  2. thanks for your link..Method 2 works for me

    ReplyDelete
  3. Ok, me funcionó correctamente. Muchas gracias!!!!!

    ReplyDelete
  4. Hellp Pedro, Is this Spanish. I translated and now prperly understood

    Ok , it worked out properly. Thank you very much !!!!

    Thanks and Welcome

    ReplyDelete
  5. Thanks neeraj...second one worked for me

    ReplyDelete
  6. Option 2 worked for me on point!!!
    cheers Neeraj......

    ReplyDelete
  7. Great Method 2 worked for me.. Thanks to you

    ReplyDelete
  8. Hi Neeraj,

    Was just curious as to after changing the parameter why did we need to create a new tablespace undo1 and used it as undo tablespace ?

    ReplyDelete
  9. Hello Neeraj,

    I tried the second method and faced the following error:
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    ReplyDelete
    Replies
    1. try this: it may help you
      startup nomount;
      alter database mount;
      alter database open;

      Delete
    2. please help me..its not works and now my instance unable to connect

      SQL> startup mount
      ORACLE instance started.

      Database mounted.
      SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

      System altered.

      SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE

      System altered.

      SQL> shutdown immediate
      ORA-01109: database not open


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

      Database mounted.
      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-03113: end-of-file on communication channel

      SQL> startup nomount;
      ORA-24324: service handle not initialized
      ORA-01041: internal error. hostdef extension doesn 't exist
      SQL> alter database nomount;
      alter database nomount
      *
      ERROR at line 1:
      ORA-03114: not connected to ORACLE


      SQL> shutdown immediate;
      ORA-24324: service handle not initialized
      ORA-01041: internal error. hostdef extension doesn 't exist
      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-03114: not connected to ORACLE

      Delete
  10. Hi Neeraj,

    I tried the second option, but getting the following error when executing:
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01555: snapshot too old: rollback segment number 1 with name
    "_SYSSMU1_2129601957$" too small
    Process ID: 12028
    Session ID: 242 Serial number: 3

    Please let me know what shall I do,

    Thanks,

    ReplyDelete
    Replies
    1. Please check your undo retention policy.... some long running query is executing.
      use undo_management=auto

      Delete
  11. Oh Yeah... !!!
    OPTION 2 WORKED FOR ME
    YOU SAVE MY LIFE

    ReplyDelete
  12. Thank you so much !!! You are awesome !! Method 2 saved me !!

    ReplyDelete
  13. Option 2 worked for me
    Thanks a lot
    May Allah guide you to the truth

    ReplyDelete
  14. Thanks very very much....i used by method 2..thanks again..

    ReplyDelete
  15. I greatly thankful to you. Method 2 work for me. kindly suggest me how can i learn on ORACLE DBA

    ReplyDelete
  16. Gracias, me sirvió el método 2.

    Sería genial si explicas la función de cada sentencia ejecutada.
    La mayoría son intuitivas, pero en las últimas no le entendí.
    En todo caso uno lo puede investigar por aparte.

    Nuevamente muchas gracias.

    ReplyDelete
  17. method 2 worked as a charm! Thanks a lot!

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete

  19. Since last night , I stumbled on this difficulty. Method 2 is great. thank you very much

    ReplyDelete
  20. Method 2 is as awesome as it can be. Thanks man.

    ReplyDelete
  21. Thanks a lot.
    Just one solution in 2nd method on the internt

    ReplyDelete
  22. Method 2 worked for me....thanks alot

    ReplyDelete
  23. Thanks a Million!!! Method 2 Worked for me. First time didn't work but kept trying couple of times. Third time database got mounted and opened. I got all the data back thanks to your guidance!.

    ReplyDelete
  24. After Method 2 the database started, but in our application server logs we see following error:

    ORA-00600: internal error code, arguments: [2662], [106], [2606158984], [106], [2607365359], [41943227]

    What can we do?

    ReplyDelete
    Replies
    1. ORA-600 error occur because of various reasons.

      Pls check the trace file belong to ORA-600 and raise an SR with Oracle Support. you will get some idea

      Thank you
      Neeraj

      Delete
  25. Thanks for the blog. Method 2 worked for me

    ReplyDelete
  26. Very Nice neeraj ,it work for me also .......

    ReplyDelete
  27. Thanks Neeraj. Option2 worked for me

    ReplyDelete
  28. Thanks a lot for the blog. Method 2 worked for me

    ReplyDelete
  29. Thank you very very.
    much 2 worked for me

    ReplyDelete
  30. In method 2
    when i m using
    alter database open resetlogs;
    i got below error
    ERROR at line 1:
    ORA-01139: RESETLOGS option only valid after an incomplete database recovery

    and when
    alter database open;
    i got below error
    ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM_1.DBF'

    Please suggest something ASAP.

    ReplyDelete
    Replies
    1. Hi gourav,

      Pls apply the archive log to complete recovery process

      Thank you!
      Neeraj

      Delete
  31. Wonderful exercise, Solved our problem. Thanks so much Neeraj.

    ReplyDelete
  32. Very very thanks, METHOD-2 solved a big problem!
    GREAT JOB Neeraj!! :)

    ReplyDelete
  33. I am stuc this row
    SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
    SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;
    *
    ERROR at line 1:
    ORA-32001: write to SPFILE requested but no SPFILE specified at startup


    Please help :(

    ReplyDelete
    Replies
    1. Please start the database with spfile and execute these commands again

      Delete
  34. Ora 02180 error on creating tablespace usi g method 2

    ReplyDelete
  35. method 2 worked. thanks a lot.. but still i don't know what "exactly" i did :D

    ReplyDelete
  36. Hi,
    I tried the method 2 but this is the result:

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-00603: ORACLE server session terminated by fatal error
    ORA-00600: internal error code, arguments: [2662], [0], [181456303], [0],
    [181481997], [12583040], [], [], [], [], [], []
    ORA-00600: internal error code, arguments: [2662], [0], [181456302], [0],
    [181481997], [12583040], [], [], [], [], [], []
    ORA-01092: ORACLE instance terminated. Disconnection forced
    ORA-00600: internal error code, arguments: [2662], [0], [181456300], [0],
    [181481997], [12583040], [], [], [], [], [], []
    Process ID: 24838248
    Session ID: 60 Serial number: 3

    ReplyDelete
  37. Trying method 2 and I am seeing the following:

    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-00600: internal error code, arguments: [2662], [1], [1917537765], [1],
    [1917556237], [5165207], [], [], [], [], [], []
    Process ID: 10456
    Session ID: 295 Serial number: 29700


    ReplyDelete
  38. really had fun thanks. method 2 worked for me.but why did I have to create a undo tablespace?

    ReplyDelete
  39. This saved my ass.

    I didn't think I needed to re-create and assign new UNDO (since I had some already) but the database was crashing every time using the old UNDO that was there.

    Creating new undo tablespaces and assigning them to each instance solved the problem.

    ReplyDelete
  40. Thank you so much. you saved my lyf. you gave me the solution which worked in just 2 minutes. I was working on that problem for 4 hours(Method - 2).

    ReplyDelete
  41. Steps to Solve Oracle Database Recovery Problem because of Power Failure via DB Recovery Support
    It sounds extremely bizarre and odd on the off chance that somebody say their information is lost in light of energy disappointment. This appears to be shockingly in light of the fact that correct now you can't do anything, you don't have any alternative how to recuperate your lost information. On the off chance that you look through any help organization on Google you will discover numerous more organizations yet how you can believe them? Well! Try not to go anyplace, simply contact to Cognegic's DB Recovery Services or Exchange Database Recovery to get back your basic information. We give easy to use bolster and under your financial plan.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  42. please help me..its not works and now my instance unable to connect

    SQL> startup mount
    ORACLE instance started.

    Database mounted.
    SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

    System altered.

    SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE

    System altered.

    SQL> shutdown immediate
    ORA-01109: database not open


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

    Database mounted.
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    SQL> startup nomount;
    ORA-24324: service handle not initialized
    ORA-01041: internal error. hostdef extension doesn 't exist
    SQL> alter database nomount;
    alter database nomount
    *
    ERROR at line 1:
    ORA-03114: not connected to ORACLE


    SQL> shutdown immediate;
    ORA-24324: service handle not initialized
    ORA-01041: internal error. hostdef extension doesn 't exist
    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-03114: not connected to ORACLE

    ReplyDelete
  43. Its great support. Method 2 is work for me.

    ReplyDelete