Recovering a table from RMAN Backup in Oracle 12C database

In Oracle 12C new functionality is added that we can restore an individual table from RMAN Backup. In below example we will import a table farhat_log from a dump file, take rman backup of database and then drop the table we want to restore.

imp ‘/ as sysdba’ fromuser=farhat touser=farhat file=’d:\temp\farhat_log.dmp’ log=d:\temp\farhat_log.log’ buffer=20971520

SQL> select table_name,num_rows from dba_tables where owner =’FARHAT’;

TABLE_NAME NUM_ROWS
———— ———-
FARHAT_LOG 1134527
 

rman target /

RMAN> backup as compressed backupset database;

SQL> select current_scn from v$database;

CURRENT_SCN
———–

2874383

SQL> drop table farhat.farhat_log cascade constraints;

Table dropped

RMAN> recover table farhat.bas until scn 2874383 auxiliary destination ‘d:\temp’;

This will first create an auxiliary instance and restore db to directory ‘D:\temp’ and restore database, extract data pump dump from of table from auxiliary database and then drop auxiliary instance and recover table from datapump file to primary db .

SQL> select table_name,num_rows from dba_tables where owner =’FARHAT’;

TABLE_NAME NUM_ROWS
———— ———-
FARHAT_LOG 1134527