oracle expdp导出元数据TABLE COMMENT过程hang住遭遇bug14192178

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:484

expdp导出元数据时遭遇bug,TABLE COMMENT过程hang住了

在做expdp过程中,hang在以下位置几十个小时,
..Processing object type SCHEMA_EXPORT/[i]TABLE/[i]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进程方法如下:
查找当前正在运行的SID:select sid,username,program,status from V$session where status='ACTIVE' and username<>'SYS';
(sid=424)
查找该session对应的SPID:select * from V$process where addr=(select paddr from V$session where sid=424);
(spid=26087)
设定需要跟踪的进程号:oradebug setospid 26087
设定事件的跟踪级别:oradebug event 10046 trace name context forever,level 12;
查看trace文件的名称:oradebug tracefile_name
查看trace文件中直接路径读的延时信息:cat c2_dw01_29428.trc|grep direc|more

通过诊断后,确认bug:14192178 ,通过对数据库进行patch后解决。
[table=98%,rgb(255, 255, 255)]
Bug 14192178 - EXPDP of partitioned table can be slow (文档 ID 14192178.8)

Description[indent]This 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 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=13844935.8]bug 13844935, https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=13914808.8]bug 13914808 , https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=13898265.8]bug 13898265 and https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=14006804.8]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, https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=13914808.8]bug 13914808 , https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=13898265.8]bug 13898265 and https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=14192178.8&id=14006804.8]bug 14006804 should not be required if you have this fix (14192178) Getting a Fix Use one of the "Fixed" versions listed above [i](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: https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?parent=DOCUMENT&sourceId=14192178.8&patchId=14192178]Patch:14192178
[/indent]

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】