Thursday, April 30, 2009

How to trace/diagnosis oracle data pump jobs

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.

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