ORA-600 [6749]/ORA-00600: [kdsgrp1]

北柏

os：ol linux
db：ora 10201

这是一个自己的测试环境，昨天要做测试时，打开db时发现 后台报错，写了3g的 trace日志，导致audit 日志不能写，后来观察alert日志后发现如下：

alert。log：
Tue Dec  3 23:01:50 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_22278.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 00:01:09 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_22781.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 01:00:06 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_23282.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 02:00:48 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_23801.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 03:00:26 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_24303.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 04:00:04 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_24800.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 05:00:46 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_25642.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12620597], [387], [], [], [], []
Wed Dec  4 06:00:20 2013
Errors in file /oracle/admin/lixora/bdump/lixora_j000_26124.trc:


后来在mos上确定了以下为bug：
问题确定为Bug 6127434
ORA-600 [6749] Occurring on SYSMAN.MGMT_METRICS_RAW (Doc ID 467439.1)
按照文档的solution操作如下：


SQL> select * from v$version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> SELECT tablespace_name, segment_type, owner, segment_name  
  2      FROM dba_extents  
  3      WHERE file_id = 3  
  4      and 22950 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSAUX                         TABLE              SYSMAN
MGMT_LICENSES


SQL> c/22950/37685
  4*     and 37685 between block_id AND block_id + blocks - 1
SQL> r
  1  SELECT tablespace_name, segment_type, owner, segment_name
  2      FROM dba_extents
  3      WHERE file_id = 3
  4*     and 37685 between block_id AND block_id + blocks - 1

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSAUX                         TABLE              SYSMAN
SYS_IOT_OVER_49869


SQL> select table_name, iot_name from all_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_4478              RULE_SET_PR$
SYS_IOT_OVER_4484              RULE_SET_IOT$
SYS_IOT_OVER_4488              RULE_SET_ROP$
SYS_IOT_OVER_5082              AQ$_SCHEDULER$_JOBQTAB_G
SYS_IOT_OVER_5168              AQ$_SCHEDULER$_EVENT_QTAB_G
SYS_IOT_OVER_7157              AQ$_KUPC$DATAPUMP_QUETAB_G
SYS_IOT_OVER_8689              AQ$_AQ$_MEM_MC_G
SYS_IOT_OVER_8799              AQ$_ALERT_QT_G
SYS_IOT_OVER_9692              RECENT_RESOURCE_INCARNATIONS$
SYS_IOT_OVER_10099             AQ$_WM$EVENT_QUEUE_TABLE_G
SYS_IOT_OVER_35428             AQ$_SYS$SERVICE_METRICS_TAB_G

TABLE_NAME                     IOT_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_40409             EXF$IDXSECOBJ
SYS_IOT_OVER_40883             DR$SQE
SYS_IOT_OVER_40928             DR$PARALLEL
SYS_IOT_OVER_42445             RLM$ERRCODE
SYS_IOT_OVER_42452             RLM$RULESET
SYS_IOT_OVER_42459             RLM$PRIMEVTTYPEMAP
SYS_IOT_OVER_42462             RLM$EQUALSPEC
SYS_IOT_OVER_42481             RLM$JOBQUEUE
SYS_IOT_OVER_42484             RLM4J$EVTSTRUCTS
SYS_IOT_OVER_42487             RLM4J$RULESET
SYS_IOT_OVER_42490             RLM4J$ATTRALIASES

TABLE_NAME                     IOT_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_49766             AQ$_MGMT_NOTIFY_QTABLE_G
SYS_IOT_OVER_49869             MGMT_METRICS_RAW
SYS_IOT_OVER_49872             MGMT_CURRENT_METRICS
SYS_IOT_OVER_52389             ACTION_TABLE
SYS_IOT_OVER_52394             LINEITEM_TABLE
SYS_IOT_OVER_52776             AQ$_KUPC$DATAPUMP_QUETAB_D
RULE_SET_IOT$
SYS_IOT_OVER_51984             AQ$_STREAMS_QUEUE_TABLE_G
SYS_IOT_OVER_51967             AQ$_ORDERS_QUEUETABLE_G

