Table point in-time recovery using RMAN

Introduction
In this post I'd like to present a new 12c feature - RMAN table point in time recovery.
This feature allows to perform a recovery for a specific table or table partition via RMAN, and it can be very useful in the following cases :

• The table was truncated - so you can't use the 10g Flashback Table feature as the undo is not available
• The table was dropped with the purge clause -so you can't use the 10g Flashback Drop feature
• Table was logically corrupted and no undo is available - so you can't use the 10g Flashback Table feature
• Table was logically corrupted and a DDL operation modified the structure of the table - so you can't use the 10g Flashback Table feature in order to flashback the table prior to the DDL

In order to overcome this scenarios before Oracle 12c you could recover the entire database to a point in time recovery - however, you will also recover all the other objects to the same point in time which will result in a data loss.

A better option would be to perform a tablespace point-in-time-recovery (only available for the Enterprise Edition) which means that you can recovery the entire tablespace of that table to an auxiliary instance and then manually export and import the table to the target database.

The new feature in Oracle 12c performs all this automatically.

Prerequisites

In order to use this feaute you must have a backup of the tablespace/s for the selected table/tables because during the recover process Oracle will automatically perform point in time recovery using those tablespaces and then it will export the table/s from the auxiliary (temporary) instance and then it will import the data into the target (source) instance.

Demonstration:

In this demonstration I'm connected with user "pini", creating a table "emp" and inserts 1 row and then commit it:

SQL> show user;
USER is "PINI"
SQL> create table emp (id number, name varchar2(20));
Table created.
SQL> insert into emp values (1, 'DAVID');
1 row created.
SQL> commit;
Commit complete.

Now, I check what's the current SCN so I can use it later in the RMAN RECOVER command (RMAN supports point-in-time-recovery until SCN or TIME or SEQUENCE)

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
   25427549

Now I will drop it with the "purge" option (so the flashback drop feature is irrelevant):

SQL> drop table emp purge;
Table dropped.

The next step would be to connect to RMAN and execute the RECOVER TABLE command.

You should also specify the auxiliary destination which is the location of the auxiliary database files and also the Data Pump Export Dump File:

RMAN> recover table pini.emp until scn 25427549 auxiliary destination '/oravl01/recover';

Starting recover at 07-SEP-15
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Fkyk'

initialization parameters used for automatic instance:
db_name=O12102NP
db_unique_name=Fkyk_pitr_O12102NP
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oravl01/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=/oravl01/recover
log_archive_dest_1='location=/oravl01/recover'
#No auxiliary parameter file used
...
...
...
...
Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_Fkyk_kvas" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Fkyk_kvas":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "PINI"."EMP"                                5.476 KB       1 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_Fkyk_kvas" successfully completed at Mon Sep 7 14:13:05 2015 elapsed 0 00:00:11
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_temp_bytw2co8_.tmp deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_3_bytw4md4_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_2_bytw4m1t_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_1_bytw4lhq_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/datafile/o1_mf_users_bytw4gos_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_sysaux_bytvy598_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_undotbs1_bytvy590_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_system_bytvy59h_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/controlfile/o1_mf_bytvxw4w_.ctl deleted
auxiliary instance file tspitr_Fkyk_10996.dmp deleted
Finished recover at 07-SEP-15

That's it!
The EMP table is now available in the source instance with the data which is relevant to the selected time period (in my case to SCN 25427549):

SQL> select * from emp;
        ID NAME
---------- --------------------
         1 DAVID

Few Notes:

• You can specify a different location for the dump file using the DATAPUMP DESTINATION clause
• During the last step of this process, i.e. during the import step, you may choose to import the table to a new table name and not to its original name using the REMAP TABLE clause
• During the last step of this process, i.e. during the import step, you may choose to import the table to a new tablespace and not to its original tablespace using the REMAP TABLESPACE clause
• If you prefer to have a dump file point in time export backup without actually importing it backup to the target (source) database, use the NOTABLEIMPORT clause

Conclusions

• This feature requires minimum work and effort from the DBA
• Due to the fact that as part of the recovery process RMAN needs to restore an auxiliary instance, and also export and import the relevant table/s, this whole process has an overhead (mainly in terms of I/O load), therefore If you can use other options for recovering the table (such as Flashback Table/Flashback Drop) you should use them and avoid using this feature. Also, table-recovery using this feature takes much longer than using the Flashback drop/Flashback table
• When you can't use Flashback drop/Flashback table to recover the table, you may find this feature very useful.
• This feature is available with the Enterprise Edition only