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. It’s a time consuming process as whole database has to be restored from the backup and recovered to an 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 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:-
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. It’s a time consuming process as whole database has to be restored from the backup and recovered to an 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 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:-
Recovery Manager11.2.0.1.0
RMAN> Backup database plus archivelog;
Starting backup at 17-MAR-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=59 STAMP=874609124
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_ANNNN_TAG20150317T185844_BJJC0F3C_.BKP tag=TAG20150317T185844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
Starting backup at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP tag=TAG20150317T185846 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NCSNF_TAG20150317T185846_BJJC2VKF_.BKP tag=TAG20150317T185846 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
Starting backup at 17-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=60 STAMP=874609205
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_ANNNN_TAG20150317T190005_BJJC2XPC_.BKP tag=TAG20150317T190005 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
RMAN> EXIT
Recovery Manager complete.
D:\>sqlplus scott
Pasword:
Connected.
SQL> select * from tab;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
MURALI TABLE
TEST TABLE
SQL> drop table Murali purge;
Table dropped.
SQL> drop table test purge;
Table dropped.
SQL> drop table EMP purge;
SQL> drop table EMP purge;
Table dropped.
SQL> drop table DEPT purge;
Table dropped.
SQL> select * from tab;
SQL> drop table DEPT purge;
Table dropped.
SQL> select * from tab;
no rows selected
SQL> conn sys
Password:
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2176168 bytes
Variable Size 1073744728 bytes
Database Buffers 520093696 bytes
Redo Buffers 7397376 bytes
Database mounted.
SQL> EXIT
SQL> conn sys
Password:
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2176168 bytes
Variable Size 1073744728 bytes
Database Buffers 520093696 bytes
Redo Buffers 7397376 bytes
Database mounted.
SQL> EXIT
D:\app\Backup>rman target sys
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 17 19:04:48 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1380168819, not open)
RMAN> run
2> {
3> set until time "sysdate-1/24/60*5";
########### We rewind our database 5 mins before###########
#set until scn 10023;
#set until sequence 345 thread 2;
#set until restore point Murali_RESTORE;
#######Murali_RESTORE point already created##########
#####Any one of the above set line we can use###########
4> restore database;
5> recover database;
6> }
Recovery Manager11.2.0.1.0
RMAN> 2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 17-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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 D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP
channel ORA_DISK_1: piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP tag=TAG20150317T185846
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 17-MAR-15
Starting recover at 17-MAR-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-MAR-15
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>Exit
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 17 19:04:48 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1380168819, not open)
RMAN> run
2> {
3> set until time "sysdate-1/24/60*5";
########### We rewind our database 5 mins before###########
#set until scn 10023;
#set until sequence 345 thread 2;
#set until restore point Murali_RESTORE;
#######Murali_RESTORE point already created##########
#####Any one of the above set line we can use###########
4> restore database;
5> recover database;
6> }
Recovery Manager11.2.0.1.0
RMAN> 2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 17-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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 D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP
channel ORA_DISK_1: piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP tag=TAG20150317T185846
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 17-MAR-15
Starting recover at 17-MAR-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-MAR-15
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>Exit
D:\app\Backup>sqlplus scott
Password:
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
MURALI TABLE
TEST TABLE
SQL> select * from DEPT;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
MURALI TABLE
TEST TABLE
SQL> select * from DEPT;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
