How to Trace Data Pump Using a Logon Trigger

郑全

How to Trace Data Pump Using a Logon Trigger (Doc ID 1991279.1)       

In this Document
Goal
Solution
References
APPLIES TO:
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data .Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
GOAL
The purpose of this note it to explain how Data Pump can be traced using a database logon trigger.

SOLUTION
1. Create a database after logon trigger to enable Event 10046 for Data Pump  DM and DW processes :

CREATE OR REPLACE TRIGGER sys.set_dp_trace
AFTER LOGON ON DATABASE
DECLARE

  v_program v$session.program%TYPE;
  v_dyn_sql VARCHAR2(100);

BEGIN
  SELECT    substr (program, -5, 2)
  INTO   v_program
  FROM   v$session
  WHERE  sid = (SELECT DISTINCT sid FROM v$mystat);

  IF v_program = 'DW' or v_program= 'DM' THEN

    EXECUTE IMMEDIATE 'alter session set tracefile_identifier = '||'DPTRC';
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';

  END IF;
END;
/

  2. Run Data Pump Export/Import with the following parameters added to the command line:

expdp/impdp ...  METRICS=Y TRACE=480300
      Other possible values of TRACE parameter:

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
------- 'Bit AND'
1FF0300    x    x    x  'all' To trace all components          (full tracing)           
     3. Check trace files generated in USER_DUMP_DEST or DIAGNOSTIC_DEST/diag/rdbms/<dbname>/<sid>/trace. Traces have the DPTRC identifiers enclosed in the name.

Example:

-rw-r-----. 1 oracle oinstall 154K Mar 18 17:15 <SID>_dw01_2122_DPTRC.trm
-rw-r-----. 1 oracle oinstall  13M Mar 18 17:15 <SID>_dw01_2122_DPTRC.trc
-rw-r-----. 1 oracle oinstall 180K Mar 18 17:15 <SID>_dm00_2092_DPTRC.trm
-rw-r-----. 1 oracle oinstall  14M Mar 18 17:15 <SID>_dm00_2092_DPTRC.trc
       4. Run TKPROF on the trace files after Data Pump ends.

Example:

[oracle@<HOST> trace]$  tkprof <SID>_dw01_2122_DPTRC.trc <FILESYSTEM_PATH>/<SID>_dw01_2122_DPTRC.out waits=yes sort=exeela      
       5. Drop SET_DP_TRIGGER

SQL> DROP TRIGGER SYS.SET_DP_TRACE;