STEP 1: Note the current database clock time
SQL> select
to_char(sysdate,'DD-MON-YY:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-MON-
------------------------
20-FEB-15:17:45:55
STEP 2: Drop a table
We will now drop the table MYOBJECTS and then
perform an RMAN point-in-time recovery to restore the database to a point in
time just before the table was dropped. Note the number of rows in the table.
SQL> select
count(*) from myobjects;
COUNT(*)
----------
13420
SQL> drop
table myobjects;
Table dropped.
STEP 3: Shutdown and then mount the database in
preparation for restore and recovery.
SQL> conn /
as sysdba
Connected.
SQL> shutdown abort
ORACLE
instance shut down.
SQL> startup
mount;
[oracle@NVMBD1BZY150D00
CLONEDB]$ rman target / catalog rman/rman@rmanp
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Feb 20 17:49:27 2015
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: CLONEDB (DBID=958582256, not open)
connected
to recovery catalog database
Note – the incremental backup since available
is restored before applying the archive log files. The archive log files that
will be applied are those that have been generated after the Level 1
incremental backup.
Since we are restoring the database to a point
in the past, we will have to open the database with the RESETLOGS option.
RMAN> run {
2> set
until time "to_date('20-FEB-15:17:45:55','dd-MON-yy hh24:mi:ss')";
3> restore
database;
4> recover
database;
5> }
executing
command: SET until clause
Starting
restore at 20-FEB-15
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=176 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
/data/oracle/app/oracle/oradata/CLONEDB/system.dbf
channel
ORA_DISK_1: restoring datafile 00002 to
/data/oracle/app/oracle/oradata/CLONEDB/user04.dbf
channel
ORA_DISK_1: restoring datafile 00003 to
/data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf
channel
ORA_DISK_1: restoring datafile 00004 to
/data/oracle/app/oracle/oradata/CLONEDB/undo.dbf
channel
ORA_DISK_1: restoring datafile 00005 to
/data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf
channel
ORA_DISK_1: restoring datafile 00006 to /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf
channel
ORA_DISK_1: restoring datafile 00007 to
/data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf
channel
ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.38pvodgm_1_1
channel
ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.38pvodgm_1_1 tag=LEVEL 0
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished
restore at 20-FEB-15
Starting
recover at 20-FEB-15
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting incremental datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00001:
/data/oracle/app/oracle/oradata/CLONEDB/system.dbf
destination
for restore of datafile 00002:
/data/oracle/app/oracle/oradata/CLONEDB/user04.dbf
destination
for restore of datafile 00003:
/data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf
destination
for restore of datafile 00004: /data/oracle/app/oracle/oradata/CLONEDB/undo.dbf
destination
for restore of datafile 00005:
/data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf
destination
for restore of datafile 00006:
/data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf
destination
for restore of datafile 00007: /data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf
channel
ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.3cpvog7a_1_1
channel
ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.3cpvog7a_1_1 tag=LEVEL 1
BACKUP
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting
media recovery
archived
log for thread 1 with sequence 6 is already on disk as file
/data1/clonedb/archive/1_6_872166459.dbf
archived
log for thread 1 with sequence 7 is already on disk as file
/data1/clonedb/archive/1_7_872166459.dbf
archived
log for thread 1 with sequence 8 is already on disk as file
/data1/clonedb/archive/1_8_872166459.dbf
archived
log for thread 1 with sequence 9 is already on disk as file
/data1/clonedb/archive/1_9_872166459.dbf
archived
log for thread 1 with sequence 10 is already on disk as file
/data1/clonedb/archive/1_10_872166459.dbf
archived
log for thread 1 with sequence 11 is already on disk as file
/data1/clonedb/archive/1_11_872166459.dbf
archived
log for thread 1 with sequence 12 is already on disk as file
/data1/clonedb/archive/1_12_872166459.dbf
archived
log file name=/data1/clonedb/archive/1_6_872166459.dbf thread=1 sequence=6
archived
log file name=/data1/clonedb/archive/1_7_872166459.dbf thread=1 sequence=7
archived
log file name=/data1/clonedb/archive/1_8_872166459.dbf thread=1 sequence=8
archived
log file name=/data1/clonedb/archive/1_9_872166459.dbf thread=1 sequence=9
archived
log file name=/data1/clonedb/archive/1_10_872166459.dbf thread=1 sequence=10
media
recovery complete, elapsed time: 00:00:01
Finished
recover at 20-FEB-15
STEP 4: Start the database with resetlogs
option.
RMAN> sql
'alter database open resetlogs';
sql
statement: alter database open resetlogs
STEP 5: Connect to database and confirm that
table is present.
We will now connect to the database to confirm
that our table which we had earlier dropped has been restored as well as the
record count in the table matches that taken before the table drop.
[oracle@NVMBD1BZY150D00
CLONEDB]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Feb 20 17:59:52 2015
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select
count(*) from myobjects;
COUNT(*)
----------
13420
STEP 6: Reset the database in recovery catalog.
Because we have done a RESETLOGS we need to
reset the database in the recovery catalog.
[oracle@NVMBD1BZY150D00
CLONEDB]$ rman target / catalog rman/rman@rmanp
RMAN> reset
database;
new
incarnation of database registered in recovery catalog
starting
full resync of recovery catalog
full
resync complete
STEP 7: Standard Practice take full fresh backup of database.
We will now take a fresh level 0 incremental
backup
RMAN> backup
incremental level 0 database;
I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!