Introduction

For that reason, you must export and rebuild your database after using this recovery method.

SCENARIO 1

Most of the time, this recovery method is required when a data file has been left in hot backup mode through several backup cycles without an intervening shutdown and startup. Upon shutdown and startup the database will complain that a file (usually file id#1 the SYSTEM tablespace) needs more recovery and asks for logs past all available archive logs and online logs.

SCENARIO 2

An other scenario could be that the database is recovered from a hot backup and the above scenario occurs, or, the database asks for an archive log that is before any that are available (usually for the ROLLBACK segment tablespace datafiles.)

A typical error stack would be like that:

SVRMGR> connect internal Connected.

SVRMGR> startup

ORACLE instance started.

Total System Global Area xxx bytes

Fixed Size xxx bytes

Variable Size xxx bytes

Database Buffers xxx bytes

Redo Buffers xxx bytes

Statement processed.

ALTER DATABASE OPEN resetlogs * ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oradata/ORCL/system01.dbf'

Or:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 12 needs more recovery to be consistent ORA-01110:

data file 12: '/u12/oradata/ORCL/data01.dbf'

If all available archive logs are applied and all available online redo logs applied and the error is not corrected, only then should use of the parameter "_allow_resetlogs_corruption" be considered. Make sure a good backup of the database in a closed state (all files) is taken before attempting recovery using "_allow_resetlogs_corruption".

It cannot be stressed enough that the database will no longer be supported by Oracle until it is rebuilt after this type of recovery is accomplished.

HOW TO USE "_allow_resetlogs_corruption"

1. If no recovery attempts have been made, shutdown and backup the database as-is (all files).

2. If recovery attempts have been made, recovery database to state just before any other recovery attempts where made.

3. Use svrmgrl, sqlplus or appropriate interface to startup the database in a mounted by not open condition:

·         STARTUP MOUNT

4. Ensure all datafiles are set to END BACKUP status:

·         SELECT 'alter database datafile '||file_name||' END

BACKUP;' from v$datafile;

5. Alter the database into open condition:

·         ALTER DATABASE OPEN RESETLOGS;

6. If the database asks for recovery, use a UNTIL CANCEL type recovery and apply all available archive and online redo logs, then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; command.

7. If the database asks for logs that are no longer available or the above still resulted in errors, shutdown the database.

8. Insert into the initialization file the following line:

·         _allow_resetlogs_corruption=TRUE

9. Use svrmgrl, sqlplus or appropriate interface to startup the database in a mounted by not open condition:

·         STARTUP MOUNT

10. Ensure all datafiles are set to END BACKUP status:

·         SELECT 'alter database datafile '||file_name||' END

BACKUP;' from v$datafile;

11. Alter the database into open condition:

·         ALTER DATABASE OPEN RESETLOGS;

12. If the database asks for recovery, use a UNTIL CANCEL type recovery and apply all available archive and online redo logs, then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; command.

13. One the database is open, immediately do a full export of the database, or an export of the schemas you need to recover.

14. Shutdown the database, remove the parameter

"_allow_resetlogs_corruption"

15. Rebuild database

16. Import to finish the recovery

17. Implement a proper backup plan and procedure 18. It may be advisable to perform an ANALYZE TABLE…VALIDATE STRUCTURE CASCADE on critical application tables after the recovery and before the export.

Note: Uncommitted records that had been written to disk will possible be marked as committed by this procedure.