Recovering Table, Part 2 . The "Core" 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

The log of the recover is here.

• Restore the Core part

During this step, RMAN will restore an auxiliary database with following components:

• SYSTEM tablespace
• SYSAUX tablespace
• UNDO tablespaceze
• TEMP tablespace
• Archived log

Regardless of whether the database is RAC or not,the auxiliary database will be a single instance. In my case, the database (RAC) name is "PCMSp" and the 2 instances are "PCMSp1" and "PCMSp2". The auxiliary database will have these parameters

SID='DgAf'

db_name=PCMSP

db_unique_name=DgAf_pitr_PCMSP

(these parameters will be chosen automatically by rman). Also, will be restored the UNDO tablespaces of all instances (in my case, 2 instances mean 2 UNDO tablespaces). Transparently rman will run following scripts:

################

Restore the controlfile

################

{

# set requested point in time

set until  time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

}

###################

Restore the "core" datafile

###################

{

# 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 clone datafile  1 to new;

set newname for clone datafile  2 to new;

set newname for clone datafile  5 to new;

set newname for clone datafile  6 to new;

set newname for clone datafile  7 to new;

set newname for clone datafile  8 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  4 to new;

set newname for clone tempfile  1 to new;

set newname for clone tempfile  2 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 2, 5, 6, 7, 8, 3, 4;

switch clone datafile all;

}

Just to be clear

00001 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_system_%u_.dbf

00002 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_system_%u_.dbf

00003 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_sysaux_%u_.dbf

00004 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_sysaux_%u_.dbf

00005 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs1_%u_.dbf

00006 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs1_%u_.dbf

00007 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs2_%u_.dbf

00008 to /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/datafile/o1_mf_undotbs2_%u_.dbf

#############

Recover database

#############

{

# 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  1 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  6 online";

sql clone "alter database datafile  7 online";

sql clone "alter database datafile  8 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  4 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX";

sql clone 'alter database open read only';

}

During this step, the archived logs will be restored. After the archived logs are applied, the “DgAf” (the auxiliary) database can be opened and the restore of the "application" part can  start.

####################

Start of Auxiliary database

####################

sql statement: alter database open read only

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files = 

  ''/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/controlfile/o1_mf_djpc35l1_.ctl'' comment=

''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

Recovering Table, Part 1 - Introduction

Recovering Table, Part 3 - The "Application" part
Recovering Table, Part 4 - Note