- The directories created, in order to restore the auxiliary database
- 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
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 list of "core" datafile. The archived logs are not shown, here. |
- The directories that rman use for the "application" files
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 |
|
-> 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
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
