Pavan DBA's Blog

The DBA Knowledge Store

«
»

How to resolve ORA-01194-file 1 needs more recovery to be consistent

Posted by Pavan DBA on November 9, 2009


Many a times, while performing recovery of controlfile or redolog files, after opening the database with RESETLOGS option, you may land up in getting ORA-01194 file 1 needs more recovery to be consistent error.

In such situations, issue
sql> recover datafile 1;
while recovering it will ask for archive log which sometimes doesn’t exist. Then provide the redolog file path and if the data still exists in redolog file, oracle will recover the database and resetlogs option will work fine at that moment

This entry was posted on November 9, 2009 at 7:28 PM and is filed under Bkp n Recovery. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “How to resolve ORA-01194-file 1 needs more recovery to be consistent”

  1. a4akanksha said

    November 9, 2012 at 3:18 PM

    Hi Pavan,
    I am still facing the problem. Here is the what i faced:

    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 575931 generated at 11/08/2012 14:09:54 needed for thread 1
    ORA-00289: suggestion :
    D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\DBTEST\ARC00004_0798747889.001
    ORA-00280: change 575931 for thread 1 is in sequence #4

    Specify log: {=suggested | filename | AUTO | CANCEL}
    D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO01.LOG
    ORA-00308: cannot open archived log
    ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO01.LOG’
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.

    Specify log: {=suggested | filename | AUTO | CANCEL}
    D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO02.LOG
    ORA-00308: cannot open archived log
    ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO02.LOG’
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.

    Specify log: {=suggested | filename | AUTO | CANCEL}
    D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO03.LOG
    ORA-00308: cannot open archived log
    ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO03.LOG’
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.

    Specify log: {=suggested | filename | AUTO | CANCEL}
    cancel
    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’

    ORA-01112: media recovery not started

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    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’

    Reply

    • Pavan DBA said

      November 14, 2012 at 10:42 PM

      Hi,

      you are getting a different error.

      ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO03.LOG’
      ORA-27041: unable to open file
      OSD-04002: unable to open file
      O/S-Error: (OS 2) The system cannot find the file specified.

      this means redolog files are not there at OS level. just check the exact location of the files and provide that location path there. then it will work

      Reply

  2. fabio said

    July 9, 2012 at 3:11 PM

    very useful
    thanks

    Reply

Leave a Reply

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

Gravatar
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 )

Cancel

Connecting to %s

«
»
 
%d bloggers like this: