Detailed Steps for Troubleshooting ORA-00600 [kdsgrp1] (文档 ID 1492150.1)

In this Document

APPLIES TO:

PURPOSE

This document provides detailed steps for troubleshooting the ORA-00600 [kdsgrp1] internal error.It contains information from other documents including the documents shown in the references section.

Instructions for the Reader

First, please review Document 1332252.1 - Causes and Solutions for ora-00600 [kdsgrp1]. It is an excellent document which explains the error and possible causes/solutions for this particular error.
 

TROUBLESHOOTING STEPS

The following troubleshooting steps can be used to analyze the ORA-600 [kdsgrp1] error:

1. Identify the object causing the ORA-600 [kdsgrp1] to be raised
   
   For example, the trace file with ORA-00600 [kdsgrp1] contains the following information::

   * kdsgrp1-1: *************************************************
               row 0x0744877d.52 continuation at
               0x0744877d.52 file# 29 block# 296829 slot 82 not found

   
   So in this case the ORA-600 is raised in file # 29 and block # 296829.
   
   The following query can be used to retrieve the object information:

   SELECT segment_type, owner, segment_name
   FROM dba_extents
   WHERE file_id = <file number>
   AND <block number> BETWEEN block_id and block_id+blocks-1;

   
   So in the given example, issue:

   CONNECT / AS SYSDBA
   
   SELECT segment_type, owner, segment_name
   FROM dba_extents
   WHERE file_id = 29
   AND 296829 BETWEEN block_id AND block_id+blocks-1;

2. Analyze the object identified in step 1 and check for table/index mismatches and/or corruptions
   
   The SQL statements that can be used for this are:

   ANALYZE TABLE <table name> VALIDATE STRUCTURE CASCADE;

   
   and:

   ANALYZE TABLE <table name> VALIDATE STRUCTURE CASCADE ONLINE;

    

   NOTE:
   Analyze with the ONLINE option enables the Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency. With the OFFLINE option (which is the default setting) this setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries being run against the object.

3. If the above ANALYZE command fails, then check the table and indexes without using 'cascade'
   option as shown below:
    
   • For tables use:

     ANALYZE TABLE <table name> VALIDATE STRUCTURE;

     
     or:

     ANALYZE TABLE <table name> VALIDATE STRUCTURE ONLINE;

   • For indexes use:

     ANALYZE INDEX <table name> VALIDATE STRUCTURE;

     
     or:

     ANALYZE INDEX <table name> VALIDATE STRUCTURE ONLINE;

