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

If you got an error “ORA-01194: file 1 needs more recovery to be consistent” after the SQL command “alter database open resetlogs” you should check the related solution before trying anything else…

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

SQL> recover database

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> desc v$recover_file

 Name                                                  Null?  Type
 —————————————– ——– —————————-
 FILE#                                                              NUMBER
 ONLINE                                                         VARCHAR2(7)
 ONLINE_STATUS                                     VARCHAR2(7)
 ERROR                                                          VARCHAR2(18)
 CHANGE#                                                    NUMBER
 TIME                                                              DATE

 

SQL> select min(change#),max(change#) from v$recover_file;

MIN(CHANGE#) MAX(CHANGE#)
————                  ————
751133853               751133853

 

SOLUTION ;

Add following line to INIT.ORA file

_allow_resetlogs_corruption=true

SQL> shutdown immediate

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

Total System Global Area 1.6168E+10 bytes
Fixed Size                  2271072 bytes
Variable Size            2181040288 bytes
Database Buffers         1.3959E+10 bytes
Redo Buffers               26329088 bytes
Database mounted.

SQL>alter database open resetlogs;
Database altered.

 

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

  1. Muhammad Kamran says:

    Thanks a lot for wonder full solution
    I have done but with “alter database open resetlogs upgrade;”
    my database has been opened but in restricted mode and I wont allow me to open database normal
    what should I do
    Please help me

  2. sapbasisinfo says:

    Hello Muhammad ,

    If your current database version is newer than the backup datafiles then upgrade
    steps must be taken ;

    Run the upgrade script
    SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

    Close the current sqlplus connection and open new one.

    Try to shutdown and open DB

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP

    If system can do this and situation fits your structure then execute the post upgrade steps.

    SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

  3. Shashank Kaneria says:

    Thank you very much. I have only added one more command inbetween as I am using SPfile.
    I have created a SPfile from Pfile after adding the line _allow_resetlogs_corruption=true.

    My database altered to OPEN. Thank you very much.

Leave a Reply


7 × = sixty three

Blogroll