*************************************
* 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