Unveiling Oracle Data Pump Tracing Secrets

 Hey fellow database administrators! Ever wondered how to diagnose those errors that pop up during a Data Pump job? Let’s dive into the world of tracing to uncover the magic! Let's take a playful plunge into the fascinating world of DataPump tracing levels! Buckle up as we embark on a journey through hex codes and purposeful tracing.

From shadow processes to worker bees, discover how to peek into the inner workings of your database datapump jobs with an insightful flair. Unleash the ultimate tracing extravaganza and unravel Data Pump mysteries like a pro! 🚀

First things first, ensure your user has the right privileges by running the Data Pump job with TRACE as a privileged user. If you're feeling adventurous, you can even grant those privileges to your login user. Easy peasy!😉

But wait, there's more! Most errors can be diagnosed by creating trace files for the Master Control Process (MCP) and Worker Processes. Simply run a Data Pump job with standard tracing and voilà, you’ve got your trace files ready to roll!

Here's the syntax on how you run the datapump job with trace 

$ expdp <LOGIN_USER>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> 
DUMPFILE=<DUMP_NAME>.dmp 
LOGFILE=<LOG_NAME>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> 
TRACE=80300

Feeling like a tracing pro yet? Well, hold onto your hats because you can specify each Data Pump component for even more detailed tracing. And if you really want to go all out, there’s a special command for full tracing – talk about diving deep into the database rabbit hole!

--    Master Process trace file:   <SID>_dm<number>_<process_id>.trc  
--    Worker Process trace file: <SID>_dw<number>_<process_id>.trc  

Oh, and don’t forget to check your MAX_DUMP_FILE_SIZE parameter to ensure you capture all that juicy trace information. 

Pro tip: setting it to unlimited is like giving your traces the green light to go wild! 😉😄

Further adding to this, each Data-Pump component can be specified explicitly in order to obtain tracing details of that component, Summary of Data Pump trace levels are as below..

  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)

Now grab your hex codes and get ready to trace like never before. It's time to unravel the mysteries. 🤤

A huge shoutout to Mike Dietrich for sharing the wisdom in his session.(Doc ID 286496.1)

Now go forth, trace like a boss, and conquer those Data Pump mysteries! 🚀