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
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
fabio said
very useful
thanks