RMAN Restore Table
In Oracle12cR1 RMAN was enhanced to allow selective restoration of one or more tables without having to restore the entire database. However, the table has to be restored with the same name to the same schema. Oracle12cR2 allows for a table to be restored under a different name and schema. You can also restore the table to a point in time. For example before rows were erraneously dropped.
RMAN> RECOVER TABLE robla.TBL_REC_TEST UNTIL SCN 11912102303339 AUXILIARY DESTINATION '/u02/app/oracle/oradata/TSTDB' REMAP TABLE 'ROBLA'.'TBL_REC_TEST':'TBL_REC_TEST2';
Remark: during the recoveyr RMAN creates an auxiliary instance and uses Datapump to export and import the table. The end of the logging reads as follows:
. . . . . . Performing import of tables... IMPDP> Master table "SYS"."TSPITR_IMP_abbb_aonm" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_abbb_aonm": IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA IMPDP> . . imported "ROBLA"."TBL_REC_TEST2" 8.280 MB 77979 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_abbb_aonm" successfully completed at Thu Mar 28 14:10:55 2019 elapsed 0 00:00:59 Import completed Removing automatic instance Automatic instance removed auxiliary instance file /u02/app/oracle/oradata/TSTDB/TSTDB/datafile/o1_mf_temp_g9so2593_.tmp deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/ABBB_PITR_TSTDB/onlinelog/o1_mf_3_g9sob206_.log deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/ABBB_PITR_TSTDB/onlinelog/o1_mf_2_g9sob0xf_.log deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/ABBB_PITR_TSTDB/onlinelog/o1_mf_1_g9so9zym_.log deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/ABBB_PITR_TSTDB/datafile/o1_mf_iops_g9so69rb_.dbf deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/TSTDB/datafile/o1_mf_sysaux_g9snzcgy_.dbf deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/TSTDB/datafile/o1_mf_undotbs1_g9snzcdp_.dbf deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/TSTDB/datafile/o1_mf_system_g9snzcfq_.dbf deleted auxiliary instance file /u02/app/oracle/oradata/TSTDB/TSTDB/controlfile/o1_mf_g9snz4cz_.ctl deleted auxiliary instance file tspitr_abbb_35484.dmp deleted Finished recover at 28-MAR-19
Clean up some artefacts that are left behind by the operation afterwards:
Afterwards we need to manually clean up some entries that are left behind during this operation: TSTDB:/u01/app/oracle/diag/rdbms>rm -rf abbb_pitr_tstdb