31 rows selected.

SQL> create table SYSMAN.MGMT_METRICS_RAW_COPY
  2  as select * from SYSMAN.MGMT_METRICS_RAW;

as select * from SYSMAN.MGMT_METRICS_RAW
                        *
ERROR at line 2:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[]


问题到这里，先处理ORA-00600 [kdsgrp1], 按照ORA-600 [kdsgrp1] During Table/Index Full Scans (Doc ID 468883.1)  提供的solution 来处理坏块：
SQL> alter session set events '10231 trace name context forever, level 10';
SQL> create table salvage_table as select *  from corrupt_table;

还是包相同的错误：

SQL> alter session set events '10231 trace name context forever, level 10';

Session altered.

SQL>  create table SYSMAN.MGMT_METRICS_RAW_COPY
  2    as select * from SYSMAN.MGMT_METRICS_RAW;


  as select * from SYSMAN.MGMT_METRICS_RAW
                          *
ERROR at line 2:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[]

目前数据库是可以正常使用，但是一直在写大量的trace 。 请大家指点下解决思路。

北柏

trace ：http://pan.baidu.com/share/link?shareid=488291353&uk=1175986083

Liu Maclean(刘相兵

FYI

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.1.0 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.
Goal

The purpose of this plsql script is to create a new table based on a table that is producing errors such as ORA-8103 or ORA-1410 or ORA-1578. The script skips the blocks/rows producing those errors.

This is done when there is not option to restore the table from a backup like applying media recovery or recovering the table from an export or other source.

The first option to skip an ORA-1578 error is to use the DBMS_REPAIR script and decide to create a new table using create table as select (CTAS); however if for any reason that does not work use the plsql in this document instead.

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Fix

Run sqlplus with SYS or TABLE owner user

Example:

sqlplus '/ as sysdba'

or

sqlplus <table owner> / password

SKIP ORA-1578 ORA-8103 ORA-1410

REM Create a new table based on the table that is producing errors with no rows:

create table <new table name>
as
select *
from   <original table name>
where  1=2;

REM Create the table to keep track of ROWIDs pointing to affected rows:

create table bad_rows (row_id rowid
                      ,oracle_error_code number);
set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> is NOT NULL;

  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
     from <original table name> A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/


Notes:

Replace the next values in the plsql script by the values of the affected table: <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>
The idea is to get the rowid's from an existent index, then get all the columns from the table for each rowid and insert these rows into the new table. Using the "index" hint, allows the optimizer to choose the most appropriated index to scan the table based on the indexed column.
Make sure that the select in the plsql is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:

set autotrace trace explain
select /*+ index(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL;


Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it is required to change this, adjust the value of rows. e.g.:

rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.
If 'Total Bad Rows:' displays 0 and it is known for certain that there is a block incorrect on disk that is causing the ORA-8103, , then it means that the block is empty (no rows) and there is not data loss.
SKIP ORA-600

This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work.

If the problem is caused in an Index Organized Table (IOT) change ROWID by UROWID for the row_id column in table bad_rows.



drop table bad_rows;
create table bad_rows (row_id ROWID
                      ,oracle_error_code number);

rem Create the new empty table:

create table &&new_table
as select *
from &&affected_table
where 1=2;


set serveroutput on
declare
  n number:=0;
  bad_rows number := 0;
  error_code number;
  ora600 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora600, -600);
begin
  for i in (select rowid rid from  &&affected_table)  loop
  begin
    insert into  &&new_table
     select *
     from &&affected_table
     where rowid=i.rid;
     n:=n+1;
  exception
    when ora600 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,600);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,error_code);
     commit;
  end;
  end loop;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
  dbms_output.put_line('Total Good rows: '||n);
end;
/

北柏

Liu Maclean(刘相兵 发表于 2013-12-5 21:10
FYI

Applies to:

ths, 测试有效，谢谢刘大！