以下是11.2.0.1 一个4节点的rac的trc。
*** 2012-08-29 13:00:29.056
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x14001f][0x9ad3f3],[TX]
----------resource 700000ef6ec1c18----------------------
resname : [0x14001f][0x9ad3f3],[TX]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 113
hv last r.inc : 12
current inc : 12
hv status : 0
hv master : 2
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000001118492100000000110c267b0 .g
access_node : 1
vbreq_state : 0
state : x0
resp : 700000ef6ec1c18
On Scan_q? : N
Total accesses: 26075
Imm. accesses: 24164
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 01 11 84 92 10 00 00 00 01 10 c2 67 b0
GRANTED_Q :
lp 700000e918d9488 gl KJUSEREX rp 700000ef6ec1c18 [0x14001f][0x9ad3f3],[TX]
master 1 gl owner 700000ea0e40ab8 possible pid 41091126 xid 2012-0124-00020C83 bast 0 rseq 1742 mseq 0 history 0x4977d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 700000e918db7b8 gl KJUSERNL rl KJUSEREX rp 700000ef6ec1c18 [0x14001f][0x9ad3f3],[TX]
master 1 owner 3 bast 1 rseq 1714 mseq 0x1 history 0x77d497ad
convert opt KJUSERGETVALUE KJUSERNOTDDVICTIM
----------enqueue 700000e918d9488------------------------
lock version : 46977
Owner node : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0
resp : 700000ef6ec1c18
procp : 700000ea93d00e8
pid : 20447354
proc version : 0
oprocp : 0
opid : 20447354
group lock owner : 700000ea0e40ab8
possible pid : 41091126
xid : 2012-0124-00020C83
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : 0x4977d495
Msg_Seq : 0x0
res_seq : 1742
valblk : 0x0fffffffffffbd700000000000000049 .pI
user session for deadlock lock 0x700000e918d9488
sid: 390 ser: 13783 audsid: 0 user: 0/SYS flags: 0x8000051
pid: 292 O/S info: user: oracle, term: UNKNOWN, ospid: 41091126
image: oracle@ora2a (M001)
current SQL: <none>
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[292.41091126] on resource TX-0014001F-009AD3F3
*** 2012-08-29 13:00:29.075
Submitting asynchronized dump request [28]
----------enqueue 700000e918db7b8------------------------
lock version : 39527
Owner node : 3
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 1103c8738
resp : 700000ef6ec1c18
procp : 700000ea93d9490
pid : 0
proc version : 0
oprocp : 0
opid : 0
group lock owner : 0
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
Open Options : KJUSERNO_XID
Convert options : KJUSERGETVALUE KJUSERNOTDDVICTIM
History : 0x77d497ad
Msg_Seq : 0x1
res_seq : 1714
valblk : 0x00000000000000000000000000000000 .
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x29000f][0x3aa975],[TX]
----------resource 700000e99f9d5d8----------------------
resname : [0x29000f][0x3aa975],[TX]
Local node : 1
dir_node : 3
master_node : 3
hv idx : 99
hv last r.inc : 10
current inc : 12
hv status : 0
hv master : 3
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSEREX
Next Cvt mode : KJUSERNL
msg_seq : 1
res_seq : 1823
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x0900000006acffe00000000000000000 .
access_node : 3
vbreq_state : 0
state : x8
resp : 700000e99f9d5d8
On Scan_q? : N
Total accesses: 26390
Imm. accesses: 24327
Granted_locks : 0
Cvting_locks : 1
value_block: 09 00 00 00 06 ac ff e0 00 00 00 00 00 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 700000ea9d3a890 gl KJUSERNL rl KJUSEREX rp 700000e99f9d5d8 [0x29000f][0x3aa975],[TX]
master 3 gl owner 700000ea0e40ab8 possible pid 41091126 xid 2012-0124-00020C83 bast 0 rseq 1823 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE KJUSERNOTDDVICTIM
----------enqueue 700000ea9d3a890------------------------
lock version : 429637
Owner node : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0
resp : 700000e99f9d5d8
procp : 700000ea944bc98
pid : 41091126
proc version : 148
oprocp : 0
opid : 41091126
group lock owner : 700000ea0e40ab8
possible pid : 41091126
xid : 2012-0124-00020C83
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE KJUSERNOTDDVICTIM
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 1823
valblk : 0x000005387b4c5557000005387b4c5557 .8{LUW8{LUW
user session for deadlock lock 0x700000ea9d3a890
sid: 390 ser: 13783 audsid: 0 user: 0/SYS flags: 0x8000051
pid: 292 O/S info: user: oracle, term: UNKNOWN, ospid: 41091126
image: oracle@ora2a (M001)
current SQL: <none>
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[292.41091126] on resource TX-0029000F-003AA975
*** 2012-08-29 13:00:29.080
Submitting asynchronized dump request [28]
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x14001f][0x9ad3f3],[TX]
----------resource 700000ef6ec1c18----------------------
resname : [0x14001f][0x9ad3f3],[TX]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 113
hv last r.inc : 12
current inc : 12
hv status : 0
hv master : 2
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000010700000ea93d0570 .=p
access_node : 1
vbreq_state : 0
state : x0
resp : 700000ef6ec1c18
On Scan_q? : N
Total accesses: 26077
Imm. accesses: 24165
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 01 07 00 00 0e a9 3d 05 70
GRANTED_Q :
lp 700000e918d9488 gl KJUSEREX rp 700000ef6ec1c18 [0x14001f][0x9ad3f3],[TX]
master 1 gl owner 700000ea0e40ab8 possible pid 41091126 xid 2012-0124-00020C83 bast 0 rseq 1742 mseq 0 history 0x4977d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 700000e918db7b8 gl KJUSERNL rl KJUSEREX rp 700000ef6ec1c18 [0x14001f][0x9ad3f3],[TX]
master 1 owner 3 bast 1 rseq 1714 mseq 0x1 history 0x77d497ad
convert opt KJUSERGETVALUE KJUSERNOTDDVICTIM
----------enqueue 700000e918d9488------------------------
lock version : 46977
Owner node : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0
resp : 700000ef6ec1c18
procp : 700000ea93d00e8
pid : 20447354
proc version : 0
oprocp : 0
opid : 20447354
group lock owner : 700000ea0e40ab8
possible pid : 41091126
xid : 2012-0124-00020C83
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : 0x4977d495
Msg_Seq : 0x0
res_seq : 1742
valblk : 0x0fffffffffffb8604444428800000000 .`DDB
user session for deadlock lock 0x700000e918d9488
sid: 390 ser: 13783 audsid: 0 user: 0/SYS flags: 0x8000051
pid: 292 O/S info: user: oracle, term: UNKNOWN, ospid: 41091126
image: oracle@ora2a (M001)
current SQL: <none>
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[292.41091126] on resource TX-0014001F-009AD3F3
*** 2012-08-29 13:00:29.080
Submitting asynchronized dump request [28]
----------enqueue 700000e918db7b8------------------------
lock version : 39527
Owner node : 3
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 1103c8738
resp : 700000ef6ec1c18
procp : 700000ea93d9490
pid : 0
proc version : 0
oprocp : 0
opid : 0
group lock owner : 0
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
Open Options : KJUSERNO_XID
Convert options : KJUSERGETVALUE KJUSERNOTDDVICTIM
History : 0x77d497ad
Msg_Seq : 0x1
res_seq : 1714
valblk : 0x00000000000000000000000000000000 .
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.97b] :
BLOCKED 0x700000ea9d3a890 5 wq 2 cvtops x201 TX 0x29000f.0x3aa975 [2012-0124-00020C83] inst 2
BLOCKER 0x700000e91a41578 5 wq 1 cvtops x28 TX 0x29000f.0x3aa975 [4004-0049-00036FFF] inst 4
BLOCKED 0x700000e91ba5980 5 wq 2 cvtops x201 TX 0x14001f.0x9ad3f3 [4004-0049-00036FFF] inst 4
BLOCKER 0x700000e918d9488 5 wq 1 cvtops x28 TX 0x14001f.0x9ad3f3 [2012-0124-00020C83] inst 2
* Cancel deadlock victim lockp 0x700000ea9d3a890
里面的很多关键字都是缩写,我把握不准,由当前的信息可以看出是sys用户执行的操作。
sid: 390 ser: 13783
由ash中得到
PROGRAM MODULE ACTION
oracle@ora2a (M001) MMON_SLAVE Auto ADDM Slave Action
NAME PARAMETER1 PARAMETER2
enq: TX - row lock contention name|mode usn<<16 | slot
我认为是系统在做ADDM的时候出现的DEAD LOCK,但是不知到具体操作什么了造成的死锁,请告诉帮忙详细讲解一下,这样的trac如何解读,谢谢
|
|