How to Diagnose Oracle Data Pump issues - Tracing session

Tracing datapump is not straight forward, you can trace the datapump worker(dw00) or datapump master(DM00) with 10046 to get the sql etc or use the TRACE option in expdp/impdp

Where in expdp has an option called trace where you can trace the session associated to datapump and then see what its waiting for or running (internal sql statements)

There are number of bugs for datapump, tracing may help to determine what we are encountering

To begin with, we need to know different levels of tracing in datapump, the following table depicts the same.

Trace types and Trace events for Datapump:

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)

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 <UserName>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \ 

LOGFILE=<LOG_NAME>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> TRACE=480300 

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:

SQL> CONNECT / as sysdba

SQL> SHOW PARAMETER max_dump

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size                   string      10M

SQL> ALTER SYSTEM SET max_dump_file_size = unlimited SCOPE = both; 

SQL> SHOW PARAMETER max_dump 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size                   string      UNLIMITED

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 <UserName>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \ 

LOGFILE=<LOG_NAME>.log SCHEMAS=<SCHEMA_NAME> TRACE=480300

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    

You can trace and IMPDP session as well like below:

$ impdp <UserName>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \   

LOGFILE=<LOG_NAME>.log FULL=y TRACE=1ff0300