Recovering Table, Part 4 - Note

There are two additional pieces of information I want to give you.

1. The directories created, in order to restore the auxiliary database
2. The configuration of rman script

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 directories that rman use for the "core" files

Into directory specified by "auxiliary destination" parameter, rman will create a directory named as the same name of the production database. In my case it was PCMSP. Here will be restored the "core files"

Under this directory, there will be 3 directory:

• one for controlfile (/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/controlfile/)
• one for datafile (/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/datafile/)
• one for redlog online (/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DGAF_PITR_PCMSP/onlinelog/)

The archived log, will be restored under PCMSP directory (the same level fo controlfile/datafile/onlinelog directory)

The list of "core" datafile. The archived logs are not shown, here.

• The directories that rman use for the "application" files

At the same level of the directory where rman will restore the core files (auxiliary destination=/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp) will be created a new directory (DSEK_PITR_PCMSP in my case) where the "application" datafiles will go.

core files => /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/PCMSP/

application files => /FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp/DSEK_PITR_PCMSP/

where

• DSEK is a prefix put by rman
• PITR is for Point In Time to Recovery
• PCMSP is the name fo database

The "core" directory (PCMS) and "application" directory (DESK_PITR_PCMSP), are created at the same level. Also the archived log will be restored to the same level. Here are shown the archived log needed for the recovery of "application" tablespace

Summarizing what I said, the tree of directy is:

<auxiliary destination>
     |
     -> archived log for "core" part
     -> archived log for "application" part
     -<db_name>
            |
            -> controlfile dir
                  |
                  -> controlfile
            -> onlinelog dir
                  |
                  -> redolog file online
            -> datafile dir
                  |
                  -> SYSTEM tbs
                  -> SYSAUX tbs
                  -> TEMP tbs
                  -> all UNDO tbs
     -<pitr_db_name>
            |
            -> datafile for "application" part

• Channel configuration

Another important thing to show is the script I used. How you can see, I'm not using the allocation of a channel. In my case, the backup is on tapes. If you try to allocate a channel, like this (I use Legato Networker. This is why all env parameters starts with "NSR_")

run {
2> ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE'
PARMS 'ENV=(NSR_SERVER=bck_server, NSR_CLIENT=bck_client, NSR_GROUP=db_group, NSR_DATA_VOLUME_POOL=db_pool_1M)'; 
3>
4> recover table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE
5> until time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')"
6> auxiliary destination '/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp'
7> remap table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE:PIANIFICAZIONE_MAN_BCK;
8> }


then you get following error:

RMAN06034: at least 1 channel must be allocated to execute this command

The cause and solution are explained in this metalink note: "Recover Table Fails with RMAN06034:
at least 1 channel must be allocated to execute this command (Doc ID 2105325.1)":


You cannot manually allocate a channel when RMAN will implicitly create the auxiliary instance, the channel specification must be configured in the target database. This is done with:

configure channel sbt_tape FORMAT '%U' PARMS='ENV=(NB_ORA_CLIENT=......, NB_ORA_POLICY=............., NB_ORA_SCHED=.....)';


So, I have to connect to the target and to the catalog/controlfile and run the "configure channel" command:

$rman
RMAN> connect target /
RMAN> configure channel sbt_tape FORMAT '%U' PARMS='ENV=(NLS_CLIENT=......, NLS_SERVER=.....)';

Using Legato Networker, the only two parameters that you have to use for the restore are NSR_CLIENT and NSR_SERVER. The tape number is saved into catalog/controlfile.

• Other recovery scenarios

As told in Part 1, there are several cases to recover table. I report two example from the manual pages.

The first one. The recover generate just a dump file. The import will not be done.

RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
    UNTIL TIME 'SYSDATE-1'
    AUXILIARY DESTINATION '/tmp/oracle/recover'
    DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
    DUMP FILE 'emp_dept_exp_dump.dat'
    NOTABLEIMPORT;

The second one. For the PITR, the recover will use the UNTIL SEQUENCE and it'll remap also the tablespace.

RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999
    UNTIL SEQUENCE 354
    AUXILIARY DESTINATION '/tmp/oracle/recover'
    REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',
              'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999' 
    REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';



Recovering Table, Part 1 - Introduction
Recovering Table, Part 2 - The "Core" part
Recovering Table, Part 3 - The "Application" part