How to perform Database Point in time Recovery DBPITR

About Database Point in time recovery
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.

Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.

2)The entire database will be unavailable during the operation.

3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.

Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.

Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations.

In the following steps I demonstrate an example of how we can perform DBPITR.

1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.

SQL> CONN ARJU/A
Connected.

SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.

2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.

SQL> SHUTDOWN ABORT
ORACLE instance shut down.

3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.

SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> }
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08

4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';

using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS

5)Check the objects under Arju Schema.

SQL> conn arju/a
Connected.
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
TesT
MY_TABLE

And see that BEFORE_PITR is lost.

In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.

Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;
}