oracle 绑定变量模糊查询,求助-ACTIVE DG 异常shutdown

环境： 主库 11.2.0.3  RAC 2个节点

备库 11.2.0.3  active data guard

os redhat 5.7 x64

故障描述：

连续2天下午，备库异常挂掉，重启备库恢复，相关日志如下：

asm alert 日志报

Tue Jan 29 16:35:49 2013

WARNING: client [sanddb:sanddbdg] not responsive for 192s; state=0x1. killing pid 367

Tue Jan 29 16:36:49 2013

WARNING: client [sanddb:sanddbdg] cleanup delayed; waited 252s, pid 367 mbr 0x0

Tue Jan 29 16:37:50 2013

WARNING: client [sanddb:sanddbdg] cleanup delayed; waited 312s, pid 367 mbr 0x0

Tue Jan 29 16:38:50 2013

WARNING: client [sanddb:sanddbdg] cleanup delayed; waited 372s, pid 367 mbr 0x0

oracle alert 日志报：

Tue Jan 29 16:34:40 2013

System State dumped to trace file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_ora_32504.trc

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_ora_32504.trc:

Tue Jan 29 16:39:22 2013

NOTE: Deferred communication with ASM instance

Tue Jan 29 16:39:22 2013

NOTE: ASMB terminating

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_pr00_648.trc:

ORA-01041: internal error. hostdef extension doesn't exist

ORA-03113: end-of-file on communication channel

Process ID:

Session ID: 430 Serial number: 213

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_asmb_361.trc:

ORA-15064: communication failure with ASM instance

ORA-03135: connection lost contact

Process ID:

Session ID: 289 Serial number: 879

NOTE: deferred map free for map id 421843

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_asmb_361.trc:

ORA-15064: communication failure with ASM instance

ORA-03135: connection lost contact

Process ID:

Session ID: 289 Serial number: 879

Some DDE async actions failed or were cancelled

ASMB (ospid: 361): terminating the instance due to error 15064

Tue Jan 29 16:39:23 2013

System state dump requested by (instance=1, osid=361 (ASMB)), summary=[abnormal instance termination].

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_pr00_648.trc:

ORA-00313: open failed for members of log group 32 of thread 2

ORA-00312: online log 32 thread 2: '+DATA/sanddb/standbylog/stdbyredo12.log'

ORA-17503: ksfdopn:2 Failed to open file +DATA/sanddb/standbylog/stdbyredo12.log

ORA-03113: end-of-file on communication channel

Process ID:

Session ID: 430 Serial number: 213

System State dumped to trace file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_diag_321.trc

Tue Jan 29 16:39:23 2013

opiodr aborting process unknown ospid (579) as a result of ORA-1092

Tue Jan 29 16:39:23 2013

opiodr aborting process unknown ospid (583) as a result of ORA-1092

Tue Jan 29 16:39:23 2013

ORA-1092 : opitsk aborting process

Tue Jan 29 16:39:23 2013

ORA-1092 : opitsk aborting process

NOTE: Deferred communication with ASM instance

Errors in file /u01/app/oracle/diag/rdbms/sanddbdg/sanddb/trace/sanddb_pr00_648.trc:

ORA-01041: internal error. hostdef extension doesn't exist

NOTE: deferred map free for map id 371853

Dumping diagnostic data in directory=[cdmp_20130129163923], requested by (instance=1, osid=361 (ASMB)), summary=[abnormal instance termination].

Instance terminated by ASMB, pid = 361

TRACE文件AWK分析后：

Starting Systemstate 1

..............................................................................

.....................................

Ass.Awk Version 1.0.9 - Processing sanddb_ora_32504.trc

System State 1

~~~~~~~~~~~~~~~~

1:

2:  0: waiting for 'pmon timer'

3:  0: waiting for 'latch free'        [Latch 60010d08]

4:  0: waiting for 'VKTM Logical Idle Wait'

5:  0: waiting for 'rdbms ipc message'

6:  0: waiting for 'DIAG idle wait'

7:  0: waiting for 'rdbms ipc message'

8:  0: waiting for 'DIAG idle wait'

9:  0: waiting for 'rdbms ipc message'

10: 0: waiting for 'rdbms ipc message'

11: 0: waiting for 'rdbms ipc message'

12: 0: waiting for 'rdbms ipc message'

13: 0: waiting for 'smon timer'

14: 0: waiting for 'rdbms ipc message'

15: 0: waiting for 'rdbms ipc message'

16: 0: waiting for 'ASM file metadata operation'[Latch 601098b8]

17: 0: waiting for 'latch: shared pool'[Latch 601098b8]

18: 0: waiting for 'rdbms ipc message'

19:

20:

21: 0: waiting for 'wait for unread message on broadcast channel'

22: 0: waiting for 'SQL*Net message from client'

23:                                    [Latch 6000a018]

24: 0: waiting for 'class slave wait'

25: 0: waiting for 'rdbms ipc message'

26: 0: waiting for 'rdbms ipc message'

