How to Diagnose Oracle Data Pump-如何給資料泵添加診斷資訊，diagnosepump-

How to Diagnose Oracle Data Pump-如何給資料泵添加診斷資訊，diagnosepump-

11g的資料泵（expdp/impdp）還是有很多bug，經常遇到莫名的卡死現象，讓人不知所措，最近看了一篇文章，其實在進行資料泵匯出匯入的時候，可以進行日誌跟蹤

Parameter: TRACE

啟用跟蹤功能，只需要在DataPump(expdp)或DataPump(impdp)的時候添加一個trace參數，對其指定一個7位元的十六進位。前三個數字啟用跟蹤特定的資料泵組件,而通常最後四位元字為:0300。任何前置字元為零的可以省略,跟蹤參數指定的值是不區分大小寫。

TRACE = 04A0300
 or:
TRACE=4a0300

注意該參數的使用必須要有特定的許可權，否則會報ORA-31631錯誤

% 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

這種情況，我們只需要授予 EXP_FULL_DATABASE 或者IMP_FULL_DATABASE角色

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 

這裡的參數為480300，其他Trace的參數及跟蹤的資料泵組件對應的例子有：

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

產生的追蹤記錄檔的格式一般如下：

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

如何找到參數檔案的位置？ 

Data Pump trace files 寫在BACKGROUND_DUMP_DEST 和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

 追蹤記錄檔產生案例

<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:orcl1Release:1Version:6Machine:00F6C6C64C00Instance name: hnyy1Redo thread mounted by this instance: 1Oracle process number: 559Unix process pid: 28574120, image: oracle@orcl1 (DM00)*** 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-05 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 subtab_name upd prvtaqis 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: 2KUPP: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=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 kwqberlst ascn -1500723611 lascn 22 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 ('電子病曆圖形','檢驗映像結果','檢驗報告映像') 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 22 KUPM:13:06:42.750: ****IN DISPATCH 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);