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

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

ORA-01110: data file 1: '/u01/dump01/files/data/system01'

If you try to give “alter database open resetlogs” command after cloning the database  using cold backup you probably see this 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.

Lets do this

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

Viola…!!

Now shutdown the database

SQL>shut immediate

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

And  add below parameter in the pfile

 _allow_resetlogs_corruption=true

SQL> startup mount pfile= ‘$ORACLE_HOME/dbs/initDBNAME.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

Done . . !!