tracing oracle data pump job for errors

tracing oracle data pump job for errors

— 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#, p.pid
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

=========================================================

Enabling tracing on a running DATAPUMP job

What if I already triggered my datapump job and it’s in the middle of doing it’s task? Shall I kill the entire job intermittently and restart it by enabling the tracing again? That’s really a bad idea  if we’re dealing with GBs/TBs of data.

Thanks to the datapump team at Oracle; They made it much more simpler to the users.

Please follow the below procedure to enable tracing on an existing datapump job.

% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f.dmp \ LOGFILE=expdp_f.log FULL=Y
Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 17:11:08
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** DIRECTORY=my_dir 
DUMPFILE=expdp_f.dmp LOGFILE=expdp_f.log FULL=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA--

press Control-C to go to the Interactive Command mode, and temporarily stop the job with the STOP_JOB command:

Export> stop Are you sure you wish to stop this job ([yes]/no): yes
-- use the system generated job-name SYS_EXPORT_FULL_01 to re-attach to the job
-- and specify the TRACE parameter with a tracing level:


% expdp system/manager ATTACH=sys_export_full_01 TRACE=480300 Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 17:23:48
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: FALSE
  GUID: F4E6BF997DFA46378D543F998E82653E
  Start Time: Thursday, 18 October, 2007 17:23:49 
  Mode: FULL
  Instance: m10203wa
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** DIRECTORY=my_dir DUMPFILE=expdp_f.dmp 
LOGFILE=expdp_f.log FULL=y
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /usr/DataPump/expdp_f.dmp
    bytes written: 4,096
Worker 1 Status:
  State: UNDEFINED
-- restart the job and change back from Interactive Command mode to Logging mode
-- with CONTINUE_CLIENT (note that tracing with level 480300 is now active):
Export> cont
Restarting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** DIRECTORY=my_dir 
DUMPFILE=expdp_f.dmp LOGFILE=expdp_s.log FULL=y
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
...

Use a database init.ora/spfile event to trace Data Pump (Not in a case of a job  that’s already running). Data Pump tracing can also be started with a line with EVENT 39089 in the initialization parameter file. This method should only be used to trace the Data Pump calls in an early state, e.g. if details are needed about theDBMS_DATAPUMP.OPEN API call. Trace level 0x300 will trace all Data Pump client processes. Note that this event cannot be enabled through an ALTER SYSTEM command, only a restart of the database will enable the event.
Example when using init.ora initialization parameter file:

- add the following line to init.ora parameter file: 
EVENT="39089 trace name context forever,level 0x300" 
- Restart the database. 
- Start the Export Data Pump or Import Data Pump job.

Example when using spfile initialization parameter file:

-- when using spfile parameter file:
CONNECT / as sysdba 
SHOW PARAMETER event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string ALTER SYSTEM SET EVENTS = '39089 trace name context forever, level 0x300' 
SCOPE = spfile;  SHUTDOWN immediate
STARTUP
SHOW PARAMETER event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      39089 trace name context forev
                                                 er, level 0x300
- Start the Export Data Pump or Import Data Pump job.
-- to remove the event(s) again:
ALTER SYSTEM RESET EVENT SCOPE = spfile SID='*';  
SHUTDOWN immediate
STARTUP

=============================================================================================