Recovering Table, Part 3 - The "Application" part

The script I used to recover the table is:

run {
2> recover table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE
3> until time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')"
4> auxiliary destination '/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp'
5> remap table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE:PIANIFICAZIONE_MAN_BCK;
6> }

The recovery table phase is split into 2 logical steps:

1. Restore the core part
2. Restore the application part

• Restore the "Application" part

During this step, RMAN will restore:

• Tablespaces where the table is located
• Archived log

Transparently rman will run following scripts:

##########################
Restore the "application" tablespace
##########################

In my case, there is just a tablespace

{
# set requested point in time
set until  time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  13 to new;
set newname for datafile  14 to new;
set newname for datafile  17 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  13, 14, 17;

switch clone datafile all;
}

where

00013 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_%u_.dbf
00014 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_%u_.dbf
00017 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_%u_.dbf


###########################
Recover the "application" tablespace
###########################

{
# set requested point in time
set until  time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  13 online";
sql clone "alter database datafile  14 online";
sql clone "alter database datafile  17 online";
# recover and open resetlogs
recover clone database tablespace  "PCMS_DATA", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}

How you can see into log file, also in this case, it is necessary to restore the Archived Log.

##########################
Creation of expdp/impdp directory
##########################

{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp''";
}

#############
expdp of the table
#############

After the Database recovery, it start the table export...:

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_DgAf_BngC":  
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 7 MB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "PCMS_ADM"."PIANIFICAZIONE_MANUTENZIONE"    5.110 MB   34394 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_DgAf_BngC" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_DgAf_BngC is:
   EXPDP>   /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/tspitr_DgAf_81953.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_DgAf_BngC" successfully completed at Thu May 4 15:51:03 2017 elapsed 0 00:00:29
Export completed


{
# shutdown clone before import
shutdown clone abort
}

###########
impdp of table
###########

...and the table import:

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_DgAf_gvec" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_DgAf_gvec":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "PCMS_ADM"."PIANIFICAZIONE_MAN_BCK"         5.110 MB   34394 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_DgAf_gvec" successfully completed at Thu May 4 15:51:14 2017 elapsed 0 00:00:04
Import completed

How you can see, after the expdp command is completed, because the auxilyary database it's not necessary anymore. it will be closed,


#########
The last step
#########

After importing the table, all restored files ("core" filse, "application" files and archived logs), will be deleted.

auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_temp_djpckr57_.tmp deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_temp_djpckr70_.tmp deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_8_djpddy16_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_6_djpddo9z_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_4_djpdddof_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_2_djpdd443_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_7_djpddsr2_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_5_djpddk23_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_3_djpdd893_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/o1_mf_1_djpdczsp_.log deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_djpcndlt_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_djpd013l_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/o1_mf_pcms_dat_djpcop6k_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_sysaux_djpcc7lb_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_sysaux_djpc4pyw_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs2_djpcbo4t_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs2_djpc9bnh_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs1_djpc8t8l_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs1_djpc7rts_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_system_djpc79bp_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_system_djpc65fy_.dbf deleted
auxiliary instance file /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/controlfile/o1_mf_djpc35l1_.ctl deleted
auxiliary instance file tspitr_DgAf_81953.dmp deleted

Recovering Table, Part 1 - The "Core" part
Recovering Table, Part 2 - The "Application" part
Recovering Table, Part 4 - Note