Tracing datapump sessions

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   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 --- + 1FF0300    x    x    x  'all' To trace all components          (full tracing)

In case of full tracing two trace files are generated in BACKGROUND_DUMP_DEST just like standard tracing. And one trace file is generated in USER_DUMP_DEST.

Shadow Process trace file: _ora_.trc

Individual tracing level values in hexadecimal are shown except last one in the list. You can use individual value or combination of values. If you sum all the individual values you will get 1FF0300 which is full tracing.

To use full level tracing issue data pump export as,expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300To use full level tracing for data pump import operation issue import as,impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300However for most cases full level tracing is not required. As trace 400300 is to trace Worker process(es) and trace 80300 is to trace Master Control Process (MCP). So combining them is trace 480300 and by using trace 480300 you will be able to trace both Master Control process (MCP) and the Worker process(es). This would serve the purpose.So to diagnose any data pump export problem issue,expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300To diagnose any data pump import problem issue,impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300

In case of standard tracing trace files are generated in BACKGROUND_DUMP_DEST. In case of standard tracing,

– If it is Master Process trace file then generated file name is, _dm_.trc

– If it is Worker Process trace file then generated file name is,_dw_.trc