ORA-600 [kdsgrp1]的解决

ORA-600 [kdsgrp1]的解决

今天一个同事执行一个sql，老报ORA-600 [kdsgrp1]的错误，在metallink查找这个错误，和大家共享一下，并有如下的疑问
和大家共同探讨一下：
   1，在这个文档中，提到了row piece的名词，这个如何解释，google以下，
      我们知道，row是由一个或多个piece组成的,下面是一些基本的规则
     （1）如果columns <256,且能在一个block中存储,那么只有一个 piece
        （2） 如果columns>=256,且能在一个block存储,那么每行的第256列以后形成的piece会与1-255列组成的piece　
        链接在一起,     这种情况叫做intra-blockchaining; 即跨内链接
     （3）如果不能在一个block中存储，那么就会有多个pieces chained在一起.每个piece在一个block中;
         或者原来以一个piece存储在一个block中，后来因update,导致在一个block中存储不下，必须跨块存储;
         这种情况是跨块链接不管是块内链接还是块间链接,piece之间都是通过piece的rowid来连接的,实际上相当于一个单向链表
     这个解释可以吧？
  2，如果1成立，那我就想那个场合会导致这个ORA-600 [kdsgrp1]错误出现，我的猜想如下：
      （1）如果有一个表数据存在跨块存储的现象，假设是block1和block2，block1第100行数据，包含了一个rowid，这个rowid指向block2的一行；
       （2）现在用rman备份，然后恢复到另外一台机器，这个时候每行数据的rowid的发生了变化（会发生变化吗？请大家指教），但是block1的第100行数据纪录的rowid没有变化，当访问到这个数据块时，这个rowid指向的数据不存在了，所以报ORA-600 [kdsgrp1]

文档如下：
Subject:  ORA-600 [kdsgrp1]
  Doc ID:  Note:468883.1 Type:  TROUBLESHOOTING
  Last Revision Date:  03-MAR-2008 Status:  PUBLISHED

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
  References



--------------------------------------------------------------------------------



Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 11.1
Information in this document applies to any platform.

Purpose
The error is raised when we fail to find a row piece, i. e., the block is fine, but the row does not exist.
This error is new in 10g, in 9i we didn't raise this error and the corruption was unnoticed.


We raise ORA-600[kdsgrp1] assert during table/index full scans if a row piece is not found.
The error is raised only if Event 10231 or SKIP_CORRUPT_BLOCKS is not set.

/* Check table number, whether the row slot exists, whether row exists */
/* if row doesn't exist, and neither 'skip corrupt block' nor
** 'user rowid' bits are set, then raise error kdsgrp1.


Note: the block is good and because of that DBV, RMAN, analyze will not fail.
Last Review Date
November 28, 2007
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
This error can be eliminated by skipping the "corruption", however
before doing anything like this you should verify if there is actually any data in the
referenced blocks that are getting errors and will be skipped.
Please involve Oracle Support to see if it's possible to extract the data.


Trace file generated by ora-600[kdsgrp1] will show at the beginning something similar to:

*** 2007-04-26 19:53:57.671
*** SERVICE NAME HARTLEY) 2007-04-26 19:53:57.671
*** SESSION ID 304.20) 2007-04-26 19:53:57.671
            row 0826817f.ffffffff continuation at
            file# 32 block# 2523519 slot 0 not found
**************************************************

Please dump the block in order to retrieve the data:

SQL> alter system dump datafile 32 block 2523519;

WORKAROUND:


In order to skip the error one of these two w/a can be used:

1) set event 10231 and export the good rows or use 'create table as select ..'

        Setting the event in a Session
        ~~~~~~~~~~~~~~~~~~~

SQL> alter session set events '10231 trace name context forever, level 10';
SQL> create table salvage_table as select *  from corrupt_table;

        Setting the event at Instance level
        ~~~~~~~~~~~~~~~~~~~~~~

        Edit your init.ora add the following event and bounce database:
   

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

        export the table or use 'create table .. as select ..'

or

2) run DBMS_REPAIR.SKIP_CORRUPT_BLOCKS() procedure on the table

        Connect as a SYSDBA user and mark the table as needing to skip corrupt blocks thus:

SQL> execute dbms_repair.skip_corrupt_blocks('','');

       After this you can export the table or run 'create table .. as select ..'.


You can find the lost rows from the corrupted blocks by comparing the corrupted table with the new table.
Once you have the rowid of the skipped rows you can use DBMS_ROWID package to find the file# and block#, and dump the block to retrieve the data:


SQL> alter system dump datafile block ; /** a trace file will be generated under udump

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/563971/viewspace-627124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/563971/viewspace-627124/