************************************* * Description: Tracing Data Pump API * Date: 11:23 AM EST, 09/25/2018 ************************************* <1> Oracle Data Pump is an individual utility, which can be kicked off from system command line to export/import database object logical defination: | |__ o. In addition, Data Pump provides API for PL\SQL developer to be merged enclosed the procedures or functions. | |__ o. Following is the example to initilize a Data Pump session: DECLARE PumpHandle NUMBER; BEGIN PumpHandle := dbms_datapump.open(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'Job_Name_2', version => 'LATEST'); END; / <2> To understand the detail of Data Pump process by launching the utility with trace option: | |__ CMD> expdp emeralit/emeralit TRACE=480300 metrics=yes | |__ CMD> impdp emeralit/emeralit TRACE=480300 metrics=yes | |__ o. If it is Master Process trace file then generated file name is, _dm_.trc. The location is under normal /diag/trace folder. If it is Worker Process trace file then generated file name is, _dw_.trc. The location is under normal /diag/trace folder. *** 2018-08-28 15:23:56.818 *** SESSION ID:(22.15571) 2018-08-28 15:23:56.818 *** CLIENT ID:() 2018-08-28 15:23:56.818 *** SERVICE NAME:(SYS$USERS) 2018-08-28 15:23:56.818 *** MODULE NAME:() 2018-08-28 15:23:56.818 *** ACTION NAME:() 2018-08-28 15:23:56.818 KUPP:15:23:56.818: Current trace/debug flags: 00480300 = 4719360 *** MODULE NAME:(Data Pump Master) 2018-08-28 15:23:56.818 *** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2018-08-28 15:23:56.818 KUPC:15:23:56.818: Setting remote flag for this process to FALSE KUPM:15:23:56.833: Attached to control queue as MCP KUPM:15:23:56.833: While starting, control queue subscriber count is: 2 KUPP:15:23:56.833: Initialization complete for master process DM00 *** 2018-08-28 15:23:56.927 KUPM:15:23:56.927: Entered main loop *** 2018-08-28 15:23:58.927 KUPM:15:23:58.927: ****IN DISPATCH at 55438, request type=1001 KUPM:15:23:58.927: Current user is: MICORE KUPM:15:23:58.927: hand := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA', '', 'SYS_EXPORT_SCHEMA_01', '', '2'); *** 2018-08-28 15:23:59.005 KUPM:15:23:59.005: Resumable enabled KUPM:15:23:59.005: Entered state: DEFINING KUPM:15:23:59.005: initing file system KUPM:15:23:59.005: ****OUT DISPATCH, request type=1001, response type =2041 *** 2018-08-28 15:24:03.005 KUPM:15:24:03.005: ****IN DISPATCH at 55443, request type=1035 KUPM:15:24:03.005: Current user is: MICORE KUPM:15:24:03.005: DBMS_DATAPUMP.SET_PARAMETER (hand, 'TRACE', 4719360); KUPM:15:24:03.005: In Check_param_dependency with name: TRACE KUPM:15:24:03.005: ****OUT DISPATCH, request type=1035, response type =2041 *** 2018-08-28 15:24:09.005 KUPM:15:24:09.005: ****IN DISPATCH at 55449, request type=1022 KUPM:15:24:09.005: Current user is: MICORE KUPM:15:24:09.005: DBMS_DATAPUMP.ADD_FILE (hand, '', '', 0, , 3); KUPM:15:24:09.005: ****OUT DISPATCH, request type=1022, response type =2041 *** 2018-08-28 15:24:14.005 KUPM:15:24:14.005: ****IN DISPATCH at 55454, request type=1012 KUPM:15:24:14.005: Current user is: MICORE KUPM:15:24:14.005: DBMS_DATAPUMP.LOG_ENTRY (hand, ';;; Export: Release 11.2.0.4.0 - Production on Tue Aug 28 15:23:55 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ', 1); KUPM:15:24:14.005: Log message received from shadow: KUPM:15:24:14.005: RQ,KUPC$C_1_20180828152356,KUPC$A_1_152356661000000,MCP,4,Y KUPM:15:24:14.005: ;;; Export: Release 11.2.0.4.0 - Production on Tue Aug 28 15:23:55 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. KUPM:15:24:14.005: ****OUT DISPATCH, request type=1012, response type =2041 <3> Following SQL could find which session is for Data Pump, and trace the session accordingly: | |__ SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr; <4> Tracing the session, and tune the output: | |__ SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; | ALTER SESSION SET EVENTS '10046 trace name context off'; | |__ SQL> tkprof c:\path\oraprod_smon_2029.trc c:\path\output.rcf Reference: | |__ o. http://db.geeksinsight.com/2013/01/08/tracing-datapump-sessions/ | |__ o. https://blog.dbi-services.com/how-to-debug-a-data-pump-error/ | |__ o. https://mdesouza.wordpress.com/2013/01/23/tracing-oracle-data-pump-job-for-errors/ | |__ o. https://www.realdbamagic.com/expdp-impdp-trace/
Your Comments