Thursday, September 2, 2010

Open database with inconsistent data

The backup script was accidentally deleted by SA. We don't have the backup and the customer require a recovery to a certain date. The only backup we have is an operating system backup and all archive logs. I started with a backup and applied archivelogs for about 2 days. But no matter how many archive logs applied, I still got the following message:

Specify log: {=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\ASMP\SYSTEM01.DBF'

Solution:

at init file set _ALLOW_RESETLOGS_CORRUPTION=TRUE and then the database is opened.
Do a full export of the database, create a new one and do an import.

SQL> startup mount;ORACLE instance started.
Total System Global Area 453498908 bytesFixed Size 75804 bytesVariable Size 295604224 bytesDatabase Buffers 157286400 bytesRedo Buffers 532480 bytesDatabase mounted.SQL> recover database using backup controlfile until cancel;ORA-00279: change 754753809 generated at 08/24/2010 12:15:12 needed for thread1ORA-00289: suggestion : E:\ORACLE\ORADATA\ASMP\ARCHIVE\ARC82677.001ORA-00280: change 754753809 for thread 1 is in sequence #382677
Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 754755854 generated at 08/24/2010 12:15:53 needed for thread1ORA-00289: suggestion : E:\ORACLE\ORADATA\ASMP\ARCHIVE\ARC82678.001ORA-00280: change 754755854 for thread 1 is in sequence #382678ORA-00278: log file 'E:\ORACLE\ORADATA\ASMP\ARCHIVE\ARC82677.001' no longerneeded for this recovery
Specify log: {=suggested filename AUTO CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: 'E:\ORACLE\ORADATA\ASMP\SYSTEM01.DBF'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL>

No comments: