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:
# set target time for all operations in the RUN block
SET UNTIL TIME 'Dec 8 2012 07:55:00';
# without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP
ALTER DATABASE MOUNT;
Hope the article will help you to easily understand the Oracle Database Point-in-time Recovery Feature.