How to Diagnose Oracle Data Pump-How to add diagnostic information to a Data Pump

How to Diagnose Oracle Data Pump-How to add diagnostic information to a Data Pump

There are still many bugs in the 11g Data Pump (expdp/impdp), and we often encounter inexplicable card death, which makes people feel overwhelmed. I recently read an article, in fact, you can track logs when exporting and importing data from a data pump.

Parameter: TRACE

To enable the tracking function, you only need to add a trace parameter when DataPump (expdp) or DataPump (impdp) and specify a 7-digit hexadecimal value for it. The first three digits enable specific tracking data pump components, and the last four digits are usually 0300. Any leading zero can be omitted. the value specified by the trace parameter is case insensitive.

TRACE = 04A0300

Or:

TRACE = 4a0300

Note that the use of this parameter must have specific permissions, otherwise a ORA-31631 error will be reported

% Expdp scott/tiger DIRECTORY = my_dir DUMPFILE = expdp_s.dmp LOGFILE = expdp_s.log TABLES = emp TRACE = 480300
Export: Release 10.2.0.3.0-Production on Friday, 19 October, 2007 13:46:33
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required.

In this case, you only need to grant the EXP_FULL_DATABASE or IMP_FULL_DATABASE role

CONNECT/AS SYSDBA
GRANT exp_full_database TO scott;
% Expdp scott/tiger DIRECTORY = my_dir DUMPFILE = expdp_s.dmp LOGFILE = expdp_s.log TABLES = emp TRACE = 480300

Here the parameter is 480300. Examples of other Trace parameters and tracking data pump components are as follows:

-- Example of combination (last 4 digits are usually 0300 ):
40300 to trace Process services
80300 to trace Master Control Process (MCP)
400300 to trace Worker process (es)
-- +
4C0300 to trace Process services and Master Control and Worker processes

The format of the generated trace log is generally as follows:

-- Run a Data Pump job with full tracing:
-- This results in two trace files in BACKGROUND_DUMP_DEST:
-- Master Process trace file: <SID >_dm <number >_< process_id>. trc
-- Worker Process trace file: <SID> _ dw <number> _ <process_id>. trc
-- And one trace file in USER_DUMP_DEST:
-- Shadow Process trace file: <SID> _ ora _ <process_id>. trc

How can I find the location of the parameter file?
Data Pump trace files are written in BACKGROUND_DUMP_DEST and USER_DUMP_DEST.

<Strong> Data Pump Master Control Process (MCP). </strong>
Format: <SID> _ dm <number> _ <process_id>. trc
Example: ORCL_dm00_2896.trc or: ORCL_dm01_3422.trc (for second active Master Control Process)
Location: BACKGROUND_DUMP_DEST or <ADR_HOME>/trace
<Strong> Data Pump Worker Process trace file. </strong>
Format: <SID> _ dw <number> _ <process_id>. trc
Example: ORCL_dw01_2936.trc or: ORCL_dw01_2844.trc and ORCL_dw02_2986.trc (if PARALLEL = 2)
Location: BACKGROUND_DUMP_DEST or <ADR_HOME>/trace
<Strong> Data Pump Shadow Process trace file. </strong>
Format: <SID> _ ora _ <process_id>. trc
Example: orcl_ora_301_trc
Location: USER_DUMP_DEST or <ADR_HOME>/trace

Tracking log generation case

Trace file/u01/app/product/oracle/diag/rdbms/hnyy/hnyy1/trace/hnyy1_dm00_28574120.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME =/u01/app/product/oracle/db_1
System name: AIX
Node name: orcl1
Release: 1
Version: 6
Machine: 00F6C6C64C00
Instance name: hnyy1
Redo thread mounted by this instance: 1
Oracle process number: 559
Unix process pid: 28574120, image: oracle @ orcl1 (DM00)

* ** 13:06:38. 154
* ** Session id: (8851.28833) 13:06:38. 154
* ** Client id: () 13:06:38. 154
* ** Service name :( SYS $ USERS) 13:06:38. 154
* ** Module name: () 13:06:38. 154
* ** Action name: () 13:06:38. 154
 
KUPP: 13: 06: 38.142: Current trace/debug flags: 00480300 = 4719360
* ** Module name :( Data Pump Master) 13:06:38. 195
* ** Action name :( SYS_EXPORT_FULL_01) 13:06:38. 195
 
KUPC: 13: 06: 38.194: Setting remote flag for this process to FALSE
Prvquota is-Enter
Prvquota is subtab_name upd
Prvquota is sys table upd
KUPM: 13: 06: 38.336: Attached to control queue as MCP
KUPM: 13: 06: 38.345: While starting, control queue subscriber count is: 2
KUPP: 13: 06: 38.346: Initialization complete for master process DM00
KUPM: 13: 06: 38.405: Entered main loop
KUPM: 13: 06: 38.640: *** in dispatch at 47198, request type = 1001
KUPM: 13: 06: 38.640: Current user is: SYSTEM
KUPM: 13: 06: 38.640: hand: = DBMS_DATAPUMP.OPEN ('Port', 'full', '', 'sys _ EXPORT_FULL_01 ','', '2 ');

* ** 13:06:40. 338
K
UPM: 13: 06: 40.337: Resumable enabled
KUPM: 13: 06: 40.346: Entered state: DEFINING
KUPM: 13: 06: 40.346: initing file system
Kwqberlst! Retval block
Kwqberlst rqan-> lagno_kwqiia 7
Kwqberlst rqan-> lascn_kwqiia> 0 block
Kwqberlst rqan-> lascn_kwqiia 7

Kwqberlst ascn-1500723611 lascn 22
KUPM: 13: 06: 42.700: *** in dispatch at 47202, request type = 1035
KUPM: 13: 06: 42.700: Current user is: SYSTEM
KUPM: 13: 06: 42.700: DBMS_DATAPUMP.SET_PARAMETER (hand, 'client _ command', 'System/******** dumpfile = dump: full20141204b _ % U_db.dmp logfile = dump: full_expdp1204b_db.log full = y exclude = STATISTICS, PACKAGE, FUNCTION, PROCEDURE, INDEX, TABLE: "IN (select table_name from dba_tab
KUPM: 13: 06: 42.700: les where table_name in ('electronic medical record graphics ', 'test image result', 'test report image') and owner = 'zlhis ') "cluster = n TRACE = 480300 ');
KUPM: 13: 06: 42.700: In Check_param_dependency with name: CLIENT_COMMAND
KUPM: 13: 06: 42.703: *** out dispatch, request type = 1035, response type = 2041
Kwqberlst! Retval block
Kwqberlst rqan-> lagno_kwqiia 7
Kwqberlst rqan-> lascn_kwqiia> 0 block
Kwqberlst rqan-> lascn_kwqiia 7
Kwqberlst ascn-1500723611 lascn 22
KUPM: 13: 06: 42.750: *** in dispatch at 47202, request type = 1022
KUPM: 13: 06: 42.750: Current user is: SYSTEM
KUPM: 13: 06: 42.750: DBMS_DATAPUMP.ADD_FILE (hand, 'full20141204b _ % U_db.dmp ', 'dump', 0, 1 );