求助1：数据库异常宕机诊断

ricky

环境： 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 [CF] 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[zhfx1]$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[zhfx1]$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：请帮忙诊断一下故障原因。

ricky

想看刘大 精彩的分析过程 请刘大不吝赐教。

lunar

alert日志，crs日志，os日志传下，否则ML怎么分析啊，O(∩_∩)O哈哈~

Maclean Liu(刘相兵

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 [CF] 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

Maclean Liu(刘相兵

....................................................
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:                                      [DEAD]
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' [Enq CF-00000000-00000000] 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)[Buffer 0x00400234] 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:                                      [DEAD]
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' [Enq CF-00000000-00000000] 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)[Buffer 0x00400234] 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:                                      [DEAD]
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' [Enq CF-00000000-00000000] 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)[Buffer 0x00400234] 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:                                      [DEAD]
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' [Enq CF-00000000-00000000] 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.
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$

Maclean Liu(刘相兵

大量等待'log file switch completion 切换日志无法成功， 可能与当时的IO以及整体负载有关

需要当时的AWR，如果没有AWR则需要附件时间点的AWR和ADDM