Whenever you issue, impdp help=y or expdp help=y you can see a list of parameters that can be used for oracle data pump export/import jobs. From there you don't see any parameter by which you can trace data pump jobs. But tracing datapump job is an important issue in case of diagnosing incorrect behavior and/or troubleshooting Data Pump errors. The undocumented parameter TRACE is really useful to troubleshoot data pump jobs.
The
tracing of data pump is done by TRACE parameter. This parameter takes
value as 7 digit hexadecimal number. Specifying the parameter value
follow some rules.
Out of 7 digit hexadecimal number,
- first 3 digits are responsible to enable tracing for a specific data pump component.
- Rest 4 digits are usually 0300
- Specifying more than 7 hexadecimal number is not allowed. Doing so will result,
UDE-00014: invalid value for parameter, 'trace'.
- Specifying leading 0x (hexadecimal specification characters) is not allowed.
- Value to be specified in hexadecimal. You can't specify it in decimal.
- Leading zero can be omitted. So it may be less than 7 hexadecimal digit.
- Values are not case sensitive.
Before
starting tracing be sure you have large enough value setting of the
MAX_DUMP_FILE_SIZE initialization parameter because this size is used to
capture all the trace information. The default value is UNLIMITED which
is ok.
SQL> show parameter max_dump_file
NAME TYPE VALUE
------------------------------------ ----------- -------------------
max_dump_file_size string UNLIMITED
SQL> select value from v$parameter where name='max_dump_file_size';
VALUE
--------------------------------------------------------------------
UNLIMITED
If it is not unlimited then you can set it by,
SQL> alter system set max_dump_file_size=UNLIMITED;
System altered.
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.
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:
<SID>_dm<number>_<process_id>.trc
- If it is Worker Process trace file then generated file name is:
<SID>_dw<number>_<process_id>.trc
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: <SID>_ora_<process_id>.trc
The list of trace level in data pump is shown 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
--- +
1FF0300 x x x 'all' To trace all components (full tracing)
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 solve any data pump export problem issue:
expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300
To solve any data pump import problem issue:
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300