4. If ANALYZE fails and the object is corrupt, then:
   1. For non-SYS owned objects, drop and recreate the object when possible:
      1. For an user index corruption, drop and recreate the index.
      2. For Advanced Queue objects we cannot simply drop a queue index owned by the SYSTEM user.
         You can recreate the offending queue tables. If you need the messages that are in the queue then you need to take backup of the data. Re-enqueue the messages back to the queue after a recreate of the queue table
         or:
         Reorganize/move the queue objects:
         • For Oracle versions 10.2.0.5 and above, see Document 1410195.1 - How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION
         • For Oracle version below 10.2.0.5.0, see Document 304522.1 - How to Move Queue Tables without using the Export or Datapump Utilities
      3. Analyze fails with error ORA-01499

         ANALYZE TABLE <table name> VALIDATE STRUCTURE CASCADE ONLINE;
         ERROR at line 1:
         ORA-01499: table/index cross reference failure - see trace file

         
         and the trace file generated shows:

         tsn: 8  rdba: 0x0443e30e  seg/obj: 0xfd6b

          
         • To identify the offending object based on tsn and rdba:
           See Document 1499.1 - OERR: ORA-1499 table/Index Cross Reference Failure - see trace file
           tsn: Tablespace Number where the INDEX is stored.
           rdba: Relative data block address of the INDEX segment header.
            

           CONNECT / AS SYSDBA
           SELECT owner, segment_name, segment_type, partition_name
           FROM DBA_SEGMENTS
           WHERE header_file = (SELECT file#
                                FROM v$datafile
                                WHERE rfile# = dbms_utility.data_block_address_file(to_number('0443e30e','XXXXXXXX'))
                                AND ts#= 8)
           AND header_block = dbms_utility.data_block_address_block(to_number('0443e30e','XXXXXXXX'));

         • To identify the offending object based on seg/obj:
           Note the value of the seg/obj field (i.e. 0xfd6b) and translate this to a decimal number (in this case: 64875).
           Look up the object name in the data dictionary:

           CONNECT / AS SYSDBA
           SELECT owner, object_name, object_type
           FROM dba_objects
           WHERE object_id = 64875;

         Check if the issue is solved after drop/create the index.
         1. Analyze fails with error ORA-1499 /ORA-8102 - Index inconsistency
            For INSERT statements on INTERVAL partitioned tables, see:
            Document 10633840.8 - Bug 10633840 - ORA-1502 on insert statement on INTERVAL partitioned table. ORA-8102 / ORA-1499 Index inconsistency
            and:
            Bug:10633840 - ORA-1502 WHILE RUNNING INSERT STATEMENT ON PARTITIONED TABLE
            
            This issue is fixed in 11.2.0.2 PSU 7.
         2. Analyze fails with error OERI[kdsgrp1]/ORA-1499 - Corrupt index
            After PDML executed in serial, see:
            Document 9469117.8 - Bug 9469117 - Corrupt index after PDML executed in serial. Wrong results. OERI[kdsgrp1]/ORA-1499 by analyze
            and:
            Bug:9469117 - INDEX WITH DELETED KEYS - WRONG RESULTS. OERI [KDSGRP1] / ORA-1499 BY ANALYZE
            
            This issue is fixed in the 11.2.0.2 patchset.
         3. Analyze fails with ORA-1499, Corruption with self-referenced row in a MSSM (Manual Segment Space Management) tablespace. Wrong results/ORA-600 [6749]/ORA-8102:
            See:
            Document 7705591.8 - Bug 7705591 - Corruption with self-referenced row in MSSM tablespace. Wrong Results / OERI[6749] / ORA-8102
            and:
            Bug:7705591 - CORRUPTED NRID IN A CHAINED ROW POINTING TO ITSELF. ORA-600 [6749] AND ORA-8102
            
            This issue is fixed in the 11.2.0.2 patchset.
            
            You can verify the use of MSSM in the SEGMENT_SPACE_MANAGEMENT column of DBA_TABLESPACES.
         4. Analyze fails with errors ORA-1499, ORA-8102, ORA-600 [kdsgrp1] - Bitmap index/table mismatch, when querying a table with bitmap indexes:
            See:
            Document 13146182.8 - Bug 13146182 - ORA-1499 ORA-8102 ORA-600 [kdsgrp1] Bitmap Index / Table mismatch
            and:
            Bug:13146182 - ORA-600 [KDSGRP1] ERROR WHEN QUERYING A TABLE WITH BITMAP INDEXES
            
            This issue is fixed in the 12.1.
   2. If a SYS object, please perform a database recovery or log a service request.
      
      You can use RMAN's BACKUP CHECK LOGICAL VALIDATE DATABASE command to check for any logical or physical corruption. It does not actually make a backup, but will do the block checking.
      
      For details, see Document 472231.1 How to identify all the Corrupted Objects in the Database with RMAN

5. If analyze is successful and reports no corruption:
    
   1. If running Oracle release 11.1.0.7.0 or below:
      
      Apply the fix for bug 8720802, fixed in 11.2.0.2.
      For details, see:
      Document 8720802.8 - Bug 8720802 - Add check for row piece pointing to itself (db_block_checking,dbv,rman,analyze)
      and:
      unpublished Bug:8720802 - ADD CHECK FOR CONTINUED ROW PIECE POINTING TO ITSELF
      
      Without the fix of bug 8720802 tools like DBVERIFY, RMAN, and ANALYZE don't detect this logical corruption.
      
      An example is the above mentioned bug 7705591.
       
      1. If table and index analysis doesn't report errors and the table doesn't use the LONG column datatype then see:
         Document 8771916.8 - Bug 8771916 - OERI [kdsgrp1] during CR read
         and:
         unpublished Bug:8771916 - ORA-00600 [KDSGRP1] WHEN DOING AN UPDATE
         
         This issue is fixed in the 11.2.0.2 patchset.
         
         Workaround is to set the "_row_cr"=FALSE instance parameter.
          

         Note:
         Disabling rowCR (which is an optimization to reduce consistent-read rollbacks during queries) by setting "_row_cr"=FALSE in the instance could cause performance degradation of queries - the statistics "RowCR hits"/"RowCR attempts" can help show if this workaround may be detrimental to performance. So setting this parameter must be verified in a test environment before applying it in production.

      2. If table and index analysis doesn't report errors and the table uses a LONG column datatype then see:
         Document 735435.1 - ORA-600 [kdsgrp1] Generated When Table Contains a LONG
         and:
         Bug:6445948 - ORA-600 [KDSGRP1] HAPPENING => TABLE AND INDEX ANALYZES FINE
         
         This issue is fixed in 11.2.0.1.
         
         Workaround:
         • Set the "_row_cr"=FALSE instance parameter.
         • Drop and recreate the index.
            

         Note:
         Disabling rowCR (which is an optimization to reduce consistent-read rollbacks during queries) by setting "_row_cr"=FALSE in the instance could cause performance degradation of queries - the statistics "RowCR hits"/"RowCR attempts" can help show if this workaround may be detrimental to performance. So setting this parameter must be verified in a test environment before applying it in production.

   2. If your running Oracle release 11.2.0.3.0 in a RAC (Real Application Clusters) then test the problem by disabling reader bypass, by setting "_gc_bypass_readers"=FALSE on all nodes. This won't have a dramatic performance impact.
      
      Monitor the system, and if problem does not occur after setting the parameter then apply Patch:13807411 (fixed in 12.1) and remove the parameter setting.
      
      For more information, see:
      Document 13807411.8 - Bug 13807411 - ORA-600 [kcbchg1_38] using XA in RAC
      and:
      unpublished Bug:13807411 - ORA-00600[KCBCHG1_38] DURING CURRENT CLEANOUT
       
   3. Otherwise, check if there are any chained rows in the table. If these exist then we may have an undetected corruption and the issue should reproduce whenever the offending SQL statement or a Full Table Scan is run or the entire table is being exported.
      
      If there is a permanent invalid chained row, the row producing the ORA-600 [kdsgrp1] can be skipped by setting the 10231 event:

      event="10231 trace name context forever, level 10"

      
      This should be removed from the instance parameters immediately after the table reporting the ORA-600 [kdsgrp1] has been salvaged.
      
      Alternatively this event can be set at the session level, as in:

      ALTER SESSION SET EVENTS '10231 trace name context forever, level 10';

      
      For more information, see:
      Document 21205.1 - EVENT: 10231 "skip corrupted blocks on _table_scans_"
      Document 33405.1- Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231

6. If the issue is only occurring in memory you 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;

7. When feasible apply lateset patchset or one-off patches for relevant known bugs. Please see Document 285586.1- ORA-600 [kdsgrp1] for a list of known issues for your Oracle version.

8. If further assistance is needed, file a new Service Request with Oracle Global Software Support.

REFERENCES

显示更多