Database Point In Time Recovery.
DBPITR works at the physical level to return the datafiles to their state at a target time in the past. In an RMAN DBPITR operation, we specify a target SCN, log sequence, restore point, or time. RMAN restores the database from backups created before the target time, and then applies incremental backups and logs to re-create all changes between the time of the datafile backups and the end point of recovery. When the end point is specified as an SCN, the database applies the redo logs and stops at the end of each redo thread or the specified SCN, whichever occurs first. When the end point is specified as a time, the database internally determines a suitable SCN for the specified time and then recovers to this SCN. When we perform DBPITR the database outage is expected and any recent updates will back out which have made after the restore time.
Lets simulate the DBPITR.
I've created a user 'rohit' whose default tablespace is USER. I created a table and inserted records into that and took a database backup on dated 24. Again I inserted some more data into the tables under schema rohit and scott on dated 25. Now I'll delete the tablespace TEST and recover it. I'll also check whether there would be any data loss due to the DBPITR.
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test; <<-- Will restore till here though older controlfile
COUNT(*)
----------
14014
SQL> insert into test
2 select * from test;
14014 rows created.
SQL> /
SQL> select count(*) from test;
COUNT(*)
----------
42042
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test; <<-- Will restore till here through older controlfile
COUNT(*)
----------
5600
SQL> insert into test
2 select * from test;
5600 rows created.
SQL> select count(*) from test;
COUNT(*)
----------
11200
SQL> conn rohit/rohit
Connected.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
11200
SQL> host rman target /
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
3 300 TEST *** /u01/app/oracle/oradata/test01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
RMAN>
SQL> conn /as sysdba
Connected.
SQL> drop tablespace TEST including contents and datafiles;
Tablespace dropped.
Excerpts from alert log.
Tue Jun 25 18:03:04 2013
Starting background process SMCO
Tue Jun 25 18:03:04 2013
SMCO started with pid=23, OS id=5450
Tue Jun 25 18:22:01 2013
drop tablespace TEST including contents and datafiles
ORA-1031 signalled during: drop tablespace TEST including contents and datafiles...
Tue Jun 25 18:22:19 2013
drop tablespace TEST including contents and datafiles
Deleted file /u01/app/oracle/oradata/test01.dbf
Completed: drop tablespace TEST including contents and datafiles
Tue Jun 25 18:28:08 2013
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
RMAN> restore tablespace 'TEST';
Starting restore at 25-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/25/2013 18:23:23
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TEST"
[oracle@oracle ~]$ cd /u01/app/oracle/fast_recovery_area/DB1/autobackup/
[oracle@oracle autobackup]$ ls -al
drwxr-x--- 2 oracle oinstall 4096 Jun 24 11:53 2013_06_20
drwxr-x--- 2 oracle oinstall 4096 Jun 24 11:53 2013_06_21
drwxr-x--- 2 oracle oinstall 4096 Jun 25 16:56 2013_06_24
drwxr-x--- 2 oracle oinstall 4096 Jun 25 18:01 2013_06_25
[oracle@oracle autobackup]$ cd 2013_06_24
[oracle@oracle 2013_06_24]$ ls
o1_mf_s_818942034_8whshwx7_.bkp o1_mf_s_818949173_8wj0gz3p_.bkp
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 1291847836 bytes
Database Buffers 385875968 bytes
Redo Buffers 11636736 bytes
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818949173_8wj0gz3p_.bkp';
Starting restore at 25-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/db1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/db1/control02.ctl
Finished restore at 25-JUN-13
RMAN> sql'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> run
2> {
3> set until time "to_date('06-25-2013:18:03:04','MM-DD-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 25-JUN-13
Starting implicit crosscheck backup at 25-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 25-JUN-13
Starting implicit crosscheck copy at 25-JUN-13
using channel ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck copy at 25-JUN-13
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_25/o1_mf_s_819044828_8wlxw607_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_25/o1_mf_s_819050469_8wm3dfth_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818942034_8whshwx7_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818949173_8wj0gz3p_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_21/o1_mf_s_818710579_8w8qgx53_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wj2cx20_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wj2cx4p_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162706_8wlxw2t3_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrk5f_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrffs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrgoy_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrljs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrhws_.bkp
using channel ORA_DISK_1
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 /u01/app/oracle/oradata/db1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/db1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/db1/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp tag=TAG20130625T162529
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
Finished restore at 25-JUN-13
Starting recover at 25-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/oradata/db1/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc thread=1 sequence=11
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc thread=1 sequence=12
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc thread=1 sequence=14
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc thread=1 sequence=15
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc thread=1 sequence=16
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc thread=1 sequence=20
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc thread=1 sequence=21
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/oradata/db1/redo02.log thread=1 sequence=23
media recovery complete, elapsed time: 00:00:40
Finished recover at 25-JUN-13
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
3 300 TEST *** /u01/app/oracle/oradata/test01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
5600
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
14014
So here we can see that the changes which had occurred after the restore time is back out.
DBPITR works at the physical level to return the datafiles to their state at a target time in the past. In an RMAN DBPITR operation, we specify a target SCN, log sequence, restore point, or time. RMAN restores the database from backups created before the target time, and then applies incremental backups and logs to re-create all changes between the time of the datafile backups and the end point of recovery. When the end point is specified as an SCN, the database applies the redo logs and stops at the end of each redo thread or the specified SCN, whichever occurs first. When the end point is specified as a time, the database internally determines a suitable SCN for the specified time and then recovers to this SCN. When we perform DBPITR the database outage is expected and any recent updates will back out which have made after the restore time.
Lets simulate the DBPITR.
I've created a user 'rohit' whose default tablespace is USER. I created a table and inserted records into that and took a database backup on dated 24. Again I inserted some more data into the tables under schema rohit and scott on dated 25. Now I'll delete the tablespace TEST and recover it. I'll also check whether there would be any data loss due to the DBPITR.
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test; <<-- Will restore till here though older controlfile
COUNT(*)
----------
14014
SQL> insert into test
2 select * from test;
14014 rows created.
SQL> /
SQL> select count(*) from test;
COUNT(*)
----------
42042
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test; <<-- Will restore till here through older controlfile
COUNT(*)
----------
5600
SQL> insert into test
2 select * from test;
5600 rows created.
SQL> select count(*) from test;
COUNT(*)
----------
11200
SQL> conn rohit/rohit
Connected.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
11200
SQL> host rman target /
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
3 300 TEST *** /u01/app/oracle/oradata/test01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
RMAN>
SQL> conn /as sysdba
Connected.
SQL> drop tablespace TEST including contents and datafiles;
Tablespace dropped.
Excerpts from alert log.
Tue Jun 25 18:03:04 2013
Starting background process SMCO
Tue Jun 25 18:03:04 2013
SMCO started with pid=23, OS id=5450
Tue Jun 25 18:22:01 2013
drop tablespace TEST including contents and datafiles
ORA-1031 signalled during: drop tablespace TEST including contents and datafiles...
Tue Jun 25 18:22:19 2013
drop tablespace TEST including contents and datafiles
Deleted file /u01/app/oracle/oradata/test01.dbf
Completed: drop tablespace TEST including contents and datafiles
Tue Jun 25 18:28:08 2013
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
RMAN> restore tablespace 'TEST';
Starting restore at 25-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/25/2013 18:23:23
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TEST"
[oracle@oracle ~]$ cd /u01/app/oracle/fast_recovery_area/DB1/autobackup/
[oracle@oracle autobackup]$ ls -al
drwxr-x--- 2 oracle oinstall 4096 Jun 24 11:53 2013_06_20
drwxr-x--- 2 oracle oinstall 4096 Jun 24 11:53 2013_06_21
drwxr-x--- 2 oracle oinstall 4096 Jun 25 16:56 2013_06_24
drwxr-x--- 2 oracle oinstall 4096 Jun 25 18:01 2013_06_25
[oracle@oracle autobackup]$ cd 2013_06_24
[oracle@oracle 2013_06_24]$ ls
o1_mf_s_818942034_8whshwx7_.bkp o1_mf_s_818949173_8wj0gz3p_.bkp
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 1291847836 bytes
Database Buffers 385875968 bytes
Redo Buffers 11636736 bytes
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818949173_8wj0gz3p_.bkp';
Starting restore at 25-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/db1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/db1/control02.ctl
Finished restore at 25-JUN-13
RMAN> sql'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> run
2> {
3> set until time "to_date('06-25-2013:18:03:04','MM-DD-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 25-JUN-13
Starting implicit crosscheck backup at 25-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 25-JUN-13
Starting implicit crosscheck copy at 25-JUN-13
using channel ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck copy at 25-JUN-13
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_25/o1_mf_s_819044828_8wlxw607_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_25/o1_mf_s_819050469_8wm3dfth_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818942034_8whshwx7_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_24/o1_mf_s_818949173_8wj0gz3p_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_21/o1_mf_s_818710579_8w8qgx53_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wj2cx20_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wj2cx4p_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162706_8wlxw2t3_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrk5f_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrffs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrgoy_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrljs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_annnn_TAG20130625T162509_8wlxrhws_.bkp
using channel ORA_DISK_1
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 /u01/app/oracle/oradata/db1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/db1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/db1/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T162529_8wlxs29y_.bkp tag=TAG20130625T162529
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
Finished restore at 25-JUN-13
Starting recover at 25-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/oradata/db1/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wj2cvyj_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_1_8wj2fqg1_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_2_8wj2fsgp_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_3_8wjbn908_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_4_8wjbnc4n_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_5_8wjbr53m_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_6_8wjv67l2_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_24/o1_mf_1_7_8wjx2hcf_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_8_8wk5t9px_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_9_8wkp5vbk_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_10_8wkqgz7t_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_11_8wl3pc2d_.arc thread=1 sequence=11
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_12_8wlmqok6_.arc thread=1 sequence=12
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_13_8wlxdhmo_.arc thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_14_8wlxdm5n_.arc thread=1 sequence=14
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_15_8wlxl96c_.arc thread=1 sequence=15
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_16_8wlxn5t2_.arc thread=1 sequence=16
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_17_8wlxrf04_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_18_8wlxw1r5_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_19_8wm2gs16_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_20_8wm2gw2m_.arc thread=1 sequence=20
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_21_8wm2x6nx_.arc thread=1 sequence=21
archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_25/o1_mf_1_22_8wm2x9rf_.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/oradata/db1/redo02.log thread=1 sequence=23
media recovery complete, elapsed time: 00:00:40
Finished recover at 25-JUN-13
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle/oradata/db1/system01.dbf
2 900 SYSAUX *** /u01/app/oracle/oradata/db1/sysaux01.dbf
3 300 TEST *** /u01/app/oracle/oradata/test01.dbf
4 140 USERS *** /u01/app/oracle/oradata/db1/users01.dbf
5 446 UNDOTBS2 *** /u01/app/oracle/oradata/db1/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1 TEMP1 1 /u01/app/oracle/oradata/db1/temp02.dbf
2 500 TEMP1 500 /u01/app/oracle/oradata/db1/temp03.dbf
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
5600
SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
14014
So here we can see that the changes which had occurred after the restore time is back out.
No comments:
Post a Comment