http://www.toadworld.com/platforms/oracle/w/wiki/1010.cancel-based-recovery.aspx
Cancel-Based recovery allows the DBA to cancel recovery at a desired point. This situation is most likely occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored. In this situation, you would apply all logs until you reached the missing files and then cancel the recovery.
Follow these steps to execute a cancel-based recovery:
- If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.
- Make a full backup of the database, including all datafiles, a control file, and the parameter files in case an error is made during the recovery.
- Correct the problem that caused the media failure. If the problem cannot be corrected, the datafiles must be restored to an alternate location. If this is the case, the ALTER TABLESPACE RENAME DATAFILE command must be used to change the location of the datafile in the control file.
- If the current control files do not match the physical structure of the database at the time you want to recover to, restore a backup of the control file that matches the database's physical file structure at the point in time you want to recover to. Replace all current control files of the database with the one you want to use for recovery. If you do not have a backup copy of the control file, you can create a new one.
- Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.
Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.
- Start SQL*Plus and connect to Oracle as SYS.
- Start the instance and mount the database using the STARTUP command with the MOUNT option.
- If you restored files to an alternative location, change the location now in the control file by using the ALTER TABLESPACE RENAME DATAFILE command.
- Use the RECOVER DATABASE UNTIL CANCEL command to begin cancel-based recovery. If a backup of the control file is being used, make sure to specify the USING BACKUP parameter.
Oracle will now start the roll forward by applying the archived redo logfiles and the online redo logfile. Oracle will prompt you for each logfile. If you used a backup control file, you must enter the names of the online redo logfiles.
- Continue applying redo logfiles until the most recent, undamaged logfile has been applied.
Enter "CANCEL" instead of the logfile name to cancel the recovery. Oracle will respond with a recovery successful message.
- Use the ALTER DATABASE OPENcommand with the RESETLOGS or NORESETLOGS option. You should use the RESETLOGS option if you used a backup of the control file in recovery, or the recovery was incomplete. Use the NORESETLOGS option if the recovery was complete. If you are using a standby database and must reset the logs, the standby database will have to be re-created.
You can check the ALERT file to see if your incomplete recovery was actually a complete recovery. If the recovery was a complete recovery, the message in the ALERT file is as follows
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is recorded:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
- After opening the database using the RESETLOGS option, perform a normal shutdown and a full database backup. If you do not do this, any changes made after the recovery and before the next full backup are unrecoverable. If you did not reset the logs, the database is still recoverable.