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

Error log:

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/RTS_NEW/system_new.dbf'

Workaround for this error is to provide all the available archive log files to the recovery:

SQL> recover database using backup controlfile until cancel;
...
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs;
Database altered.

Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

SQL> CREATE UNDO TABLESPACE undo2 datafile '/u01/app/oracle/oradata/RTS_NEW/undo2_df1.dbf' size 200m autoextend on maxsize 30G;
Tablespace created.
SQL> alter system set undo_tablespace = undo2 scope=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.

Now bounce your database.

SQL> shutdown immediate
SQL> startup

Cheers!! Database started successfully.

Staty Tune. 🙂

13 thoughts on “ORA-01194: file 1 needs more recovery to be consistent”

  1. after changing undo tablespace to undo 2 why you fire the command to undo 1

    SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;

    • Thank you Aslam for writing!
      Its typo mistake, good observation, correction done.
      SQL> alter system set undo_tablespace = undo2 scope=spfile;

  2. Thank you Sir, it worked !!!.

    This was exactly the fix I needed for exactly the same problem, reporting ORA-01194: and ORA-01110.

    Although I used to be a very good DBA some time ago, I doubt I could have found the line
    ALTER SYSTEM SET “_allow_resetlogs_corruption”= TRUE SCOPE = SPFILE;
    by myself or at least not that quickly.

    If you have time could you please explain what that command does, as it seems counterintuitive to “”_allow_resetlogs_corruption” – not that I am complaining 🙂 .

  3. Muchas gracias por escribir este artículo. Resolvió mi problema en Oracle 18c XE…

  4. Absolute Great work – many many thanks, I could save my local db terminated on linux vbox while windows host was in the blue screen panic!

  5. Thank you. You saved me !!! The command – recover database using backup controlfile until cancel; fixed my RECOVER Database problem.

Leave a Reply