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;

————                  ————
751133853               751133853



Add following line to INIT.ORA file


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


    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

2 + = eleven