7.6 Performing Database Point-In-Time Recovery

Database point-in-time recovery (DBPITR) restores the database from backups prior to the target time for recovery, then uses incremental backups and redo to roll the database forward to the target time.

DBPITR is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database.

The target SCN can be specified using a date and time, in which case the operation is sometimes called time-based recovery.

7.6.1 Requirements for Database Point-in-Time Recovery

The requirements for database point-in-time recovery are as follows:

Your database must be running in ARCHIVELOG mode.
You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs for the period between the SCN of the backups and the target SCN.

7.6.2 Point-in-Time Recovery and Database Incarnations: Concepts

Understanding DBPITR requires background information on database incarnations and how RMAN treats backups from times not in the current incarnation path. In particular, there are special considerations if you are returning your database to a point in time prior to the most recent OPEN RESETLOGS.

This section contains the following topics:

Understanding Parent, Ancestor and Sibling Database Incarnations
Incarnation History of a Database: Example
Database Incarnations and Orphaned Backups

7.6.2.1 Understanding Parent, Ancestor and Sibling Database Incarnations

A new incarnation of a database is created whenever each time the database is opened with the RESETLOGS option. Performing an OPEN RESETLOGS archives the current online redo logs, Incarnation resets the log sequence number to 1, and then gives the online redo logs a new time stamp and SCN. It also increments the incarnation number, which is used to uniquely tag and identify a stream of redo.

Incarnations can stand in several relations to each other:

The incarnation from which the current incarnation branched following an OPEN RESETLOGS operation is called the parent incarnation of the current incarnation.
The parent incarnation and all of its parent incarnations are the ancestor incarnations of the current incarnation.
Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

7.6.2.2 Incarnation History of a Database: Example

Figure 7-1, "Database Incarnation History With Multiple Resetlogs" shows a database that goes through several incarnations.

Figure 7-1 Database Incarnation History With Multiple Resetlogs

Description of "Figure 7-1 Database Incarnation History With Multiple Resetlogs"

Incarnation 1 of the database starts at SCN 1, and continues through SCN 1000 to SCN 2000. At SCN 2000 in incarnation 1, you perform a point-in-time recovery back to SCN 1000, and open the datbase with a RESETLOGS operation. This creates incarnation 2, which begins at SCN 1000 and contines to SCN 3000. At SCN 3000 in incarnation 2, you perform another point-in-time recovery and RESETLOGS operation. This creates incarnation 3, starting at SCN 2000.

You can view the incarnation history of a database using the LIST INCARNATION command. Output describing the incarnation history in the figure is:

LIST INCARNATION;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID          STATUS   Reset SCN  Reset Time
------- ------- -------- -------------- -------  ---------- ----------
1       1       TRGT     930791268      PARENT   1          23-APR-05
2       2       TRGT     930791268      PARENT   1000       23-APR-05
3       3       TRGT     930791268      CURRENT  2000       23-APR-05

The value in the Reset SCN column is the SCN at which the RESETLOGS was performed. The Inc Key column is the incarnation key. RMAN uses the incarnation key to identify the database incarnation in some commands, such as using RESET DATABASE TO INCARNATION to change the current incarnation in some complex recovery scenarios.

7.6.2.2.1 Sibling Incarnations, Ambiguous SCNs and RESET DATABASE INCARNATION

When working with a database where flashback or point-in-time recovery operations have produced sibling incarnations, note that a given SCN value can refer to more than one point in time, depending upon which incarnation has been set as the current incarnation. For example, in the figure, SCN 1500 could refer to a point in incarnation 1 or 2.

By default, when used with an RMAN command like FLASHBACK DATABASE or RECOVER... UNTIL, an SCN is assumed to refer to the current incarnation path, rather than sibling incarnations. However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation. For example, consider the following command used in point-in-time recovery:

RMAN> RECOVER DATABASE TO SCN 1500;

If used in the database described in Figure 7-1, SCN 1500 refers to incarnation 2 by default. If, however, you run the following sequence of commands:

RMAN> RESET DATABASE INCARNATION TO 1;
RMAN> RECOVER DATABASE TO SCN 1500;

SCN 1500 refers to the point in time during incarnation 1 when the SCN was 1500.

