求助1：数据库异常宕机诊断
环境： hp_ux数据库 10.2.0.4 rac
故障现象：
2014 年1月24日 一点左右，数据库节点1异常宕机。
alert日志现象：
Sat Jan 25 00:40:03 2014
Thread 1 advanced to log sequence 196732 (LGWR switch)
Current log# 3 seq# 196732 mem# 0: /dev/vgzhfx03/rlv_log3_1
Current log# 3 seq# 196732 mem# 1: /dev/vgzhfx03/rlv_log3_2
Current log# 3 seq# 196732 mem# 2: /dev/vgzhfx03/rlv_log3_3
Sat Jan 25 00:56:58 2014
System State dumped to trace file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc
Sat Jan 25 00:57:57 2014
Killing enqueue blocker (pid=29412) on resource CF-00000000-00000000
by killing session 873.1
Sat Jan 25 00:58:03 2014
Killing enqueue blocker (pid=29412) on resource CF-00000000-00000000
by killing session 873.1
Sat Jan 25 00:58:05 2014
Errors in file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc:
ORA-00494: enqueue held for too long (more than 900 seconds) by 'inst 1, osid 29412'
Sat Jan 25 00:58:08 2014
System State dumped to trace file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc
Sat Jan 25 00:58:31 2014
System State dumped to trace file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc
Sat Jan 25 01:02:58 2014
System State dumped to trace file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc
Sat Jan 25 01:03:57 2014
Killing enqueue blocker (pid=29412) on resource CF-00000000-00000000
by terminating the process
LMD0: terminating instance due to error 2103
Sat Jan 25 01:03:59 2014
Shutting down instance (abort)
集群节点现象
oracle@zhfxdb1$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.zhfx.db application ONLINE ONLINE zhfxdb2
ora....x1.inst application ONLINE OFFLINE
ora....x2.inst application ONLINE ONLINE zhfxdb2
ora....B1.lsnr application ONLINE ONLINE zhfxdb1
ora....db1.gsd application ONLINE ONLINE zhfxdb1
ora....db1.ons application ONLINE ONLINE zhfxdb1
ora....db1.vip application ONLINE ONLINE zhfxdb1
ora....B2.lsnr application ONLINE ONLINE zhfxdb2
ora....db2.gsd application ONLINE ONLINE zhfxdb2
ora....db2.ons application ONLINE ONLINE zhfxdb2
ora....db2.vip application ONLINE ONLINE zhfxdb2
检查数据库进程，ps -ef|grep ora_smon 发现这个进程还在的，登陆到数据库里面检查：
select open_mode from v$database;
mounted
数据库处于mounted模式，alter database open不能打开。
继而重启节点1，发现节点1不能正常shutdown immediate；
oracle@zhfxdb1$ps -ef|grep ora_
oracle 14761 1 0 01:09:16 ? 0:01 ora_dbw0_zhfx1
oracle 29412 1 0 22:00:17 ? 0:05 ora_ckpt_zhfx1
oracle 28560 1 0 09:15:37 ? 0:01 ora_dbw0_zhfx1
发现这3个进程无法kill ，kill -9也不能杀。
最后解决办法：重启服务器。
question：请帮忙诊断一下故障原因。
想看刘大 精彩的分析过程 请刘大不吝赐教。 alert日志，crs日志，os日志传下，否则ML怎么分析啊，O(∩_∩)O哈哈~ Killing enqueue blocker (pid=29412) on resource CF-00000000-00000000
by killing session 873.1
Sat Jan 25 00:58:03 2014
Killing enqueue blocker (pid=29412) on resource CF-00000000-00000000
by killing session 873.1
Sat Jan 25 00:58:05 2014
Errors in file /oracle/product/10.2.0.1/admin/zhfx/bdump/zhfx1_diag_29383.trc:
ORA-00494: enqueue held for too long (more than 900 seconds) by 'inst 1, osid 29412'
Sat Jan 25 00:58:08 2014
某进程长期持有 CF control file enqueue
SO: c00000063a3e9388, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=14, calls cur/top: c00000063e5d0a40/c00000063e5d0a40, flag: (6) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 114
last post received-location: kcbbza
last process to post me: c00000063a3e8b98 1 6
last post sent: 0 0 21
last post sent-location: ksbria
last process posted by me: c00000063a3e9388 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c00000063a46b500
O/S info: user: oracle, term: UNKNOWN, ospid: 29412
OSD pid info: Unix process pid: 29412, image: oracle@zhfxdb1 (CKPT)
Short stack dump:
29412是instance 1 的CKPT
stack call为
ksdxfstk()+48<-ksdxcb()+5776<-sspuser()+640<-<kernel><-_read_sys()+48<-_read()+224<-skgfospo()+2048<-$cold_skgfrwat()+64<-ksfdwtio()+608<-ksfdwat1()+112<-ksfdrwat0()+608<-ksfdblock()+208<-k
cflwi()+144<-$cold_kcflci()+384<-kcblci()+352<-kcblcio()+832<-kcblsltck()+96<-krhahrs()+448<-krhahtrm()+624<-kcvcfsi()+9200<-kcvucpb()+1184<-kcvcca()+768<-ksbcti()+816<-ksbabs()+1568<-ksbrd
p()+2816<-opirip()+1136<-$cold_opidrv()+1408<-sou2o()+336<-$cold_opimai_real()+640<-main()+368<-main_opd_entry()+80
SO: c00000063a5f94f8, type: 4, owner: c00000063a3e9388, flag: INIT/-/-/0x00
(session) sid: 873 trans: 0000000000000000, creator: c00000063a3e9388, flag: (51) USR/- BSY/-/-/-/KIL/-
DID: 0001-000E-00000005, short-term DID: 0001-000E-00000006
txn branch: 0000000000000000
oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS
service name: SYS$BACKGROUND
waiting for 'direct path read' blocking sess=0x0000000000000000 seq=13292 wait_time=0 seconds since wait started=15
file number=7c, first dba=1, block cnt=1
Dumping Session Wait History
for 'direct path read' count=1 wait_time=0
file number=7b, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=1
file number=7a, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=1
file number=79, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=7185
file number=78, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=0
file number=77, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=1
file number=76, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=0
file number=75, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=0
file number=74, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=0
file number=73, first dba=1, block cnt=1
for 'direct path read' count=1 wait_time=1
file number=72, first dba=1, block cnt=1
CKPT 最近的等待为direct path read
....................................................
Starting Systemstate 4
..................................................
Ass.Awk Version 1.0.33
~~~~~~~~~~~~~~~~~~~~~~
Source file : zhfx1_diag_29383.trc
System State 1 (2014-01-25 00:56:58.120)
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
1:
2: waiting for 'pmon timer' seq=5
3: last wait for 'ksdxexeotherwait'
4: waiting for 'rdbms ipc message' seq=238
5: waiting for 'rdbms ipc message' seq=25980
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
10: waiting for 'rdbms ipc message' seq=9
11: waiting for 'rdbms ipc message' seq=20655
12: waiting for 'rdbms ipc message' seq=8881
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
15: waiting for 'smon timer' seq=5134
16: waiting for 'rdbms ipc message' seq=29
17: waiting for 'rdbms ipc message' seq=1647
18:
19: waiting for 'SQL*Net message from client' seq=7457
20: waiting for 'Streams AQ: qmn coordinator idle wait' seq=1541
21: waiting for 'log file switch completion' seq=7384
22: waiting for 'rdbms ipc message' seq=75
23: waiting for 'rdbms ipc message' seq=58933
24: waiting for 'Streams AQ: waiting for messages in the queue' seq=11390
25: waiting for 'SQL*Net message from client' seq=21047
26: waiting for 'SQL*Net message from client' seq=10119
27: waiting for 'SQL*Net message from client' seq=955
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
Cmd: Insert
30: last wait for 'db file scattered read' (134,ddb,3)
Cmd: Select
31: waiting for 'log file switch completion' seq=18995
Cmd: PL/SQL Execute
32: waiting for 'SQL*Net message from client' seq=80
33: waiting for 'log file switch completion' seq=22462
Cmd: Select
34: waiting for 'SQL*Net message from client' seq=725
35: waiting for 'SQL*Net message from client' seq=385
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
Cmd: PL/SQL Execute
38: waiting for 'log file switch completion' seq=3802
Cmd: Update
39: waiting for 'log file switch completion' seq=5
40: waiting for 'SQL*Net message from client' seq=7155
41: waiting for 'log file switch completion' seq=4
42: waiting for 'SQL*Net message from client' seq=169
43: waiting for 'log file switch completion' seq=27869
Cmd: PL/SQL Execute
44: waiting for 'log file switch completion' seq=63580
Cmd: PL/SQL Execute
45: waiting for 'log file switch completion' seq=37655
Cmd: PL/SQL Execute
46: waiting for 'log file switch completion' seq=28997
Cmd: PL/SQL Execute
47: waiting for 'log file switch completion' seq=26832
Cmd: PL/SQL Execute
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
50: waiting for 'buffer busy waits' (1,234,1) seq=2
51: waiting for 'log file switch completion' seq=5
52: waiting for 'log file switch completion' seq=4
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate. (The holder may have released the resource before we
dumped the state object tree of the blocking process).
o Lines with 'Enqueue conversion' below can be ignored *unless*
other sessions are waiting on that resource too. For more, see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)
Resource Holder State
Enq CF-00000000-00000000 14: waiting for 'direct path read'
Buffer 0x00400234 48: waiting for 'log file switch completion'
Blockers According to Tracefile Wait Info:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. This may not work for 64bit platforms. See bug 2902997 for details.
2. If the blocking process is shown as 0 then that session may no longer be
present.
3. If resources are held across code layers then sometimes the tracefile wait
info will not recognise the problem.
No blockers seen.
Object Names
~~~~~~~~~~~~
Enq CF-00000000-00000000
Buffer 0x00400234
Summary of Wait Events Seen (count>10)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
17 : 'log file switch completion'
System State 2 (2014-01-25 00:58:08.249)
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
WARNING: The following processes had a corrupted / in-flux state object tree :
Process 30: at line 3161246
1:
2: waiting for 'pmon timer' seq=5
3: last wait for 'DIAG idle wait'
4: waiting for 'rdbms ipc message' seq=239
5: waiting for 'rdbms ipc message' seq=27410
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
10: waiting for 'rdbms ipc message' seq=10
11: waiting for 'rdbms ipc message' seq=20656
12: waiting for 'rdbms ipc message' seq=8882
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
15: waiting for 'smon timer' seq=5134
16: waiting for 'rdbms ipc message' seq=29
17: waiting for 'rdbms ipc message' seq=1651
18:
19: waiting for 'SQL*Net message from client' seq=7497
20: waiting for 'Streams AQ: qmn coordinator idle wait' seq=1549
21: waiting for 'log file switch completion' seq=7384
22: waiting for 'rdbms ipc message' seq=78
23: waiting for 'rdbms ipc message' seq=58982
24: waiting for 'Streams AQ: waiting for messages in the queue' seq=11452
25: waiting for 'SQL*Net message from client' seq=21163
26: waiting for 'SQL*Net message from client' seq=10175
27: waiting for 'SQL*Net message from client' seq=955
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
Cmd: Insert
30: last wait for 'db file sequential read' (134,e1d,1)
Cmd: Select
31: waiting for 'log file switch completion' seq=18995
Cmd: PL/SQL Execute
32: waiting for 'SQL*Net message from client' seq=80
33: waiting for 'log file switch completion' seq=22462
Cmd: Select
34: waiting for 'SQL*Net message from client' seq=725
35: waiting for 'SQL*Net message from client' seq=385
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
Cmd: PL/SQL Execute
38: waiting for 'log file switch completion' seq=3802
Cmd: Update
39: waiting for 'log file switch completion' seq=5
40: waiting for 'SQL*Net message from client' seq=7201
41: waiting for 'log file switch completion' seq=4
42: waiting for 'SQL*Net message from client' seq=169
43: waiting for 'log file switch completion' seq=27869
Cmd: PL/SQL Execute
44: waiting for 'log file switch completion' seq=63580
Cmd: PL/SQL Execute
45: waiting for 'log file switch completion' seq=37655
Cmd: PL/SQL Execute
46: waiting for 'log file switch completion' seq=28997
Cmd: PL/SQL Execute
47: waiting for 'log file switch completion' seq=26832
Cmd: PL/SQL Execute
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
50: waiting for 'buffer busy waits' (1,234,1) seq=2
51: waiting for 'log file switch completion' seq=5
52: waiting for 'log file switch completion' seq=4
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate. (The holder may have released the resource before we
dumped the state object tree of the blocking process).
o Lines with 'Enqueue conversion' below can be ignored *unless*
other sessions are waiting on that resource too. For more, see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)
Resource Holder State
Enq CF-00000000-00000000 14: waiting for 'direct path read'
Buffer 0x00400234 48: waiting for 'log file switch completion'
Blockers According to Tracefile Wait Info:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. This may not work for 64bit platforms. See bug 2902997 for details.
2. If the blocking process is shown as 0 then that session may no longer be
present.
3. If resources are held across code layers then sometimes the tracefile wait
info will not recognise the problem.
No blockers seen.
Object Names
~~~~~~~~~~~~
Enq CF-00000000-00000000
Buffer 0x00400234
Summary of Wait Events Seen (count>10)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
17 : 'log file switch completion'
System State 3 (2014-01-25 00:58:28.240)
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
WARNING: The following processes had a corrupted / in-flux state object tree :
Process 22: at line 1769907
Process 30: at line 3161246
1:
2: waiting for 'pmon timer' seq=5
3: last wait for 'ksdxexeotherwait'
4: waiting for 'rdbms ipc message' seq=240
5: waiting for 'rdbms ipc message' seq=27913
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
10: waiting for 'rdbms ipc message' seq=10
11: waiting for 'rdbms ipc message' seq=20660
12: waiting for 'rdbms ipc message' seq=8883
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
15: waiting for 'smon timer' seq=5134
16: waiting for 'rdbms ipc message' seq=29
17: waiting for 'rdbms ipc message' seq=1651
18:
19: waiting for 'SQL*Net message from client' seq=7517
20: waiting for 'Streams AQ: qmn coordinator idle wait' seq=1553
21: waiting for 'log file switch completion' seq=7384
22: last wait for 'rdbms ipc message'
23: waiting for 'rdbms ipc message' seq=59005
24: waiting for 'Streams AQ: waiting for messages in the queue' seq=11482
25: waiting for 'SQL*Net message from client' seq=21229
26: waiting for 'SQL*Net message from client' seq=10217
27: waiting for 'SQL*Net message from client' seq=955
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
Cmd: Insert
30: last wait for 'db file scattered read' (134,e48,3)
Cmd: Select
31: waiting for 'log file switch completion' seq=18995
Cmd: PL/SQL Execute
32: waiting for 'SQL*Net message from client' seq=80
33: waiting for 'log file switch completion' seq=22462
Cmd: Select
34: waiting for 'SQL*Net message from client' seq=725
35: waiting for 'SQL*Net message from client' seq=385
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
Cmd: PL/SQL Execute
38: waiting for 'log file switch completion' seq=3802
Cmd: Update
39: waiting for 'log file switch completion' seq=5
40: waiting for 'SQL*Net message from client' seq=7247
41: waiting for 'log file switch completion' seq=4
42: waiting for 'SQL*Net message from client' seq=169
43: waiting for 'log file switch completion' seq=27869
Cmd: PL/SQL Execute
44: waiting for 'log file switch completion' seq=63580
Cmd: PL/SQL Execute
45: waiting for 'log file switch completion' seq=37655
Cmd: PL/SQL Execute
46: waiting for 'log file switch completion' seq=28997
Cmd: PL/SQL Execute
47: waiting for 'log file switch completion' seq=26832
Cmd: PL/SQL Execute
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
50: waiting for 'buffer busy waits' (1,234,1) seq=2
51: waiting for 'log file switch completion' seq=5
52: waiting for 'log file switch completion' seq=4
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate. (The holder may have released the resource before we
dumped the state object tree of the blocking process).
o Lines with 'Enqueue conversion' below can be ignored *unless*
other sessions are waiting on that resource too. For more, see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)
Resource Holder State
Enq CF-00000000-00000000 14: waiting for 'direct path read'
Buffer 0x00400234 48: waiting for 'log file switch completion'
Blockers According to Tracefile Wait Info:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. This may not work for 64bit platforms. See bug 2902997 for details.
2. If the blocking process is shown as 0 then that session may no longer be
present.
3. If resources are held across code layers then sometimes the tracefile wait
info will not recognise the problem.
No blockers seen.
Object Names
~~~~~~~~~~~~
Enq CF-00000000-00000000
Buffer 0x00400234
Summary of Wait Events Seen (count>10)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
17 : 'log file switch completion'
System State 4 (2014-01-25 01:02:58.311)
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
WARNING: The following processes had a corrupted / in-flux state object tree :
Process 30: at line 3161246
1:
2: waiting for 'pmon timer' seq=5
3: last wait for 'ksdxexeotherwait'
4: waiting for 'rdbms ipc message' seq=242
5: waiting for 'rdbms ipc message' seq=33483
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
10: waiting for 'rdbms ipc message' seq=11
11: waiting for 'rdbms ipc message' seq=20668
12: waiting for 'rdbms ipc message' seq=8885
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
15: waiting for 'smon timer' seq=5134
16: waiting for 'rdbms ipc message' seq=34
17: last wait for 'rdbms ipc message'
18:
19: waiting for 'SQL*Net message from client' seq=7703
20: waiting for 'Streams AQ: qmn coordinator idle wait' seq=1587
21: waiting for 'log file switch completion' seq=7384
22: waiting for 'rdbms ipc message' seq=81
23: waiting for 'rdbms ipc message' seq=59226
24: waiting for 'Streams AQ: waiting for messages in the queue' seq=11752
25: waiting for 'SQL*Net message from client' seq=21759
26: waiting for 'SQL*Net message from client' seq=10461
27: waiting for 'SQL*Net message from client' seq=955
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
Cmd: Insert
30: last wait for 'db file scattered read' (135,6e73,2)
Cmd: Select
31: waiting for 'log file switch completion' seq=18995
Cmd: PL/SQL Execute
32: waiting for 'SQL*Net message from client' seq=84
33: waiting for 'log file switch completion' seq=22462
Cmd: Select
34: waiting for 'SQL*Net message from client' seq=749
35: waiting for 'SQL*Net message from client' seq=385
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
Cmd: PL/SQL Execute
38: waiting for 'log file switch completion' seq=3802
Cmd: Update
39: waiting for 'log file switch completion' seq=3
40: waiting for 'SQL*Net message from client' seq=7429
41: waiting for 'log file switch completion' seq=4
42: waiting for 'SQL*Net message from client' seq=169
43: waiting for 'log file switch completion' seq=27869
Cmd: PL/SQL Execute
44: waiting for 'log file switch completion' seq=63580
Cmd: PL/SQL Execute
45: waiting for 'log file switch completion' seq=37655
Cmd: PL/SQL Execute
46: waiting for 'log file switch completion' seq=28997
Cmd: PL/SQL Execute
47: waiting for 'log file switch completion' seq=26832
Cmd: PL/SQL Execute
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
50:
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate. (The holder may have released the resource before we
dumped the state object tree of the blocking process).
o Lines with 'Enqueue conversion' below can be ignored *unless*
other sessions are waiting on that resource too. For more, see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)
Resource Holder State
Enq CF-00000000-00000000 14: waiting for 'direct path read'
Blockers According to Tracefile Wait Info:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. This may not work for 64bit platforms. See bug 2902997 for details.
2. If the blocking process is shown as 0 then that session may no longer be
present.
3. If resources are held across code layers then sometimes the tracefile wait
info will not recognise the problem.
No blockers seen.
Object Names
~~~~~~~~~~~~
Enq CF-00000000-00000000
Summary of Wait Events Seen (count>10)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
15 : 'log file switch completion'
List of Processes That May Be Stuck
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
21: waiting for 'log file switch completion' seq=7384
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
31: waiting for 'log file switch completion' seq=18995
33: waiting for 'log file switch completion' seq=22462
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
38: waiting for 'log file switch completion' seq=3802
39: waiting for 'log file switch completion' seq=5
41: waiting for 'log file switch completion' seq=4
43: waiting for 'log file switch completion' seq=27869
44: waiting for 'log file switch completion' seq=63580
45: waiting for 'log file switch completion' seq=37655
46: waiting for 'log file switch completion' seq=28997
47: waiting for 'log file switch completion' seq=26832
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
50: waiting for 'buffer busy waits' (1,234,1) seq=2
51: waiting for 'log file switch completion' seq=5
52: waiting for 'log file switch completion' seq=4
6: waiting for 'ges remote message' seq=9283
7: waiting for 'gcs remote message' seq=394
8: waiting for 'gcs log flush sync' seq=393
9: waiting for 'gcs remote message' seq=373
13: waiting for 'enq: CF - contention' seq=21428
14: waiting for 'direct path read' seq=13292
21: waiting for 'log file switch completion' seq=7384
28: waiting for 'log file switch completion' seq=4
29: waiting for 'log file switch completion' seq=46344
31: waiting for 'log file switch completion' seq=18995
33: waiting for 'log file switch completion' seq=22462
36: waiting for 'Streams AQ: waiting for time management or cleanup tasks' seq=3888
37: waiting for 'log file switch completion' seq=2280
38: waiting for 'log file switch completion' seq=3802
41: waiting for 'log file switch completion' seq=4
43: waiting for 'log file switch completion' seq=27869
44: waiting for 'log file switch completion' seq=63580
45: waiting for 'log file switch completion' seq=37655
46: waiting for 'log file switch completion' seq=28997
47: waiting for 'log file switch completion' seq=26832
48: waiting for 'log file switch completion' seq=3
49: waiting for 'Streams AQ: qmn slave idle wait' seq=2
------------------ooOoo------------------
For the LATEST version of this utility see
http://dlsunuk11.uk.oracle.com/Public/Utils.html#ass
For additional documentation see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html
Suggested improvements, bugs etc. should be sent to kevin.p.quinn@oracle.com
End of report. 3421474 Lines Processed.
$
$
$
大量等待'log file switch completion 切换日志无法成功， 可能与当时的IO以及整体负载有关
需要当时的AWR，如果没有AWR则需要附件时间点的AWR和ADDM
页:
[1]