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-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.