08-15-2015, 08:42 AM
Inconsistent database recovery
Introduction:
This document provides high level steps in order to recover the database when all the archivelogs and the redo logs of the database has been lost.
Issue details and steps taken to resolve:
There might be cases when you end up having no backups and the disks of the database gets crashed or some one has deleted all the archivelogs of the database in a test environment where data might be critical.
The below is the error message received when we tried a recovery of the database.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 456781 generated at 10/19/2013 14:56:44 needed for thread 1
ORA-00289: suggestion : 234.arc
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: ‘SYSTEM01.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: ‘SYSTEM01.dbf’
We tried getting any of the old backups and the archivelogs copy from the disk mirrors but unfortunately nothing seems to have helped.
After doing lot of research, we ended up setting up the below parameters and then started the database which helped in opening the database in an in-consistent state. Update the below parameters in the parameter file (if running in spfile, create a copy of the same).
_ALLOW_RESETLOGS_CORRUPTION = TRUE
UNDO_MANAGEMENT = MANUAL
Comment out the other parameters related to Undo tablespace, etc so that Oracle will not check the undo / rollback segments.
Once the above is done, startup the database in MOUNT state and then open the database with resetlogs.
If this fails with a ORA-600 error, then it might be because of the SCN being not updated correctly in the datafile headers and hence the SCN can be bumped slightly by restarting the database couple of times otherwise setting a numeric value for the “_minimum_giga_scn” parameter.
Since the datafiles header is not in a consistent state, the best option is to export and imnport the data into a new database. In our case, it was simple as the size of the database was around 120GB only.
References : Database team worked with self in this issue. Also, this should not be tried in production environments or big databases.
Thanks.
--------------------------------------------- End of Document ---------------------------------------------------------
Introduction:
This document provides high level steps in order to recover the database when all the archivelogs and the redo logs of the database has been lost.
Issue details and steps taken to resolve:
There might be cases when you end up having no backups and the disks of the database gets crashed or some one has deleted all the archivelogs of the database in a test environment where data might be critical.
The below is the error message received when we tried a recovery of the database.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 456781 generated at 10/19/2013 14:56:44 needed for thread 1
ORA-00289: suggestion : 234.arc
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: ‘SYSTEM01.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: ‘SYSTEM01.dbf’
We tried getting any of the old backups and the archivelogs copy from the disk mirrors but unfortunately nothing seems to have helped.
After doing lot of research, we ended up setting up the below parameters and then started the database which helped in opening the database in an in-consistent state. Update the below parameters in the parameter file (if running in spfile, create a copy of the same).
_ALLOW_RESETLOGS_CORRUPTION = TRUE
UNDO_MANAGEMENT = MANUAL
Comment out the other parameters related to Undo tablespace, etc so that Oracle will not check the undo / rollback segments.
Once the above is done, startup the database in MOUNT state and then open the database with resetlogs.
If this fails with a ORA-600 error, then it might be because of the SCN being not updated correctly in the datafile headers and hence the SCN can be bumped slightly by restarting the database couple of times otherwise setting a numeric value for the “_minimum_giga_scn” parameter.
Since the datafiles header is not in a consistent state, the best option is to export and imnport the data into a new database. In our case, it was simple as the size of the database was around 120GB only.
References : Database team worked with self in this issue. Also, this should not be tried in production environments or big databases.
Thanks.
--------------------------------------------- End of Document ---------------------------------------------------------