How to Diagnose Oracle Data Pump-How to add diagnostic information to the Data Pump, diagnosepump-

How to Diagnose Oracle Data Pump-How to add diagnostic information to the Data Pump, diagnosepump-

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, 2005, 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 SYSDBAGRANT 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_3020.trc Location: USER_DUMP_DEST   or   <ADR_HOME>/trace

Tracking log generation case

<Pre name = "code" class = "SQL"> Trace file/u01/app/product/oracle/diag/rdbms/hnyy/hnyy1/trace/hnyy1_dm00_28574120.trcOracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit ProductionWith the Partitioning, real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME =/u01/app/product/oracle/db_1System name: AIXNode name: orcl1Relea Se: 1 Version: 6 Machine: 00F6C6C64C00Instance name: hnyy1Redo 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 falseprv?is-Enter prv=is subtab_name upd prv=is sys table upd KUPM: 13: 06: 38.336: attached to control queue as MCPKUPM: 13: 06: 38.345: While starting, control queue subscriber count is: 2 KUPP: 13: 06: 38.346: Initialization complete for master process DM00KUPM: 13: 06: 38.405: Entered main loopKUPM: 13: 06: 38.640: *** in dispatch at 47198, request type = 1001 KUPM: 13: 06: 38.640: Current user is: SYSTEMKUPM: 13: 06: 38.640: hand: = DBMS_DATAPUMP.OPEN ('Port', 'full', '', 'sys _ EXPORT_FULL_01 ','', '2'); *** 13:06:40. 338 KUPM: 1 3:06:40. 337: Resumable enabledKUPM: 13: 06: 40.346: Entered state: DEFININGKUPM: 13: 06: 40.346: initing file systemkwqberlst! Retval block kwqberlst rqan-> Objective 7 kwqberlst rqan-> lascn_kwqiia> 0 block kwqberlst rqan-> Objective 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: SYSTEMKUPM: 13: 06: 42.700: DBMS_DATAPUMP.SET_PARAMETER (hand, 'client _ command', 'System/******** dumpfile = dump: full20141204b _ % U_db.dmp logfile = dump: full_ex Pdp1204b_db.log full = y exclude = STATISTICS, PACKAGE, FUNCTION, PROCEDURE, INDEX, TABLE: "IN (select table_name from dba_tabKUPM: 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_COMMANDKUPM: 13: 06: 42.703: *** out dispatch, request type = 1035, response type = 2041 kwqberlst! Retval block kwqberlst rqan-> Objective 7 kwqberlst rqan-> lascn_kwqiia> 0 block kwqberlst rqan-> Objective 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: SYSTEMKUPM: 13: 06: 42.750: DBMS_DATAPUMP.ADD_FILE (hand, 'full20141204b _ % U_db.dmp ', 'dump', 0, 1 );