ORACLE SYSTEMDUMP分析

                                            ORACLE SYSTEMDUMP分析

   某日一数据库已经hang住不动了,数据库的版本是oracle 12.1.0.1.发现通过sqlplus正常的连接已经无法使用了.
整个实例hang住了，如下

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 20 01:41:11 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba;

connect命令hang住，无法正常连接

查看alert.log发现很长时间内没有日志抛出了.

Fri Jul 18 09:11:45 2014
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Fri Jul 18 10:14:33 2014
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Fri Jul 18 13:00:46 2014
Thread 1 advanced to log sequence 42 (LGWR switch)
  Current log# 2 seq# 42 mem# 0: +DATA/tt/redo02.log
Fri Jul 18 15:22:20 2014
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 4194304 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;
Fri Jul 18 22:00:03 2014

 

[oracle@rac1 trace]$ date

Fri Jul 18 23:47:48 CST 2014

 

还好sqlplus提供了一个prelim选项可以连接实例

[oracle@rac1 ~]$ sqlplus -prelim / as sysdba;

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 20 01:48:37 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL>

 

对系统进行两次systemdump 266。

[oracle@rac1 ~]$ sqlplus -prelim / as sysdba;

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 20 01:48:37 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump systemdump 266;

等待90秒

SQL> oradebug dump systemdump 266;

 

[oracle@localhost ~]$ awk -f ass109.awk tt1_ora_2806.trc

Starting Systemstate 1
....................................................................
Starting Systemstate 2
...................................................................
Ass.Awk Version 1.0.9 - Processing tt1_ora_2806.trc

System State 1
~~~~~~~~~~~~~~~~
1:                    
51:                                    
52:                                    
53:                                    
54:                                    
55:                                    
56:                                    
57: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
58: 0: waiting for 'cursor: pin S'     
     Cmd: Select
59: 0: waiting for 'Streams AQ: qmn slave idle wait'
60: 0: waiting for 'SQL*Net message from client'
61: 0: waiting for 'gc freelist'       
     Cmd: Select
62: 0: waiting for 'REPL Capture/Apply: RAC AQ qmn coordinator'
63: 2: waited for 'Streams AQ: waiting for time management or cleanup tasks'
64: 0: waiting for 'Streams AQ: load balancer idle'
69: 0: waiting for 'Space Manager: slave idle wait'
70: 0: waiting for 'gc freelist'       
     Cmd: Select
71: 0: waiting for 'gc freelist'       
     Cmd: Select
74: 0: waiting for 'gc freelist'       
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.

                    Resource Holder State
        Latch sent-location:    ??? Blocker

Object Names
~~~~~~~~~~~~
Latch sent-location:                  last post sent-location: kji.h LINE:

在使用了awk脚本分析后，发现没有很明显的阻塞，看来先只能提SR了.