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

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

  1. a4akanksha said

    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’

    • Pavan DBA said

      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

  2. fabio said

    very useful
    thanks

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

 
%d bloggers like this: