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