TRACE DATAPUMP JOBS ORACLE

Many of DBA’s may be unaware of tracing a datapump session with a direct hidden parameter that can be specified with either EXPDP/IMPDP

In help option, trace parameter will not be visible

[oracle@dpprod trace]$ expdp -help|grep trace

There is a hidden parameter which can be set while execution of expdp/impdp to trace the job at a granular level. There are different types of trace that can be enabled from API till slave jobs

Below is a sample trace map which can be used for enabling relevant trace

Mostly, we trace master and worker process with

480300 ( KUPM – KUPW )– 80300 (KUPM) + 400300 (KUPW) which is a combination of master and worker process trace

For example, if i need trace file to be generated for expdp, i can enable trace like follows

expdp \'/ as sysdba\' directory=dpexp tables=sh.custome dumpfile=trc.dmp logfile=trc.log reuse_dumpfiles=Y trace=480300

Trace files can be searched by going into default directory

SQL> select name from v$diag_info where name like '%Diag Trace%';

NAME
----------------------------------------------------------------
Diag Trace

Check for dm00(master) and dw00(worker) trace files

[oracle@dpprod trace]$ ls -lrt *dm00* *dw00*|tail
-rw-r----- 1 oracle oinstall   90 Jan 14 10:46 dpprod_dm00_7295.trm
-rw-r----- 1 oracle oinstall 1156 Jan 14 10:46 dpprod_dm00_7295.trc
-rw-r----- 1 oracle oinstall   71 Jan 14 10:46 dpprod_dw00_7329.trm
-rw-r----- 1 oracle oinstall  980 Jan 14 10:46 dpprod_dw00_7329.trc
-rw-r----- 1 oracle oinstall   90 Jan 14 10:46 dpprod_dm00_7326.trm
-rw-r----- 1 oracle oinstall 1156 Jan 14 10:46 dpprod_dm00_7326.trc
-rw-r----- 1 oracle oinstall   71 Jan 14 10:47 dpprod_dw00_7388.trm
-rw-r----- 1 oracle oinstall  976 Jan 14 10:47 dpprod_dw00_7388.trc
-rw-r----- 1 oracle oinstall   90 Jan 14 10:47 dpprod_dm00_7386.trm
-rw-r----- 1 oracle oinstall 1152 Jan 14 10:47 dpprod_dm00_7386.trc

Sample trace for master process:

This file contains a preamble with header details like system information followed by sessions information followed by the original trace messages

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      dpprod
Release:        4.14.35-1902.300.11.el7uek.x86_64
Version:        #2 SMP Tue Mar 17 17:11:47 PDT 2020
Machine:        x86_64
Instance name: dpprod
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 7386, image: oracle@dpprod (DM00)


*** 2022-01-14 10:47:27.356
*** SESSION ID:(34.58124) 2022-01-14 10:47:27.356
*** CLIENT ID:() 2022-01-14 10:47:27.356
*** SERVICE NAME:(SYS$USERS) 2022-01-14 10:47:27.356
*** MODULE NAME:(Data Pump Master) 2022-01-14 10:47:27.356
*** CLIENT DRIVER:() 2022-01-14 10:47:27.356
*** ACTION NAME:(SYS_SQL_FILE_FULL_01) 2022-01-14 10:47:27.356

KUPC: Setting remote flag for this process to FALSE
KUPP: Initialization complete for master process DM00

Leave a Reply