Ensure that the user that connects to the database with the Export Data Pump or Import Data Pump utility, is a privileged user (i.e. a user who has the DBA role or the EXP_FULL_DATABASE resp. IMP_FULL_DATABASE role), e.g.:
-- run this Data Pump job with TRACE as a privileged user:
% expdp <LOGIN_USER>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> TRACE=480300
-- or:
-- make the login user a privileged user:
CONNECT / AS SYSDBA
GRANT exp_full_database TO <LOGIN_USER>;
% expdp <LOGIN_USER>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log TABLES=<TABLE_NAME> TRACE=480300
The majority of errors that occur during a Data Pump job, can be diagnosed by creating a trace file for the Master Control Process (MCP) and the Worker Process(es) only, e.g.:
-- To run a Data Pump job with standard tracing:
-- This results in two trace files in BACKGROUND_DUMP_DEST:
-- Master Process trace file: <SID>_dm<number>_<process_id>.trc
-- Worker Process trace file: <SID>_dw<number>_<process_id>.trc
% expdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log SCHEMAS=<SCHEMA_NAME> TRACE=480300
Each Data Pump component can be specified explicitly in order to obtain tracing details of that component:
-- Summary of Data Pump trace levels:
-- ==================================
Trace DM DW ORA Lines
level trc trc trc in
(hex) file file file trace Purpose
------- ---- ---- ---- ------ -----------------------------------------------
10300 x x x SHDW: To trace the Shadow process (API) (expdp/impdp)
20300 x x x KUPV: To trace Fixed table
40300 x x x 'div' To trace Process services
80300 x KUPM: To trace Master Control Process (MCP) (DM)
100300 x x KUPF: To trace File Manager
200300 x x x KUPC: To trace Queue services
400300 x KUPW: To trace Worker process(es) (DW)
800300 x KUPD: To trace Data Package
1000300 x META: To trace Metadata Package
------- 'Bit AND'
1FF0300 x x x 'all' To trace all components (full tracing)
There is very useful note Note:351598.1 "Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)"
Combinations of tracing Data Pump components are possible, e.g.:
-- Example of combination (last 4 digits are usually 0300):
40300 to trace Process services
80300 to trace Master Control Process (MCP)
400300 to trace Worker process(es)
------- 'Bit AND'
4C0300 to trace Process services and Master Control and Worker processes
In order to trace all Data Pump components, level 1FF0300 can be specified:
-- Run a Data Pump job with full tracing:
-- This results in two trace files in BACKGROUND_DUMP_DEST:
-- Master Process trace file: <SID>_dm<number>_<process_id>.trc
-- Worker Process trace file: <SID>_dw<number>_<process_id>.trc
-- And one trace file in USER_DUMP_DEST:
-- Shadow Process trace file: <SID>_ora_<process_id>.trc
% impdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log FULL=y TRACE=1ff0300
Also ensure that the init.ora/spfile initialization parameter MAX_DUMP_FILE_SIZE is large enough to capture all the trace information (or set it to unlimited which is the default value), e.g.:
-- Ensure enough trace data can be written to the trace files:
CONNECT / as sysdba
SHOW PARAMETER max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 10M
ALTER SYSTEM SET max_dump_file_size = unlimited SCOPE = both;
SHOW PARAMETER max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED
Special thanks to Mike Dietrich for sharing this doc id in his session :
