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)