7.6.2.3 Database Incarnations and Orphaned Backups

When a database goes through multiple incarnations, some backups can become orphaned. Orphaned backups are backups that are created during incarnations of the database that are not ancestors of the current incarnation.

Given the database from the example in "Point-in-Time Recovery and Database Incarnations: Concepts", the following table explains which backups are orphans, based upon which incarnation is current.

Current Incarnation	Usable Backups (Nonorphaned)	Orphaned Backups
Incarnation 1	All backups from incarnation 1	All backups from incarnations 2 and 3
Incarnation 2	All backups from incarnation 1 prior to SCN 1000 All backups from incarnation 2	Backups from incarnation 1 after SCN 1000. All backups from incarnation 3
Incarnation 3	All backups from incarnation 1 prior to SCN 1000 All backups from incarnation 2 prior to SCN 2000 All backups from incarnation 3	All backups from incarnation 1 after SCN 1000 All backups from incarnation 2 after SCN 2000

7.6.2.3.1 Uses of Orphaned Backups

Orphaned backups are usable by RMAN in cases where you wish to restore the database to a point in time not in the current incarnation path. RMAN is able to restore backups from direct ancestor incarnations and recover to the current time, even across OPEN RESETLOGS operations, as long as a continuous path of archived logs exists from the earliest backups to the point to which you want to recover. RMAN can also perform restore and recovery with orphaned backups, if you restore a control file from an incarnation in which the changes represented in the backups had not been abandoned.

7.6.3 Preparing for Database Point-in-Time Recovery

Take the following steps to prepare for DBPITR:

Determine the target time, SCN, restore point, or log sequence number that should end recovery. The Flashback Query, Flashback Version Query and Flashback Transaction Query features can help you identify when the logical corruption occured.

You can also examine the alert.log for information that may help you determine the time of the event from which you need to recover.

Alternatively, you can determine the log sequence number that contains the target SCN, and then recover through that log. For example, query V$LOG_HISTORY to view the logs that have been archived.
```
RECID      STAMP      THREAD#    SEQUENCE#  FIRST_CHAN FIRST_TIM NEXT_CHANG
---------- ---------- ---------- ---------- ---------- --------- ----------
         1  344890611          1          1      20037 24-SEP-04    20043
         2  344890615          1          2      20043 24-SEP-04      20045
         3  344890618          1          3      20045 24-SEP-04      20046
```
If, for example, you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m., just before the drop occurred. You will lose all changes to the database made after that time.
If you are using a target time expression instead of a target SCN, then make sure that the time format environment variables are set appropriately before invoking RMAN. The following are sample Globalization Support settings:
```
NLS_LANG = american_america.us7ascii
NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS" 
```

7.6.4 Database Point-in-Time Recovery Within the Current Incarnation

DBPITR within the current incarnation is performed using the current control file. When performing DBPITR, you can avoid errors by using the SET UNTIL command to set the target time at the beginning of the process, rather than specifying the UNTIL clause on the RESTORE and RECOVER commands individually. This ensures that the datafiles restored from backup will have timestamps early enough to be used in the subsequent RECOVER operation.

The steps required for DBPITR are as follows:

Connect RMAN to the target database and, if applicable, the recovery catalog database. Bring the database to a MOUNT state:
```
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
```
Perform the following operations within a RUN block:
1. Use SET UNTIL to specify the target time, restore point, SCN, or log sequence number for DBPITR. If specifying a time, then use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.
2. If automatic channels are not configured, then manually allocate disk and tape channels as needed.
3. Restore and recover the database.
The following example performs DBPITR on the target database until SCN 1000:
```
RUN
{ 
    SET UNTIL SCN 1000;    
  # Alternatives:
  # SET UNTIL TIME 'Nov 15 2004 09:00:00';
  # SET UNTIL SEQUENCE 9923;  
  RESTORE DATABASE;
  RECOVER DATABASE;
}
```

Note:

You can also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time:

SET UNTIL TIME 'Nov 15 2004 09:00:00';
  SET UNTIL SEQUENCE 9923;  
  SET UNTIL RESTORE POINT before_update;

