标题: How to Trace Data Pump Using a Logon Trigger [打印本页] 作者: 郑全 时间: 2022-8-10 19:10 标题: 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
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