How to perform Database Point in Time Recovery(DBPITR)

DBPITR abbreviates to Database point in time recovery. When we want to rewind our database back to a previous time then we can use DBPITR to do that.

When we have performed some unwanted changes in our database and we want to undo that, we can perform DBPITR to undo that. 

Only perform DBPITR when you are not able to undo the unwanted change with Flashback technology.

Disadvantages :-

1. Its a time consuming process as whole database has to be restored from the backup and recovered to a earlier time using archive logs and incremental backups.

2. Unlike TSPITR in which only particular tablespace objects are inaccessible  whole database is unavailable during this entire process. 

3. All changes which are made after the TIME or SCN or log sequence which we used in DBPITR will be lost.

Requirements :-

1. Database has to be in archive log mode.
2. A valid backup of full database is needed and all archive logs or incremental backup after that backup will be required.

##NOTE:- When we have to undo those changes which are are present in a different incarnation then DBPITR has to be done because we cannot rewind particular objects to a different incarnation, we have to rewind whole database.

Example :-

Consider we create a table named AMIT and AMIT_BCKUP and insert some records in that. Now accidentally we performed a drop command on table. Now we want to rewind our database to a time before drop operation. PFB steps :-

SQL> select table_name from user_tables where table_name like 'AM%';

TABLE_NAME
------------------------------
AMIT
AMIT_BCKUP

SQL> drop table AMIT;

Table dropped.

SQL> drop table AMIT_BCKUP;

Table dropped.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size                  2175928 bytes
Variable Size             872418376 bytes
Database Buffers          620756992 bytes
Redo Buffers                7847936 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\NewAdmin>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 4 02:52:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2120650031, not open)

RMAN> run
2> {
3> set until time "sysdate-1/24/60*6";   ########### We rewind our database 6 mins before###########
#set until scn 10023;
#set until sequence 345 thread 2;
#set until restore point AMIT_RESTORE; #######AMIT_RESTORE point already created##########
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 04-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_8ZSV29QW_.DBF
channel ORA_DISK_1: restoring datafile 00002 to G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_8ZSV29Y3_.DBF
channel ORA_DISK_1: restoring datafile 00003 to G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_8ZSV2B0R_.DBF
channel ORA_DISK_1: restoring datafile 00004 to G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\O1_MF_USERS_8ZSV2B72_.DBF
channel ORA_DISK_1: reading from backup piece G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_08_03\O1_MF_NNNDF_TAG20130803T191402_8ZT292Z5_.BKP
channel ORA_DISK_1: piece handle=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_08_03\O1_MF_NNNDF_TAG20130803T191402_8ZT292Z5_.BKP tag=TAG20130803T191402
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 04-AUG-13

Starting recover at 04-AUG-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_03\O1_MF_1_7_8ZT2C6SZ_.ARC
archived log for thread 1 with sequence 8 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_03\O1_MF_1_8_8ZT2GG5P_.ARC
archived log for thread 1 with sequence 1 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_04\O1_MF_1_1_8ZTWR0B7_.ARC
archived log for thread 1 with sequence 2 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_04\O1_MF_1_2_8ZTX0MNK_.ARC
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_03\O1_MF_1_7_8ZT2C6SZ_.ARC thread=1 sequence=7
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_03\O1_MF_1_8_8ZT2GG5P_.ARC thread=1 sequence=8
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_04\O1_MF_1_1_8ZTWR0B7_.ARC thread=1 sequence=1
media recovery complete, elapsed time: 00:00:06
Finished recover at 04-AUG-13

RMAN>

RMAN> alter database open resetlogs;

database opened

RMAN>

RMAN> exit

Recovery Manager complete.

C:\Users\NewAdmin>
C:\Users\NewAdmin>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 4 02:58:18 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select table_name from user_tables where table_name like 'AM%';

TABLE_NAME
------------------------------
AMIT
AMIT_BCKUP

We successfully Rewind our database to a before drop point.

############# Before doing any important change its better to create a restore point , Its helps a lot while doing recovery as no need to find the earlier time or SCN or log sequence to perform recovery##############

SQL> create restore point amit_restore;

Restore point created.

Related Articles :-
 How to perform TSPITR

I hope this article helped you.

Regards,
Amit Rath