Datapump between different schemas and different tables in 11g.

Hello everyone.

Feature - Expdp/impdp

DB - 11gr2 (11.2.0.2)

Non-ASM. Non-RAC

OS - AIX, 6.1.8.15

Scenario

Task is to export few tables from Production and import it into Test schema with new names. 

Tables in Production to be Exported

SEND_ASR_MESSAGE

SEND_ASR_CFA

SEND_ASR_SECLOC

RECEIVE_ASR_MESSAGE

RECEIVE_ASR_CFA

RECEIVE_ASR_SECLOC

Schema name - CHGTWY_PROD

Tables to be imported as below names

SEND_ASR_MESSAGE_PROD

SEND_ASR_CFA_PROD

SEND_ASR_SECLOC_PROD

RECEIVE_ASR_MESSAGE_PROD

RECEIVE_ASR_CFA_PROD

RECEIVE_ASR_SECLOC_PROD

Schema name - CHBAGTWY_PERF

Challenge

- Need to use both remap_schema and remap_table. 

- Many bugs have been reported for remap_table in 11g version. 

Step 1 - Create necessary directories and grants. 

CREATE OR REPLACE DIRECTORY DATA_EXP AS '/oraback/EXP';

grant read,write on directory DATA_EXP to system; (since I am using system user). 

Step 2 - Create par file for expdp

vi export_table_backup.par 

USERID=system/L00k_fwd directory=DATA_EXP dumpfile=expdp_REQ0070510_tables_9_3_2014.dmp logfile=expdp_REQ0070510_tables_9_3_2014.log tables=CHBAGTWY_PERF.SEND_ASR_MESSAGE,CHBAGTWY_PERF.SEND_ASR_CFA,CHBAGTWY_PERF.SEND_ASR_SECLOC,CHBAGTWY_PERF.RECEIVE_ASR_MESSAGE,CHBAGTWY_PERF.RECEIVE_ASR_CFA,CHBAGTWY_PERF.RECEIVE_ASR_SECLOC

Step 3 - Execute in nohup. (nohup will make sure your export is completed, although if we exit from the shell)

nohup expdp parfile=export_table_backup.par > nohup_expdp.out &

Step 4 - Copy the dump files to remote server.  

Step 5 - Create par file for impdp .

Vi import_tables.par

USERID=system/L00k_fwd directory=DATA_EXP dumpfile=expdp_REQ0070510_tables_9_3_2014.dmp logfile=impdp_REQ0070510_tables_9_3_2014.log  REMAP_SCHEMA=CHGTWY_PROD:CHBAGTWY_PERF REMAP_TABLE=SEND_ASR_MESSAGE:SEND_ASR_MESSAGE_PROD,SEND_ASR_CFA:SEND_ASR_CFA_PROD,SEND_ASR_SECLOC:SEND_ASR_SECLOC_PROD,RECEIVE_ASR_MESSAGE:RECEIVE_ASR_MESSAGE_PROD,RECEIVE_ASR_CFA:RECEIVE_ASR_CFA_PROD,RECEIVE_ASR_SECLOC:RECEIVE_ASR_SECLOC_PROD

Note - Please note that in remap_table you should not use OWNER.T1:T2, as this will be considered as partition and not table. 

Step 6 - Execute in nohup. 

nohup impdp parfile=import_tables.par > nohup.out &

At this point you will get some very specific errors. 

ORA-31684: Object type TRIGGER:"CHBAGTWY_PERF"."SEND_ASR_MSG_CHG_Q" already exists

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS []

ORA-31604: invalid NAME parameter "MODIFY" for object type PROCDEPOBJ in function ADD_TRANSFORM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPW$WORKER", line 8353

This is reported as a bug - Bug 16551094. 

So the workaround is to exclude a parameter. Please refer below DOC ids for the same. 

ORA-39151 Reported By DataPump Import When Using REMAP_TABLE Option (Doc ID 886762.1)

Impdp fails with error ORA-39126 ORA-31604 invalid transform NAME parameter "MODIFY" for object type PROCACT_INSTANCE in function ADD_TRANSFORM (Doc ID 1596495.1)