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.


7 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.

  4. raj says:

    Hi Buddy

    I was trying to copy data files from a live instance and & to created database by creating a new control file manually.

    Whole my last three attempts I had to give up as I was always stuck by “ORA-01194: file 1 needs more recovery to be consistent”. Well not anymore, I know I cannot do this with a production instance, yet, managing to bring up an instance using such a hack gives an awesome feeling. Thank you

  5. sapbasisinfo says:

    Hi Raj ,
    On prod system, take the database backup mode via sql command “ALTER DATABASE BEGIN BACKUP;”
    Copy all the data files from PROD to TEST , changed PROD database mode to end backup via “ALTER DATABASE END BACKUP;” , if you are using the same SID then just recover the database with following commands ;

    SQL>startup nomount
    SQL>alter database mount;
    SQL>recover database using backup controlfile until cancel;
    (Provide archive files)

    Media recovery cancelled.
    SQL>alter database open resetlogs;

    If you are going to change your SID , then create a trace file from controlfile on PROD via following SQL command ;

    SQL>alter database backup controlfile to trace;

    Edit the controlfile trace and change SID (IF needed file locations) then ;
    SQL> startup nomount
    execute the tracefile as script like below ;
    Control file created
    SQL> alter database open;

Leave a Reply

8 + = twelve