ORA-00600问题排查与分析实例

昨天处理了一起ORA-00600的错误，其中也经历了各种曲折，真是雾里看花，看透了之后发现很多问题都是有原因的。起初是开发说有一个

昨天处理了一起ORA-00600的错误，其中也经历了各种曲折，真是雾里看花，看透了之后发现很多问题都是有原因的。起初是开发说有一个job运行的时候报错了，Oracle数据库版本是11.2.0.2.0

等到问题提交到我这，客户已经检查了一些信息了。但是还是没有结论。
 对于这个问题，我还是照例开始检查数据库日志。
 在那个时间段内里出现了ora-00600的错误。
Wed Jun 10 13:47:17 2015
 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_p070_1200.trc  (incident=2124332):
ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/incident/incdir_2124332/PRODB_p070_1200_i2124332.trc
 Wed Jun 10 13:48:04 2015
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.

在这个ora-00600错误前后又碰到了几个奇怪的ora错误,我把错误日志按照时间先后来排列一下。

Thread 1 advanced to log sequence 82829 (LGWR switch)
  Current log# 1 seq# 82829 mem# 0: /dbccbPR1/oracle/PRODB/redolog_A1/redo/redo01A.log
  Current log# 1 seq# 82829 mem# 1: /dbccbPR1/oracle/PRODB/redolog_B1/redo/redo01B.log
 Archived Log entry 82894 added for thread 1 sequence 82828 ID 0xb8c6d509 dest 1:
 Wed Jun 10 07:10:17 2015
ORA-00060: Deadlock detected. More info in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_ora_18508.trc.
 Wed Jun 10 07:10:21 2015
ORA-00060: Deadlock detected. More info in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_ora_18593.trc.
 Wed Jun 10 07:12:14 2015
ORA-00060: Deadlock detected. More info in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_ora_16505.trc.
 Wed Jun 10 07:26:54 2015
ORA-00060: Deadlock detected. More info in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_ora_18558.trc.
 Wed Jun 10 07:52:47 2015
 Thread 1 advanced to log sequence 82830 (LGWR switch)
  Current log# 2 seq# 82830 mem# 0: /dbccbPR1/oracle/PRODB/redolog_A2/redo/redo02A.log
  Current log# 2 seq# 82830 mem# 1: /dbccbPR1/oracle/PRODB/redolog_B2/redo/redo02B.log

 Wed Jun 10 12:09:40 2015
 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x210] [PC:0x193F4DF, kxfpqrclb()+71] [flags: 0x0, count: 1]
 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_p135_15651.trc  (incident=2116204):
ORA-07445: exception encountered: core dump [kxfpqrclb()+71] [SIGSEGV] [ADDR:0x210] [PC:0x193F4DF] [Address not mapped to object] []
ORA-10382: parallel query server interrupt (reset)
 Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/incident/incdir_2116204/PRODB_p135_15651_i2116204.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Jun 10 12:09:41 2015
 Dumping diagnostic data in directory=[cdmp_20150610120941], requested by (instance=1, osid=15651 (P135)), summary=[incident=2116204].
 Wed Jun 10 12:09:44 2015
 Sweep [inc][2116204]: completed
 Sweep [inc2][2116204]: completed
 Wed Jun 10 12:09:59 2015

 Wed Jun 10 13:46:27 2015
 Archived Log entry 82967 added for thread 1 sequence 82901 ID 0xb8c6d509 dest 1:
 Wed Jun 10 13:47:17 2015
 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_p070_1200.trc  (incident=2124332):
ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/incident/incdir_2124332/PRODB_p070_1200_i2124332.trc
 Wed Jun 10 13:48:04 2015

 Archived Log entry 82993 added for thread 1 sequence 82927 ID 0xb8c6d509 dest 1:
 Wed Jun 10 14:38:53 2015
 Errors in file /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/trace/PRODB_ora_15548.trc  (incident=2110436):
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmucalm coll)
 Incident details in: /opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/PRODB/incident/incdir_2110436/PRODB_ora_15548_i2110436.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.

为了更加清晰，我把对应的ORA错误和数据库负载联系在一起。
对于deadlock的错误，很可能是应用死锁造成的，简单查看了下trace日志，做了基本确认，就交给开发去分析这部分了。
从后续的错误情况来看，似乎和后续的问题没有直接关系。我们暂时先放下这个deaklock的错误。所以在图中没有标注出来。
 其它三个都用红色标注出来。可以看出在负载开始增加的几个时间点里，依次发生了几个ORA错误。