Tricky Oracle Recovery for missing Archive Log!!

 Author: James Bryan

It is the most widely encountered scenario by most database administrators where we try to recover database to some previous point in time but the archive logs that we need are accidentally removed.

Today we will discuss a tricky recover scenario where some development databases need to be recovered from old backup. The controlfile is successfully recreated but unfortunately the required archive logs are not there.

Incomplete recovery:

Oracle "UNTIL CANCEL" clause specifies that you are performing an incomplete
recovery. Omitting this clause makes Oracle assume that you want to perform a complete recovery. Attempting incomplete recover for our database failed with below error.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

SPECIFY LOG: {=SUGGESTED | FILENAME | AUTO | CANCEL}
cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf'
ORA-01112: media recovery not started

SQL> ALTER DATABASE OPEN RESETLOGS;

alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf'

_ALLOW_RESETLOGS_CORRUPTION Parameter:

_ALLOW_RESETLOGS_CORRUPTION is Oracle hidden parameter that allows resetlogs even if it will cause corruption. Setting _ALLOW_RESETLOGS_CORRUPTION to true allows us to open our database even if it is not properly recovered. Although your database will open without consistency checks but it may result in a corrupted database and needs to be recreated.

Force Open Database:

Now if we force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE then our database gets open but the instance gets crashed immediately after open due to undo tablespace corruption.

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Wed Apr 30 08:15:30 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340

Resolve Undo Corruption Issue:

Oracle UNDO_MANAGEMENT parameter specifies which undo space management mode should be used by the system. We changed UNDO_MANAGEMENT parameter in init.ora file to MANUAL so as to resolve the undo corruption problem. This allowed our database to successfully open and our database was up and running.

Now drop the old corrupted undo tablespace and create a new undo tablespace. After that we changed back the UNDO_MANAGEMENT parameter to AUTO and UNDO_TABLESPACE to NEWUNDOTABLESPACE.

It will resolve our issue and database will be up and running without any issue.

Rebuild Database:

Once the database is opened, then immediately rebuild the database by performing a full-database export, then creating a brand new and separate database, and finally importing the recent export dump.

Recovering Oracle Database with missing Archive Log:

Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Effective Tips for Fixing Online Redo Log Corruption!!
   Great Tips for Protecting Oracle Listener!!
   Inside Oracle DETERMINISTIC Clause!!
   Boost Oracle Performance by forcing Query Order!!