How to recover and open the database if the archive log required for recovery is missing.
we had to recover one of our development databases from old backup.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.
It was critical for us to recover database because of some project deadline.
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’
After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.
We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.
Alert log shows below error
Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 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
To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.
It resolved our issue and database was up and running without any issue.
_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.
As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.