expdp导出元数据时遭遇bug,TABLE COMMENT过程hang住了
在做expdp过程中,hang在以下位置几十个小时,
..Processing object type SCHEMA_EXPORT/TABLE/COMMENT
通过expdp过程指定TRACE=480300 :
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (文档 ID 286496.1)
输出hang的trace 日志信息如下:
KUPW:12:24:27.492: 1: TABLE
KUPW:12:24:27.492: 1: NEW_SR_INCOME
KUPW:12:24:27.493: 1: WC_NSUP2_R_ACCL_ANSW_BN_MA_F
KUPW:12:24:27.494: 1: Base Process info: 41996 and processing status: C and processing state: R
KUPW:12:24:27.494: 1: new state: R
KUPW:12:24:27.494: 1: new status: C
KUPW:12:24:27.494: 1: In procedure BUILD_SUBNAME_LIST with COMMENT:.
KUPW:12:24:27.494: 1: In function NEXT_PO_NUMBER
KUPW:12:24:27.494: 1: KUPF$FILE.WRITE_LOB called.
KUPW:12:24:27.498: 1: KUPF$FILE.WRITE_LOB returned.
KUPW:12:24:27.498: 1: MD FilePieces Count: 1
KUPW:12:24:27.498: 1: FORALL called.
KUPW:12:24:27.510: 1: FORALL returned.
KUPW:12:24:27.511: 1: DBMS_LOB.TRIM called. v_md_xml_clob
KUPW:12:24:27.511: 1: DBMS_LOB.TRIM returned.
KUPW:12:24:27.511: 1: DBMS_METADATA.FETCH_XML_CLOB called. Handle: 100001
同时再结合10046事件,分析出hang在以下系统表:
DW slaves showing time executing SQL likely to be of the form "SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(...FROM SYS.KU$_ACPTABLE_VIEW..."
10046 trace expdp进程方法如下:
通过诊断后,确认bug:14192178 ,通过对数据库进行patch后解决。
Bug 14192178 - EXPDP of partitioned table can be slow (文档 ID 14192178.8) |
DescriptionThis problem is introduced in 11.2.0.3
Datapump export (EXPDP) of partitioned objects can be slow compared to earlier releases.
There have been several separate bug fixes which attempted to address this expdp performanceissue for various internal queries such as bug 13844935, bug 13914808 , bug 13898265 and bug 14006804.
This fix effectively supersedes all of those fixes and can also help for some IMPDP / import operations and some DBMS_METADATA queries.
Rediscovery Notes
Observe poor performance of datapump export of partitioned objects in 11.2.0.3 DW slaves showing time executing SQL likely to be of the form
"SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(...FROM SYS.KU$_ACPTABLE_VIEW...
" Workaround None Note:
For interim patches you should only need the fix for this bug.
Fixes for earlier bugs 13844935, bug 13914808 , bug 13898265 and bug 14006804 should not be required if you have this fix (14192178) Getting a Fix Use one of the "Fixed" versions listed above (for Patch Sets / bundles use the latest version available as contents are cumulative - the "Fixed" version listed above is the first version where the fix is included) or You can check for existing interim patches here: Patch:14192178
|
上一篇: oracle远程expdp与exp一些问题说明-EXP-00113下一篇: Oracle数据库删除undo表空间遇到ORA-30013及ORA-01548的解决方案
|