
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. 🙂
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;
Thank you! it works!
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 🙂 .
Thanks. It works; database and data file recovered successfully.
Muchas gracias por escribir este artículo. Resolvió mi problema en Oracle 18c XE…
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!
Thank you very very very mutch.It works; database and data file recovered successfully.
Thank you very very very mutch .It works; database and data file recovered successfully.
Life saver!! Simple set of instructions got me exactly what I needed and my database is back up and running.
Thank you very much.
It works like a charm. Thanls
Thanks so much. It’s great help.
Thank you. You saved me !!! The command – recover database using backup controlfile until cancel; fixed my RECOVER Database problem.