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

I got ” ORA-01194: file 1 needs more recovery to be consistent ”  error in Oracle database.

 

You can read the following post to learn more details about Oracle database corruptions.

 

ORA-01578: ORACLE data block corrupted (file # string, block # string) and How to Solve Block Corruption Error

 

 

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

When I startup database , I got the following error.

SQL> startup
ORA-03113: end-of-file on communication channel

 

Instance can be started in mount mode, but database cannot open.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

 

 

When I run the recover database command, I got the following error.

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL
*
ERROR at line 1:
ORA-00279: change 8411468 generated at 04/25/2020 22:00:18 needed for thread 2
ORA-00289: suggestion : +RECO
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'MSDB'
ORA-00280: change 8411468 for thread 2 is in sequence #207

 

I have started to incomplete recovery, but I got the following errors.

 

SQL> ALTER DATABASE RECOVER CANCEL;
ALTER DATABASE RECOVER CANCEL
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/MSDB/datafile/system.256.871197447'

 

 

In this cases, Oracle offers a hidden parameter _ALLOW_RESETLOGS_CORRUPTION which will allow us to open database.

 

Firstly set undo_management parameter to manual as follows.

SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> alter system set undo_management=manual scope=spfile;
System altered.

 

Then set _allow_resetlogs_corruption parameter to TRUE as follows.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
System altered.

 

Shutdown instance.

SQL> shutdown immediate;

 

 

Startup instance in mount mode.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

 

Open database in resetlogs or noresetlogs mode.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

 

 

Do you want to learn more details about RMAN, then read the following articles.

https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/