Performing Recovery through RESETLOGS

Imagine you have a large database that is required to operate 24 hours, 7 days a week. If a media failure occurs, your recovery time has to be minimized and you have to perform an open database backup after the recovery. However, another media failure happens before you complete the backup after the incomplete recovery. You must now repeat the recovery you performed initially (which you ended with RESETLOGS), and then you must apply the additional logs that were generated after that time. In this situation, you are performing a "recovery through RESETLOGS."

Below is a set of criteria for you to evaluate to determine whether a recovery through RESETLOGS can be performed. All of the criteria in the table must be true.

CATEGORY	EXPLANATION
Oracle database	Must be 7.3.3 and above
Whole backups after RESETLOGS	No open or closed whole backup AFTER the database was opened with the RESETLOGS option
Whole backups before RESETLOGS	Have an whole open or closed backup BEFORE the database was opened with the RESETLOGS option
Control files	You have the control files BEFORE and AFTER you open the database with the RESETLOGS option
Logs	All the archived logs and online redo logs are present
Alert log	Contains information from the last incomplete recovery

Recovery through RESETLOGS is a complex process. You should try it only with an experienced DBA at your side. In a real job situation, if you have any doubts about the recovery process, you should contact Oracle technical support. The consequences of mistakes are too high. In the series of images below, suppose the DBA has just completed an incomplete recovery and opened the database with the RESETLOGS option. The current redo log sequence number is #1. He has not yet backed up the current database. Another media failure occurs, damages one of the datafiles and brings down the database. The following series of images shows you the steps of recovery through RESETLOGS.

When you perform recovery through RESETLOGS, make sure you follow the steps in the order that they are presented. Do not skip any of the steps in the process.

1. Make a backup of the current database control file.
2. Restore the datafiles and control files from the backup taken before you opened the current database with the RESETLOGS option.
3. Start the database in mount mode.
4. In your alert logs, find the change number at the completion of your last incomplete recovery.
5. Recover the database via the RECOVERY DATABASE UNTIL CHANGE <CHANGE_NUMBER> USING BACKUP CONTROLFILE; statement.
6. Shut down the database using the NORMAL or IMMEDIATE option.
7. Restore the current control file from the backup made in Step 1.
8. Mount the database.
9. Recover the database using the RECOVER DATABASE command with the appropriate option.

If you plan to recover to the current log sequence number, you should use complete database recovery via the RECOVER [AUTOMATIC] DATABASE command. However, sometimes you may only want to recover to an earlier log sequence number and you have to use the incomplete recovery with the appropriate option and open the database with the RESETLOGS option.

1. Open the database and check whether the data you expected to recover exists if you planned to recover to an earlier redo log sequence number. Check the data dictionary view V$LOG to ensure you recovered to the current log sequence number if you planned to recover to the current redo log sequence number.
2. Make a whole database backup to prevent the need for this kind of recovery in the future.