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 t
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=1FF0300 To use full level tracing for data pump import operation issue import as, impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300 However 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=480300 To 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
