How to diagnose Oracle data pump-Add diagnostic information to the pump

11g Data Pump (EXPDP/IMPDP) still have a lot of bugs, often encounter inexplicable card death phenomenon, let people overwhelmed, recently read an article, in fact, in the data pump export import, you can log tracking

Parameter:trace

With tracing enabled, simply add a trace parameter to DataPump (EXPDP) or DataPump (IMPDP) and specify a 7-digit hexadecimal number. The first three digits enable tracking of a specific data pump component, while the last four digits are usually: 0300. Any leading zeros can be omitted, and the values specified by the trace parameters are case-insensitive.

TRACE = 04a0300
Or
trace=4a0300

Note that the use of this parameter must have specific permissions, otherwise the 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, October, 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 S Ecurity, OLAP and Data Mining scoring Engine options Ora-31631:privileges are required

in this case, we only need to grant exp_full_database or imp_full_database roles

Here the parameters are 480300, other trace parameters and tracking data pump components corresponding examples are:

--Example of combination (last 4 digits is usually 0300):  40300 to trace Process services 80300 to trace Master Cont Rol process (MCP) 400300 to trace Worker Process (es)--+ 4c0300 to trace Process services and Master Control and worker PR Ocesses

The resulting trace log format is generally as follows:

--Run a Data Pump job with full tracing:--the results in both trace files in Background_dump_dest:    --    Master Pr ocess 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 do 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

Trace 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 applic ation Clusters, Automatic Storage Management, Olap,data Mining and Real application testing optionsoracle_home =/u01/app/ Product/oracle/db_1system Name:aixnode name:orcl1release:1version:6machine:00f6c6c64c00instance Name:hnyy1redo Thread mounted by this instance:1oracle process Number:559unix process pid:28574120, Image: [email protected] (DM0 0) * * * 2014-12-05 13:06:38.154*** SESSION ID: (8851.28833) 2014-12-05 13:06:38.154*** CLIENT ID: () 2014-12-05 13:06:38.154 SERVICE Name: (sys$users) 2014-12-05 13:06:38.154*** MODULE Name: () 2014-12-05 13:06:38.154*** ACTION Name: () 2014-12- 13:06:38.154 kupp:13:06:38.142:current trace/debug flags:00480300 = 4719360*** MODULE NAME: (Data Pump Master) 2014-12 -05 13:06:38.195*** ACTION NAME: (sys_export_full_01) 2014-12-05 13:06:38.195 kupc:13:06:38.194:setting remote flag for this process to Falseprvtaqis-enter Prvtaqis SUBTA B_name upd prvtaqis sys table UPD kupm:13:06:38.336:attached to control queue as Mcpkupm:13:06:38.345:while starting, CO Ntrol Queue Subscriber Count is:2kupp:13:06:38.346:initialization complete for master process dm00kupm:13:06:38.405:ent Ered main loopkupm:13:06:38.640: ****in DISPATCH at 47198, request type=1001kupm:13:06:38.640:current user IS:SYSTEMKUPM : 13:06:38.640:hand: = Dbms_datapump. OPEN (' EXPORT ', ' full ', ' ', ' sys_export_full_01 ', ' ', ' 2 '); * * * 2014-12-05 13:06:40.338kupm:13:06:40.337:resumable enabledkupm:13:06:40.346:entered state:definingkupm:13:06:40.346:initing file Systemkwqberlst!retval block Kwqberlst Rqan->lagno_kwqiia 7 Kwqberlst rqan->lascn_kwqiia > 0 block kwqberlst Rqan->lascn_kwqiia 7 Kwqberl  St ascn-1500723611 LASCN kupm:13:06:42.700: ****in DISPATCH at 47202, request type=1035kupm: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_ Expdp1204b_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 (' EMR graph ', ' Test image results ', ' 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 =2041KWQBERLST!retval block kwqberlst Rqan->lagno_kwqiia 7 kwqberlst Rqan->lascn_kwqiia > 0 Block Kwqberlst Rqan->lascn_kwqiia 7 kwqberlst ascn-1500723611 LASCN: kupm:13:06:42.750 ****in at 47202 , request type=1022kupm:13:06:42.750:current user is:systemkupm:13:06:42.750:dbms_datapump. Add_file (hand, ' full20141204b_%u_db.dmp ', ' DUMP ', 0,, 1);

How to diagnose Oracle data pump-Add diagnostic information to the pump