How to Trace Data Pump Using a Logon Trigger

Configurare noua (How To)

Situatie

The purpose of this document it to explain how Data Pump can be traced using a database logon trigger.

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

sqlplus 

connect / as sysdba

 

CREATE OR REPLACE TRIGGER sys.DATA_PUMP_TRIGGER
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 = ‘||’datapump’;
    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

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 24K Mar 20 17:15 <SID>_dw01_1233_datapump.trm
-rw-r—–. 1 oracle oinstall  7M Mar 20 17:15 <SID>_dw01_1233_datapump.trc
-rw-r—–. 1 oracle oinstall 40K Mar 20 17:15 <SID>_dm00_1234_datapump.trm
-rw-r—–. 1 oracle oinstall  5M Mar 20 17:15 <SID>_dm00_1234_datapump.trc

4. Run TKPROF on the trace files after Data Pump ends.

Example:

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

5. Drop SET_DP_TRIGGER

SQL> DROP TRIGGER SYS.SET_DP_TRACE;

Solutie

Tip solutie

Permanent

Voteaza

(15 din 23 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?