Recover and open the database with Missing Archived Logs

Every DBA should know that there is a problem here. The missing archived redo logs contain transactions which affect the data in the database. So it’s a given that you’re going to lose some data, but the question is, “How much?” Oracle takes a hard-line position and will not let you open the database normally because a data integrity issue exists. However, you may be able to retrieve much of your data if you use non-traditional means to get Oracle to drop its hard-line attitude. Retrieving the data that can be salvaged with the understanding that some data will be lost could be a whole lot better than losing all of the data because a subset of it has been corrupted.

It was critical for us to recover database because of some project deadline.


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 12/18/2012 09:33:44 needed for
thread 1
ORA-00289: suggestion : /datafs/GOLDFLEX/archivelog/2093.arc
ORA-00280: change … for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | 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: ‘/datafs/GOLDFLEX/SYSTEM01.dbf’

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:


you can use the unix strings command to extract the rollback segments:

$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU

where system01.dbf is the name of the datafile for the SYSTEM tablespace – if you have > 1 system dbf
then you need to do this for each file, writing to a different listSMUn file
each time.

2) Startup Mount

SQL>create pfile=’init<sid>.ora’ from spfile;

Edit the pfile – add :


Where <list> is the list of rollback segment names extracted as above with ‘$’ appended to each name eg

_corrupted_rollback_segments=(_SYSSMU1$, _SYSSMU2$………_SYSSMU10$)

3)Remount the instance using the amended pfile and then do the following:

SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;

4) Recover database
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.

(ZyK collecte)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s