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

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: ‘/u01/app/oracle/oradata/prod/system01.dbf'

Suppose we are facing this issue while opening the database with resetlogs option.

We have to check how many files have been recovered. We can check with the following command:-

select min(change#),max(change#) from v$recover_file;

MIN(CHANGE#)      MAX(CHANGE#)
————              ————
7533853         7533853

If the files are recovered fully then. We have to go to pfile and add a new line,

_allow_resetlogs_corruption=true

After editing the pfile and saving it we have to restart the DB with the pfile.

Shutdown Database

SQL> shutdown immediate 
Database dismounted.
ORACLE instance shut down.

Startup DB in mount mode

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.

Open the DB with Reset logs option

SQL>alter database open resetlogs;
Database altered.