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