ORA-01194: file 1 needs more recovery to be consistent
1 min readSQL> 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.
