Type of Recovery and Difference between Resetlog and No resetlogs Option

omplete Recovery

Complete recovery can be done with the database OPEN unless the SYSTEM or UNDO tablespaces are damaged (this will terminate the instance).

When your database is runing in no archive log mode and the entire database is restored to the point of the last whole closed backup, called Complete recovery.

When your database is runing archive log mode and Only need to restore lost files and recovers all data to the time of failure, called Complete recovery.

Short step for complete recovery:

* Datafiles/Tablesdpace for restore must be offline.
* Restore only lost or damaged datafiles.
* Recover the datafiles using the Recover command.
* Bring recovered datafiles online

Incomplete Recovery

Incomplete Recovery occurs when complete recovery is impossible or you want to lose some information that was entered by mistake.

You can say, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:

* Media failure destroys some or all of the online redo logs.
* A user error causes data loss, for example, a user inadvertently drops a table.
* You cannot perform complete recovery because an archived redo log is missing.
* You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes.

Difference between ResetLogs and NoResetLogs option?

After incomplete recovery (where the entire redo stream wasn't applied) we use RESETLOGS option. RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

After complete recovery (when the entire redo stream was applied) we use NORESETLOGS option. Oracle will continue using the existing (valid) log files.

What is a cancel-based recovery?

A cancel-based recovery is a user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command. UNTIL CANCEL clause to perform recovery until the user manually cancels the recovery process. Oracle Cancel-Based Recovery is usually performed when there is a requirement to recover up to a particular archived redo log file.

If the user does not specify CANCEL then the recovery process will automatically stop when all redo has been applied to the database.

When Cancel Based Recovery required (Scenario)?

* For example consider a situation where someone dropped a table and one of the online redo logs is missing and is not archived and the table needs to be recovered.
* Another case is where your backup control file does not know anything about the arhivelogs that got created after your last backup.
* Another scenario can be where you have lost all logs pass a specific sequence say X (For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied) and you want to control which archived log terminates recovery. Or a scenario where one of the archived redo log files required for the complete recovery is corrupt or missing and the only recovery option is to recover up to the missing archived redo log file.

NOTE: Remember the online logs must be reset after you perform an incomplete recovery or you perform recovery with a backup control file. So finally you will need to open database in RESETLOGS mode. To synchronize datafiles with control files and redo logs, open database using "resetlogs" options.

What is a point in time recovery?

A point in time recovery is a method to recover your database to any point in time since the last database backup.

We use RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'.

Example: RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30'

If a backup of the control file is being used with this incomplete recovery, then indicate this in the statement used to start recovery.

Example: RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30' USING BACKUP CONTROLFILE

In this type of recovery, apply redo logs until the last required redo log has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.

What is change-based recovery?

Recovers until the specified SCN.

Change-based recovery is a recovery technique using which a database is recovered up to a specified system change number (SCN). Using the UNTIL CHANGE clause with the RECOVER command performs a manual change-based recovery. However, RMAN uses the UNTIL SCN clause to perform a change-based recovery.

Begin change-based recovery, specifying the SCN for recovery termination. The SCN is specified as a decimal number without quotation marks. For example, to recover through SCN 10034 issue:

RECOVER DATABASE UNTIL CHANGE 10034;

Continue applying redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct SCN, and returns a message indicating whether recovery is successful.