oracle impdp、expdp 跟踪

mahan

--参考 MOS 文档：How to Trace Data Pump Using a Logon Trigger (Doc ID 1991279.1)，创建 10046 跟踪触发器来分析数据泵的 DM 和 DW 进程

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、执行数据泵导入并启用跟踪：

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)         

--常用跟踪值：
--TRACE=1FF0300  全组件跟踪T
--RACE=480300 核心组件跟踪（推荐用于调试）
--TRACE=10300 - 仅 Shadow process 跟踪



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.

4、Run TKPROF on the trace files after Data Pump ends.
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;