dump数据库oracle,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了.