Saturday 14 August 2021

Oracle Data Pump 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 <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 : 

Exadata Information Centre


Recently i was asked a question on the important note on the Exadata Information Center and be updated with the latest updates and patches. Here are the details :