重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: oracle impdp、expdp 跟踪 [打印本页]

作者: mahan 时间: 2025-9-14 17:33
标题: oracle impdp、expdp 跟踪
--参考 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;

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)