Archive for January, 2013

tracing oracle data pump job for errors

January 23, 2013 Leave a comment

— Obtain Data Pump process info:

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#,
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

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)

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 metrics=yes

To solve any data pump import problem issue,
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300 metrics=yes

The metric paarmeter will just output extra details in the logfile of the Export Data Pump or Import Data Pump job

Also refer to Metalink note ID 286496.1

Categories: Oracle