ORA-00600: 内部错误代码, 参数: [kdsgrp1]

今天早上，数据库报了ora 00600的错误，错误代码如下：

ORA-00600 内部错误代码, 参数 [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in apporaclediagrdbmsorcl3orclincidentincdir_23108orcl_ora_20767_i23108.trc
Fri Apr 18 085355 2014
Sweep [inc][23108] completed
Sweep [inc2][23108] completed
Fri Apr 18 085355 2014
Trace dumping is performing id=[cdmp_20140418085355]

继续查找trace文件里面的内容：

Trace file /app/oracle/diag/rdbms/orcl3/orcl/trace/orcl_ora_20767.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1
System name:	Linux
Node name:	linuxdb103
Release:	2.6.32-358.el6.x86_64
Version:	#1 SMP Fri Feb 22 00:31:26 UTC 2013
Machine:	x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 20767, image: oracle@linuxdb103


*** 2014-04-18 05:55:24.351
*** SESSION ID:(283.47337) 2014-04-18 05:55:24.351
*** CLIENT ID:() 2014-04-18 05:55:24.351
*** SERVICE NAME:(SYS$USERS) 2014-04-18 05:55:24.351
*** MODULE NAME:(JDBC Thin Client) 2014-04-18 05:55:24.351
*** ACTION NAME:() 2014-04-18 05:55:24.351
 
--------Dumping Sorted Master Trigger List --------
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_TITLEXTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_XTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_TAGXTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_AUTHORXTC
--------Dumping Trigger Sublists --------
 trigger sublist 0 :
 trigger sublist 1 :
 trigger sublist 2 :
 trigger sublist 3 :
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_TITLEXTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_XTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_TAGXTC
Trigger Owner : REPORT
Trigger Name : DR$REPORTSEARCH_AUTHORXTC
 trigger sublist 4 :
* kdsgrp1-1: *************************************************

*** 2014-04-18 08:53:45.414
            row 0x070ea840.f continuation at
            0x070ea840.f file# 28 block# 960576 slot 15 not found

应该是触发器引发的错误

MOS上关于这个600错误的解决方法：

Purpose

This document discusses the ora-600 [kdsgrp1] error, its possible causes and the work around solutions that can be tried.

Troubleshooting Steps

The ora-600 [kdsgrp1] error is thrown when a fetch operation fails to find the expected row. The error is hit in memory and so may be a memory only error or an error that results from corruption on disk.

This error may indicate (but is not restricted to) any of the following conditions:

• Lost writes
• Parallel DML issues
• Index corruption
• Data block corruption
• Consistent read [CR] issues 
• Buffer cache corruption  

A full list of known issues is given in
Note 285586.1 - ORA-600 [kdsgrp1]
Each bug has a short description that indicates the circumstances where it is hit. The bug list can be shortened by selecting your database release to show only those issues that may affect you.

This issue may be intermittent or it may persist until the underlying disk level corruption is fixed. Intermittent issues are likely to be memory based (however intermittent access to the corruption can be confused with intermittent memory issues).

Common Work Around Solutions

If the issue is in memory only we can try to immediately resolve the issue by flushing the buffer cache but remember to consider the performance impact on production systems:

 

alter system flush buffer_cache;

 

If we have an intermittent consistent read issue we can try disabling rowCR which is an optimization to reduce consistent-read rollbacks during queries by setting _row_cr=FALSE in the initialization files. However, this could lead to performance degradation of queries. Please check the ratio of the two statistics "RowCR hits"/"RowCR attempts" to determine whether the workaround is to be used.

If this is a result of index corruption then we can drop and rebuild the index. Note that this will require a maintenance window on production systems.

Root Cause Determination
Now lets look at how we discover the root cause of the problem: the first step in finding the root cause of this issue is to inspect the generated trace file. The ora-600 will generate both a trace file in the trace directory and an incident file under the incident id within the incident directory.
The top part of the trace file tells us the SQL that was being run when the error was hit:

 

----- Current SQL Statement for this session (sql_id=9mamr7xn4wg7x) -----
 

 

This immediately shows us the data objects that were accessed. Searching the trace file for the text string 'Plan Table' will locate the SQL execution plan that is dumped within this trace file. For a persistent issue this allows us to determine which indexes have been accessed and so identify indexes that should be validated to check for block corruption:

 

SQL> analyze index scott.pk_dept validate structure online;

Index analyzed.

 

An other approach we can take is to use the file and block information contained in the trace file. At the top of the trace file we will find information on the block where the corruption was found:

 

*** SESSION ID:(3202.5644) 2011-03-19 04:12:16.910
row 07c7c8c7.a continuation at
file# 31 block# 510151 slot 11 not found

This information can be used to identify the object details in dba_extents:

 

Select owner, segment_name, segment_type, partition_name,tablespace_name
From dba_extents
Where relative_fno = <file id>
And <block#> between block_id and (block_id+blocks-1);

We can then validate this object, for example a table and all it's indexes:

 

Analyze table scott.dept validate structure cascade online;

 

Remember that we may be dealing with a permanent corruption that is not located in the object blocks themselves. Examples of this include:

• Dictionary corruption issue from transportable tablespace operations: check dba_tablespaces to see if the tablespace has been plugged in.
• Lost writes in ASM diskgroup mirrors - most likely to be seen when there is heavy IO and disk resync activity. To check this run dbms_diskgroup.checkfile to detect mirror discrepancies

If analyze reports no corruption then check if there are any chained rows on the table. If these exist then we may have an undetected corruption and the issue should reproduce whenever the SQL is run. Exporting the table will also detect this issue.

If analyze and exporting the table (in the presence of chained rows) both report no errors then this should be considered a consistent read issue.

Once you understand the nature of the problem you can review the list of known bugs and determine which one matches your condition. If you cannot determine which issue is affecting you then open a service request with Oracle Support and upload the RDBMS and ASM (if applicable)instance alert logs for all nodes, any trace and incident files generated and a full description of the nature of the problem.

 

查找出问题块所在的段：

SYS@orcl>Select owner, segment_name, segment_type, partition_name,tablespace_name 
  2  From dba_extents 
  3  Where relative_fno = 28
  4  And 960576 between block_id and (block_id+blocks-1);
SYS@orcl>/
OWNER      SEGMENT_NAME         SEGMENT_TYPE       PARTITION_NAME       TABLESPACE_NAME
---------- -------------------- ------------------ -------------------- ------------------------------
REPORT     KM_REPORTSEARCH      TABLE                                   REPORT