27: 0: waiting for 'rdbms ipc message'

28: 0: waiting for 'rdbms ipc message'

29: 0: waiting for 'rdbms ipc message'

30: 0: waiting for 'class slave wait'

31: 0: waiting for 'RFS dispatch'      [Enqueue CF-00000000-00000000]

32: 0: waiting for 'RFS dispatch'      [Enqueue CF-00000000-00000000]

33: 0: waiting for 'class slave wait'

34: 0: waiting for 'RFS dispatch'

Cmd: Select

35: 0: waiting for 'RFS dispatch'

36: 0: waiting for 'SQL*Net message from client'

37: 0: waiting for 'SQL*Net message from client'

38: 0: waiting for 'buffer busy waits' (0x5,0x4b3,0x2a)

Cmd: Select

39: 0: waiting for 'SQL*Net message from client'

40: 0: waiting for 'class slave wait'

41: 0: waiting for 'Disk file operations I/O'

42: 0: waiting for 'parallel recovery slave next change'

43: 0: waiting for 'parallel recovery slave next change'

44: 0: waiting for 'parallel recovery slave next change'

45: 0: waiting for 'latch: shared pool'[Latch 601098b8]

46: 0: waiting for 'class slave wait'

47: 0: waiting for 'SQL*Net message from client'

48:                                    [Latch 6000a018]

49: 0: waiting for 'SQL*Net message from client'

50: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

51: 0: waiting for 'latch: shared pool'[Latch 601098b8]

52: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

53: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

54: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

55:                                    [Latch 601098b8]

56:                                    [Latch 601098b8]

57: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

58:                                    [Latch 601098b8]

59: 0: waiting for 'SQL*Net message from client'

60: 0: waiting for 'buffer busy waits' (0x5,0x4b3,0x2a)

Cmd: Select

61:

62: 0: waiting for 'latch: shared pool'[Latch 601098b8]

63: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

64: 0: waiting for 'latch: shared pool'[Latch 601098b8]

65: 0: waiting for 'library cache load lock'

66: 0: waiting for 'library cache load lock'

67: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

68: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

69: 0: waiting for 'library cache: mutex X'

70: 0: waiting for 'latch: shared pool'[Latch 601098b8]

71:

Cmd: Select

72: 0: waiting for 'library cache: mutex X'

73: 0: waiting for 'latch: shared pool'[Latch 601098b8]

74: 0: waiting for 'latch: shared pool'[Latch 601098b8]

75: 0: waiting for 'library cache load lock'

76: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

77: 0: waiting for 'buffer busy waits' (0x5,0x4b3,0x2a)

Cmd: Select

78: 0: waiting for 'latch: shared pool'[Latch 601098b8]

79: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

80: 0: waiting for 'latch: shared pool'[Latch 601098b8]

Cmd: Select

81:                                    [Latch 601098b8]

82:                                    [Latch 601098b8]

83:                                    [Latch 601098b8]

84:                                    [Latch 601098b8]

85:                                    [Latch 601098b8]

86:                                    [Latch 601098b8]

87:                                    [Latch 6000a018]

88:                                    [Latch 6000a018]

89:                                    [Latch 6000a018]

90:                                    [Latch 601098b8]

91:                                    [Latch 60010d08]

92:                                    [Latch 60010d08]

93:                                    [Latch 60010d08]

94:                                    [Latch 60010d08]

95:                                    [Latch 60010d08]

96:                                    [Latch 60010d08]

97:                                    [Latch 60010d08]

98:                                    [Latch 601098b8]

99:                                    [Latch 60010f38]

100:                                   [Latch 60010f38]

101:                                   [Latch 60010f38]

102:                                   [Latch 60010f38]

103:                                   [Latch 60010f38]

104:                                   [Latch 60010f38]

105:                                   [Latch 60010f38]

106:                                   [Latch 60010f38]

107:                                   [Latch 60010f38]

108:                                   [Latch 60010f38]

109:                                   [Latch 60010f38]

110:                                   [Latch 60010f38]

111:                                   [Latch 60010f38]

112:                                   [Latch 60010f38]

113:                                   [Latch 60010f38]

114:                                   [Latch 60010f38]

115:                                   [Latch 60010f38]

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 60010d08    ??? Blocker

Latch 601098b8    ??? Blocker

Latch 6000a018    ??? Blocker

Enqueue CF-00000000-00000000    12: 0: waiting for 'rdbms ipc message'

Enqueue CF-00000000-00000000    41: 0: waiting for 'Disk file operations I/O'

Latch 60010f38    ??? Blocker

Object Names

~~~~~~~~~~~~

Latch 60010d08   OS process allocation

Latch 601098b8  Child shared pool

Latch 6000a018   call allocation

Enqueue CF-00000000-00000000

Latch 60010f38        holding    (efd=3) 60010f38  OS process: req

113914 Lines Processed.

各位老大给点建议，目前连接到备库做查询的有几个应用，希望能找到具体的问题进程。