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

Error:

ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/PROD/oradata/system01.dbf’

Solution:

If you try to give “alter database open resetlogs” command after cloning the database  using cold backup,you will get the above error,

To troubleshoot this, follow these steps


SQL>  recover database;
ORA-00283: recovery session cancelled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

So, you must recover using the backed up controlfile . Why ? Because the controlfile has information of archives that need to be applied during recovery.


SQL>  recover database until cancel using BACKUP CONTROLFILE;
ORA-00283: recovery session cancelled due to errors

Now shutdown the database

SQL>shut immediate

Go to $ORACLE_HOME/dbs and locate the pfile of respective database.


And  add below hidden parameter in the pfile,


 _allow_resetlogs_corruption=true




SQL> startup mount pfile= ‘$ORACLE_HOME/dbs/initPROD.ora’;

ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size                  2188408 bytes
Variable Size             436214664 bytes
Database Buffers          616562688 bytes
Redo Buffers               14286848 bytes
Database mounted.


SQL> select name,open_mode from v$database;


NAME      OPEN_MODE
——— ——————–
TEST      MOUNTED


SQL> alter database open resetlogs;


Database altered.


SQL> select name,open_mode from v$database;


NAME      OPEN_MODE
——— ——————–
TEST      READ WRITE

  • June 19, 2017 | 19 views
  • Comments