HowtoDiagnoseOracleDataPump-如何给数据泵添加诊断信息

11g的数据泵（expdp/impdp）还是有很多bug，经常遇到莫名的卡死现象，让人不知所措，最近看了一篇文章，其实在进行数据泵导出导入

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

这里的参数为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: _dm_.trc
-- Worker Process trace file: _dw_.trc
-- And one trace file in USER_DUMP_DEST:
-- Shadow Process trace file: _ora_.trc

如何找到参数文件的位置？
Data Pump trace files 写在BACKGROUND_DUMP_DEST 和USER_DUMP_DEST.

Data Pump Master Control Process (MCP).
Format : _dm_.trc
Example: ORCL_dm00_2896.trc or: ORCL_dm01_3422.trc (for second active Master Control Process)
Location: BACKGROUND_DUMP_DEST or /trace
Data Pump Worker Process trace file.
Format : _dw_.trc
Example: ORCL_dw01_2936.trc or: ORCL_dw01_2844.trc and ORCL_dw02_2986.trc (if PARALLEL=2)
Location: BACKGROUND_DUMP_DEST or /trace
Data Pump Shadow Process trace file.
Format : _ora_.trc
Example: ORCL_ora_3020.trc
Location: USER_DUMP_DEST or /trace

跟踪日志生成案例

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)

*** 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 FALSE
prvtaqis - Enter
prvtaqis subtab_name upd
prvtaqis 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 ('EXPORT', 'FULL', '', 'SYS_EXPORT_FULL_01', '', '2');

*** 2014-12-05 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