If the operation completes without errors, then your DBPITR has succeeded. You can open the database read-only and perform queries as needed to ensure that the effects of the logical corruption have been reversed. If not, you may have chosen the wrong target SCN. In such a case, investigate the unwanted change further and determine a new target SCN, then repeat the DBPITR process.

7.6.4.1 Using a Time Expression for Database Point-in-Time Recovery

You can use a time expression instead of the SCN in the SET UNTIL statement, as shown in the preceding example. However, note that if you use SET UNTIL TIME to specify the target time for point-in-time recovery, some times that you can specify may not be in the current incarnation. The database may have been in an ancestor incarnation, or even in a sibling incarnation, at the target time. If your target time is not in the current incarnation, then see "Point-in-Time Recovery to an Ancestor Incarnation" for more information on DBPITR to ancestor incarnations, and Oracle Database Backup and Recovery Advanced User's Guide for more information on DBPITR to incarnations that are not ancestors of the current incarnation.

7.6.5 Options After Database Point-in-Time Recovery

After a successful DBPITR, your choices are:

Export one or more objects from your database using an Oracle export utility such as Data Pump Export. You can then recover the database to the current point in time and re-import the exported objects, as a way to return these objects to their state prior to the unwanted change without abandoning all other changes.
Open your database for read-write, abandoning all changes after the target SCN. In such a case, you must open the database with the RESETLOGS option, as shown here:
```
RMAN> ALTER DATABASE OPEN RESETLOGS;
```
The current online redo logs are archived, the log sequence number is reset to 1, and the online redo logs are given a new time stamp and SCN. Identifying redo log files with a new log sequence number and incarnation eliminates the possibility of corrupting datafiles by the application of obsolete archived redo logs.

The OPEN RESETLOGS operation will fail if a datafile is off-line, unless the datafile went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS because they do not need any redo.

7.6.6 Point-in-Time Recovery to an Ancestor Incarnation

The main differences between DBPITR within the current incarnation and to an SCN in an ancestor incarnation are that you must reset the incarnation of the database to the incarnation that was current at the target SCN, and you must restore a control file from the incarnation containing the target SCN.

Assume the following situation:

You run RMAN with a recovery catalog.
You have a backup of target database trgt from October 2, 2004.
DBPITR was performed on this database on October 10, 2004 to correct an earlier error. The OPEN RESETLOGS operation at the end of that DBPITR started a new incarnation.

On October 25, you discover that you need crucial data that was dropped from the database at 8:00 a.m. on October 8, 2004. This time is prior to the beginning of the current incarnation.

To perform point-in-time recovery to the older incarnation, use the following steps:

Determine which incarnation was current at the time of the backup of 2 October. Use LISTINCARNATION to find the primary key of the incarnation that was current at the target time:

LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key  Inc Key   DB Name   DB ID       STATUS     Reset SCN    Reset Time
------- -------   -------   ------      -------    ----------   ----------
1       2         TRGT      1224038686  PARENT     1            02-OCT-04
1       582       TRGT      1224038686  CURRENT    59727        10-OCT-04

Look at the Reset SCN and Reset Time columns to identify the correct incaration, and note the incarnation key in the Inc Key column. In this case, the incarnation key value is 2.

Make sure the database is started but not mounted.
```
STARTUP FORCE NOMOUNT
```
Reset trgt to the incarnation that was current at the time of the backup of 2 October. Use the value from the Inc Key column to identify the incarnation.
```
# reset database to old incarnation
RESET DATABASE TO INCARNATION 2;
```
Restore and recover the database, performing the following actions in the RUN command:
- Set the end time for recovery to the time just before the loss of the data.
- Allocate any channels required that are not already configured.
- Restore the control file from the October 2 backup and mount it.
- Restore the datafiles and recover the database. Use the RECOVER DATABASE ... UNTIL command to perform point-in-time recovery, bringing the database to the target time of 7:55 a.m. on October 8, just before the data was lost.
The following example shows all of the steps required in this case:
```
RUN
{
  # set target time for all operations in the RUN block
  SET UNTIL TIME 'Oct 8 2004 07:55:00'; 
  RESTORE CONTROLFILE ;
# without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP
  ALTER DATABASE MOUNT; 
  RESTORE DATABASE;
  RECOVER DATABASE;
}
```

ALTER DATABASE OPEN RESETLOGS;