DATABASE POINT IN TIME RECOVERY (DBPITR)

Suppose a situation when some rows are inserted in table by mistake and you have committed also. Now You want to revert back the table to previous state when unwanted rows were not inserted.   
Lets assume that you have some 458752 rows in the emp table and you have inserted more 458752 in the same table making total rows 917504.
Later you realized that you want to revert back newly inserted 458752 rows.

Another situation may be when you have dropped a table by mistake and you want your past database state when the table was present.

Or suppose you are upgrading the database/application and upgrade failed. Now you want to bring back the database state which was before upgrade.

For all these situations we can restore the database and can do incomplete recovery up to our desired point of time known as " DATABASE POINT IN TIME RECOVERY (DBPITR) ".

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     29
Next log sequence to archive   30
Current log sequence           30
SQL> conn u1/u1
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
    458752

Note that when emp table was having only 458752 then log sequence number was 30.

Now insert the rows in the table.

SQL> insert into emp select * from emp;

458752 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

  COUNT(*)
----------
    917504

SQL> conn sys/sys as sysdba
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     40
Next log sequence to archive   41
Current log sequence           41

SQL> select sequence#,thread#,first_change#, to_char(first_time,'DD/MON/YY HH24:MI:SS') TIME from v$log order by TIME;

 SEQUENCE#    THREAD# FIRST_CHANGE# TIME
---------- ---------- ------------- ------------------
        40          1       1029118 26/MAR/13 11:12:19
        41          1       1029161 26/MAR/13 11:12:21

After inserting the news rows total Number of rows became 917504 and log sequence went from 30 to 41.
If we want to have only 458752 then we will have to restore the backup and then do incomplete recovery of database  upto archive log sequence number 30. So now lets take the rman backup of database as following.

RMAN> run
2> {
3> allocate channel dsk1 type disk;
4> allocate channel dsk2 type disk;
5> allocate channel dsk3 type disk;
6> backup database format 'c:\oradata\rmanbkp\%U.rbf';
7> sql "alter system switch logfile";
8> backup archivelog all format 'c:\oradata\rmanbkp\Arch%U.rbf';
9> backup current controlfile format 'c:\oradata\rmanbkp\CTL%U.rbf';
10> }

We have taken backup of full database with all archive logs and current controlfile. Now remove the current database and start the restore using rman.

RMAN> startup nomount

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1373964 bytes
Variable Size                213911796 bytes
Database Buffers              41943040 bytes
Redo Buffers                   6410240 bytes

RMAN> restore controlfile from 'C:\oradata\rmanbkp\CTL0CO5G2HR_1_1.RBF';

Starting restore at 26-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\ORADATA\ORCL\CONTROL01.CTL
output file name=C:\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 26-MAR-13

RMAN> mount database;

database mounted
released channel: ORA_DISK_1

Now the database is mounted and and we will have to restore and recover the database upto archive log sequence 30 only . For this we will have to use "UNTIL SEQUENCE" clause of RMAN. If you want recover the database upto sequence 30 then set the command "set until sequence 31;".

RMAN> run {
2>  set until sequence 31;
3>  allocate channel dsk1 type disk;
4>  allocate channel dsk2 type disk;
5>  allocate channel dsk3 type disk;
6>  restore database;
7>  recover database;
8>  }

executing command: SET until clause

allocated channel: dsk1
channel dsk1: SID=63 device type=DISK

allocated channel: dsk2
channel dsk2: SID=129 device type=DISK

allocated channel: dsk3
channel dsk3: SID=193 device type=DISK

Starting restore at 26-MAR-13

channel dsk1: starting datafile backup set restore
channel dsk1: specifying datafile(s) to restore from backup set
channel dsk1: restoring datafile 00002 to C:\ORADATA\ORCL\SYSAUX01.DBF
channel dsk1: restoring datafile 00004 to C:\ORADATA\ORCL\USERS01.DBF
channel dsk1: reading from backup piece C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF
channel dsk2: starting datafile backup set restore
channel dsk2: specifying datafile(s) to restore from backup set
channel dsk2: restoring datafile 00001 to C:\ORADATA\ORCL\SYSTEM01.DBF
channel dsk2: restoring datafile 00003 to C:\ORADATA\ORCL\UNDOTBS01.DBF
channel dsk2: reading from backup piece C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF
channel dsk1: piece handle=C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk1: restored backup piece 1
channel dsk1: restore complete, elapsed time: 00:00:56
channel dsk2: piece handle=C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk2: restored backup piece 1
channel dsk2: restore complete, elapsed time: 00:01:06
Finished restore at 26-MAR-13

Starting recover at 26-MAR-13

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001
archived log for thread 1 with sequence 20 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001
archived log for thread 1 with sequence 21 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001
archived log for thread 1 with sequence 22 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001
archived log for thread 1 with sequence 23 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001 thread=1 sequence=19
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001 thread=1 sequence=20
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001 thread=1 sequence=21
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001 thread=1 sequence=22
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001 thread=1 sequence=23
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000024_0807881364.0001 thread=1 sequence=24
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000025_0807881364.0001 thread=1 sequence=25
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000026_0807881364.0001 thread=1 sequence=26
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000027_0807881364.0001 thread=1 sequence=27
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000028_0807881364.0001 thread=1 sequence=28
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000029_0807881364.0001 thread=1 sequence=29
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000030_0807881364.0001 thread=1 sequence=30
media recovery complete, elapsed time: 00:00:06
Finished recover at 26-MAR-13
released channel: dsk1
released channel: dsk2
released channel: dsk3

If you see the RMAN log then observe that rman has used archive upto sequence 30 only for recovery and have ignored further archives because we have used SET UNTIL SEQUENCE command .

RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs


C:\oradata\orcl>sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 26 12:30:46 2013

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


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

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> conn u1/u1
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
    458752

Please leave your comments and suggestions for betterment of this post.