expdp中的UDE-00008问题解决

在一个测试库上用expdp full=y进行数据卸载时,进程运行刚开始不久就提示UDE-00008错误[@more@]

[oracle@db2 bdump]$ expdp system/oracle directory=DUMP_DIR dumpfile=full.dmp logfile=full.log full=y
...
...
...
...
这个地方开始出错了
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.KUPC$QUE_INT", line 529
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1

根据错误提示oracle在一个消息侦听中等待超时(ORA-25254: time-out in LISTEN while waiting for a message)
重新启动db后,expdp还是不能正常运行。 在metalink上列出了好多在rac系统上有类似的BUG但在10.2.0.1版本上已经修正了。
这个测试库目前已经更新到10.2.0.3了.所以不因该存来类似的BUG。最后在expdp进程tarce文件中发现了有用的信息
这里列出了一些有用的关键信息
[oracle@db2 bdump]$ more ibudb_dm00_10398.trc
/home/oracle/product/admin/IBUDB/bdump/ibudb_dm00_10398.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2/db
System name: Linux
Node name: db2
Release: 2.4.21-32.EL
Version: #1 Fri Apr 15 21:17:56 EDT 2005
Machine: i686
Instance name: IBUDB
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 10398, image: oracle@db2 (DM00)

*** ACTION NAME:(SYS_EXPORT_FULL_01) 2007-08-31 10:01:58.080
*** MODULE NAME:(Data Pump Master) 2007-08-31 10:01:58.080
*** SERVICE NAME:(SYS$USERS) 2007-08-31 10:01:58.080
*** SESSION ID:(1081.13) 2007-08-31 10:01:58.080
*** 2007-08-31 10:01:58.080
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=08/31/2007 10:01:56
Dump Count=1
Allocation request for: tmp
Heap: 0x32e9d278, size: 16
******************************************************
HEAP DUMP heap name="sga heap" desc=0x2000002c
extent sz=0x32c8 alt=108 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x1
...
...
...
HEAP DUMP heap name="sql area" desc=0x32e9d278
extent sz=0xff4 alt=32767 het=156 rec=0 flg=2 opc=0
parent=0x2000002c owner=0x32e9d1ec nex=(nil) xsz=0x400000
Subheap has 0 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: 0x36e15e0c, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=18, calls cur/top: 0x3654ab60/0x36549f30, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 192
last post received-location: kgllldl: post after freeing latch
last process to post me: 36e13bbc 14 0
last post sent: 0 0 33
last post sent-location: ksrpublish
last process posted by me: 36e13bbc 14 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x36f76efc
O/S info: user: oracle, term: UNKNOWN, ospid: 10398
OSD pid info: Unix process pid: 10398, image: oracle@db2 (DM00)
...
...
...
=========================
User Session State Object
=========================
----------------------------------------
SO: 0x364dc240, type: 4, owner: 0x36e15e0c, flag: INIT/-/-/0x00
(session) sid: 1081 trans: 0x351ee0b0, creator: 0x36e15e0c, flag: (48100041) USR/- BSY/-/-/-/-/-
DID: 0001-0012-00000004, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
O/S info: user: oracle, term: UNKNOWN, ospid: 10398, machine: db2
program: oracle@db2 (DM00)
application name: Data Pump Master, hash value=674186793
action name: SYS_EXPORT_FULL_01, hash value=832198035
last wait for 'latch: shared pool' blocking sess=0x(nil) seq=409 wait_time=1006095 seconds since wait started=0
address=20098580, number=d5, tries=0
Dumping Session Wait History
for 'latch: shared pool' count=1 wait_time=1006095
address=20098580, number=d5, tries=0
for 'wait for unread message on broadcast channel' count=1 wait_time=976558
channel context=3605df80, channel handle=36035cf0, =0
for 'wait for unread message on broadcast channel' count=1 wait_time=976500
channel context=3605df80, channel handle=36035cf0, =0
for 'wait for unread message on broadcast channel' count=1 wait_time=976550
channel context=3605df80, channel handle=36035cf0, =0
for 'wait for unread message on broadcast channel' count=1 wait_time=976529
channel context=3605df80, channel handle=36035cf0, =0
for 'wait for unread message on broadcast channel' count=1 wait_time=975257
channel context=3605df80, channel handle=36035cf0, =0
for 'wait for unread message on broadcast channel' count=1 wait_time=972993
channel context=3605df80, channel handle=36035cf0, =0
for 'latch: library cache' count=1 wait_time=14940
address=3393dd80, number=d6, tries=0
for '
' count=1 wait_time=13
address=3393dd80, number=d6, tries=0
for 'latch: library cache lock' count=1 wait_time=40
address=3393de48, number=d7, tries=0
temporary object counter: 0
----------------------------------------
Virtual Thread:
kgskvt: 0x32f78378, sess: 0x364dc240, vc: (nil), proc: (nil)
consumer group cur: (upd? 0), mapped: , orig:
vt_state: 0x0, vt_flags: 0x20, blkrun: 0
is_assigned: 0, in_sched: 0 (0)
vt_active: 0 (pending: 0)
used quanta: 0 (cg: 0)
cpu start time: 0, quantum status: 0x0
quantum checks to skip: 0, check thresh: 0
idle time: 0, active time: 0 (cg: 0)
cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0)
queued time outs: 0, time: 0 (cur 0, cg 0)
calls aborted: 0, num est exec limit hit: 0
undo current: 0k max: 0k
----------------------------------------
UOL used : 0 locks(used=13, free=4)
KGX Atomic Operation Log 0x326437bc
Mutex (nil)(0, 0) idn 0 oper NONE
Cursor Pin uid 1081 efd 21 whr 10 slp 0
KGX Atomic Operation Log 0x326437e4
Mutex 0x32f4bac4(0, 1) idn 0 oper NONE
Cursor Stat uid 1081 efd 18 whr 2 slp 0
oper=NONE pt1=0x32f4ba10 pt2=(nil) pt3=(nil)
pt4=(nil) u41=0 stt=0
KGX Atomic Operation Log 0x3264380c
Mutex (nil)(0, 0) idn 0 oper NONE
Library Cache uid 1081 efd 0 whr 0 slp 0
============================
Current Session State Object
============================
----------------------------------------
SO: 0x364d89d0, type: 4, owner: 0x36549f30, flag: INIT/-/-/0x00
(session) sid: 1078 trans: 0x351ee0b0, creator: (nil), flag: (2) -/REC -/-/-/-/-/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: 0x3191c070, user: 0/SYS
temporary object counter: 0
=========================
Current Parent KGL Object
=========================
LIBRARY OBJECT HANDLE: handle=32e9d368 mtx=0x32e9d41c(2) cdp=2
name=select obj# from oid$ where user#=:1 and oid$=:2
hash=9d54e2404ea4561ac8c82e778b29ccb8 timestamp=08-31-2007 09:39:40
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1 hpc=0022 hlc=0022
lwt=0x32e9d3c4[0x32e9d3c4,0x32e9d3c4] ltm=0x32e9d3cc[0x32e9d3cc,0x32e9d3cc]
pwt=0x32e9d3a8[0x32e9d3a8,0x32e9d3a8] ptm=0x32e9d3b0[0x32e9d3b0,0x32e9d3b0]
ref=0x32e9d3e4[0x32e9d3e4,0x32e9d3e4] lnd=0x32e9d3f0[0x32e9d3f0,0x32e9d3f0]
LIBRARY OBJECT: object=32e9cf50
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 32e9cedc 32e9cbac 32e9ca24
1 32e9cedc 32e9cd28 31f8cf54
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 32ea793c 32e9cfe8 I/P/A/-/- 0 NONE 00
==================
Current KGL Object
==================
LIBRARY OBJECT HANDLE: handle=32e9ca24 mtx=0x32e9cad8(0) cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=X latch#=1 hpc=fffa hlc=fffa
lwt=0x32e9ca80[0x32e9ca80,0x32e9ca80] ltm=0x32e9ca88[0x3237520c,0x32468cf8]
pwt=0x32e9ca64[0x32e9ca64,0x32e9ca64] ptm=0x32e9ca6c[0x32e9ca6c,0x32e9ca6c]
ref=0x32e9caa0[0x32e9cbac,0x32e9cbac] lnd=0x32e9caac[0x32e9caac,0x32e9caac]
LIBRARY OBJECT: object=32e9c60c
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
...
...
...
LATCH:0 TOTAL SPACE: 4092
FREELIST CHUNK COUNT:93 OBJECT SIZE:44
Permanent space allocated for KGL pins
LATCH:0 TOTAL SPACE: 74088
FREELIST CHUNK COUNT:138 OBJECT SIZE:84
Permanent space allocated for KGL locks
LATCH:0 TOTAL SPACE: 247416
FREELIST CHUNK COUNT:147 OBJECT SIZE:104
Permanent space allocated for KGL S handles
LATCH:0 TOTAL SPACE: 362452
FREELIST CHUNK COUNT:42 OBJECT SIZE:316
Permanent space allocated for KGL M handles
LATCH:0 TOTAL SPACE: 232960
FREELIST CHUNK COUNT:51 OBJECT SIZE:512
Permanent space allocated for KGL L handles
LATCH:0 TOTAL SPACE: 90624
FREELIST CHUNK COUNT:56 OBJECT SIZE:768
Permanent space allocated for KGL A handles
LATCH:0 TOTAL SPACE: 135400
FREELIST CHUNK COUNT:56 OBJECT SIZE:200
Permanent space allocated for KGL objects
LATCH:0 TOTAL SPACE: 105168
FREELIST CHUNK COUNT:62 OBJECT SIZE:84
Permanent space allocated for KGL handle dependents
LATCH:0 TOTAL SPACE: 28896
FREELIST CHUNK COUNT:1315 OBJECT SIZE:12
Did Not dump SGA Heap desc=0x2000002c.
Current time: 08/31/2007 10:01:56
Last SGA heap dump at 08/31/2007 10:01:56
SGA Heap Dump Count = 1
===============================
End 4031 Diagnostic Information
===============================
kupprdp: Error 4031 detected in master process DM00.
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-31666: Master process DM00 had an unhandled exception.
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select obj# from oid$ where ...","sql area","tmp")
ORA-06512: at "SYS.KUPM$MCP", line 955
ORA-06512: at line 2

ORACLE在连续等待latch: library cache.4031是一个很普遍的问题.oracle在进行full模式的expdp的时候,要试图获取所有需要导出的
对象。这样有可能促使shared pool不断的增长.如果达到shared_pool_size参数的大小时,就会出现4031.因为测试机器的内存很小并且
SGA是通过手动分配的,所以每个组件的内存都是固定的。最后通过调整sga为自动内存管理,关键时刻可以利用oracle对SGA的自动调整
满足应用。
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 376M
sga_target big integer 300M

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1262020 No
Redo Buffers 2928640 No
Buffer Cache Size 113246208 Yes
Shared Pool Size 184549376 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 4194304 Yes
Granule Size 4194304 No
Maximum SGA Size 394264576 No
Startup overhead in Shared Pool 88080384 No
Free SGA Memory Available 79691776

11 rows selected.
利用调整后的参数启动数据库后,expdp job顺利运行,在没有提示上述错误。再次验证了oracle sga自动管理的好处。还可以通过加大共享
缓冲区的大小来解决此问题。也有UDE-00008由于其他问题造成的，所以在关键问题上必须经过分析诊断后在制定解决方案。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/129805/viewspace-967239/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/129805/viewspace-967239/