Overview of Oracle Database Point-in-time Recovery Feature

Oracle Database point in time recovery (DBPITR) is used when the data within database is screwed up and needs to be restored in a point back in time. 

DBPITR can be executed with the following requirements: 

• Database should be running in ARCHIVELOG mode.
• You should have datafiles backup from before the target SCN for DBPITR. 
• You should have archived redo logs backup between the SCN of the backups and the target SCN. 

DBPITR can be executed with the following steps: 

I am assuming that your database following conditions is true:  

• You have already RMAN with a recovery catalog.
• You already have target database backup. (trgt from December 1, 2012 )
• Assume that you had performed DBPITR on your database on December 10, 2012 to correct earlier errors. 
• The OPEN RESETLOGS operation at the end of that DBPITR started a new incarnation.

On December 25, you found out that your database was dropped at 8:00 a.m. on December 8, 2012 & you need your precious data at any cost. 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:

1. Determine which incarnation was current at the time of the backup of 1 December. 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            01-Dec-12

1       582       TRGT      1224038686  CURRENT    59727        10-Dec-12

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

2. Make sure the database is started but not mounted.

STARTUP FORCE NOMOUNT

3. Reset trgt to the incarnation that was current at the time of the backup of 1 December. Use the value from the Inc Key column to identify the incarnation.

# reset database to old incarnation

RESET DATABASE TO INCARNATION 2;

4. 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 December 1 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 December 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 'Dec 8 2012 07:55:00'; 

  RESTORE CONTROLFILE;

# without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP

  ALTER DATABASE MOUNT; 

  RESTORE DATABASE;

  RECOVER DATABASE;

}

Hope the article will help you to easily understand the Oracle Database Point-in-time Recovery Feature.