Before going into original discussion let's see how data pump job is done. The architecture of data pump job is discussed on http://arjudba.blogspot.com/2009/04/data-pump-process-architecture-master.html
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
Related Documents
No comments:
Post a Comment