Database Point-In-Time Recovery

 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!!!