RMAN Restore Table

From Robs_Wiki
Jump to: navigation, search

Introduction

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.

Example

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