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.

 

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

    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.

  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)

    SQL>Cancel
    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 ;
    CREATE CONTROLFILE REUSE SET DATABASE “
    RESETLOGS NOARCHIVELOG
    .
    .
    .
    Control file created
    SQL> alter database open;

Leave a Reply to sapbasisinfo


3 + one =

Blogroll