适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4  and later
复制

问题概述

SQL> select * from hsql.drop_1 where c_char1='999';
select * from hsql.drop_1 where c_char1='999'
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []
复制

问题原因

### 1. 问题模拟
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '/oradata/orcl/hsql01.dbf' size 100M autoextend off;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql;
begin
for i in 1 .. 100000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
end;
/
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1;
select dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_number from hsql.drop_1 where c_char1='999';
 OBJECT_ID    FILE_ID   BLOCK_ID ROW_NUMBER
---------- ---------- ---------- ----------
     87363          7        131        191

### 2. 创建索引并检查执行计划
SQL> create index hsql.i_idx1 on hsql.drop_1(c_char1) tablespace hsql; 
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'HSQL',tabname=>'DROP_1');
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 300 pagesize 9999
SQL> explain plan for
  2  select * from hsql.drop_1 where c_char1='999';

Explained.

SQL> select * from table(dbms_xplan.display());   

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1789415090

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DROP_1 |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C_CHAR1"='999')

14 rows selected.

SQL> 

### 3. 安装bbed工具,工具下载地址：https://www.modb.pro/doc/83490
[root@orcldb oracle]# tar -xf x_recovery.tar    <-- /home/oracle/
[root@orcldb x_recovery]# sh x_reco.sh 
pls,user Oracle user run x_rec!!!
[root@orcldb x_recovery]# su - oracle
[oracle@orcldb x_recovery]$ sh x_reco.sh 
--------------------------------------------------------------------------------
|**********************WELCOME: x_rec for Oracle*******************************| 
|1). install_bbed: bbed install one key                                        |
|2). xbbed: cp asm block to os block or reverse.                               |
|3). exit: quit the x_rec                                                      |
|*****************if you need any help contact my wechat: xidoublestar*********|  
--------------------------------------------------------------------------------
pls,input operation name:install_bbed
will install bbed one key?(y/n)y
begin install bbed...
installl bbed succeed...
--------------------------------------------------------------------------------
|**********************WELCOME: x_rec for Oracle*******************************| 
|1). install_bbed: bbed install one key                                        |
|2). xbbed: cp asm block to os block or reverse.                               |
|3). exit: quit the x_rec                                                      |
|*****************if you need any help contact my wechat: xidoublestar*********|  
--------------------------------------------------------------------------------
pls,input operation name:exit
[oracle@orcldb x_recovery]$

### 4. 删除row信息
[oracle@orcldb ~]$ cd bbed
[oracle@orcldb bbed]$ ls
l_bbed.sh  listfile.txt  par.bbed
[oracle@orcldb bbed]$ cat listfile.txt 
1 /oradata/orcl/system01.dbf 775946240                                          
2 /oradata/orcl/sysaux01.dbf 545259520                                          
3 /oradata/orcl/undotbs01.dbf 73400320                                          
4 /oradata/orcl/users01.dbf 5242880                                             
5 /oradata/orcl/test01.dbf 104857600                                            
6 /oradata/orcl/test02.dbf 104857600                                            
7 /oradata/orcl/hsql01.dbf 10485760                                             
[oracle@orcldb bbed]$ sh l_bbed.sh 
BBED> set dba 7,131
        DBA             0x01c00083 (29360259 7,131)
 BBED> p *kdbr[191]
rowdata[1925]
-------------
ub1 rowdata[1925]                           @3388     0x2c

BBED> x /rcccc    
rowdata[1925]                               @3388    
-------------
flag@3388: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3389: 0x01
cols@3390:    2

col   0[10] @3391: 999       
col   1[10] @3402: orastar   


BBED> d offset 3388 count 12
 File: /oradata/orcl/hsql01.dbf (7)
 Block: 131              Offsets: 3388 to 3399           Dba:0x01c00083
------------------------------------------------------------------------
 2c01020a 39393920 20202020 

 <32 bytes per line>

BBED> m /x 3c offset 3388
 File: /oradata/orcl/hsql01.dbf (7)
 Block: 131              Offsets: 3388 to 3399           Dba:0x01c00083
------------------------------------------------------------------------
 3c01020a 39393920 20202020 

 <32 bytes per line>

BBED> sum apply
Check value for File 7, Block 131:
current = 0xa78b, required = 0xa78b

BBED> v
DBVERIFY - Verification starting
FILE = /oradata/orcl/hsql01.dbf
BLOCK = 131

Block Checking: DBA = 29360259, Block Type = KTB-managed data block
data header at 0x7fde01e3c264
kdbchk: the amount of space used is not equal to block size
        used=7258 fsc=0 avsp=807 dtl=8088
Block 131 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> p kdbh
struct kdbh, 14 bytes                       @100     
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      269
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      556
   sb2 kdbhfseo                             @108      1363
   sb2 kdbhavsp                             @110      807
   sb2 kdbhtosp                             @112      807

BBED> assign kdbh.kdbhavsp=830
sb2 kdbhavsp                                @110      830

BBED> v
DBVERIFY - Verification starting
FILE = /oradata/orcl/hsql01.dbf
BLOCK = 131

Block 131 is corrupt
Corrupt block relative dba: 0x01c00083 (file 0, block 131)
Bad check value found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x01c00083
 last change scn: 0x0000.0010ad09 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xad090601
 check value in block header: 0xa78b
 computed block checksum: 0x19


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> sum apply
Check value for File 7, Block 131:
current = 0xa792, required = 0xa792

BBED> v
DBVERIFY - Verification starting
FILE = /oradata/orcl/hsql01.dbf
BLOCK = 131

Block Checking: DBA = 29360259, Block Type = KTB-managed data block
data header at 0x163e064
kdbchk: avsp(830) > tosp(807)
Block 131 failed with check code 6128

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> p kdbh
struct kdbh, 14 bytes                       @100     
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      269
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      556
   sb2 kdbhfseo                             @108      1363
   sb2 kdbhavsp                             @110      830
   sb2 kdbhtosp                             @112      807

BBED> assign kdbh.kdbhtosp=830
sb2 kdbhtosp                                @112      830

BBED> sum apply
Check value for File 7, Block 131:
current = 0xa78b, required = 0xa78b

BBED> v
DBVERIFY - Verification starting
FILE = /oradata/orcl/hsql01.dbf
BLOCK = 131

Block Checking: DBA = 29360259, Block Type = KTB-managed data block
data header at 0x163e064
kdbchk: space available on commit is incorrect
        tosp=830 fsc=0 stb=2 avsp=830
Block 131 failed with check code 6111

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> assign kdbh.kdbhtosp=832
sb2 kdbhtosp                                @112      832

BBED> sum apply
Check value for File 7, Block 131:
current = 0xa7f5, required = 0xa7f5

BBED> v
DBVERIFY - Verification starting
FILE = /oradata/orcl/hsql01.dbf
BLOCK = 131


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> 
复制

解决方案

### 1.  执行SQL报错
[oracle@orcldb bbed]$ sqlplus / as sysdba
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from hsql.drop_1 where c_char1='999';
select * from hsql.drop_1 where c_char1='999'
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []

SQL>

### 2. 检查db alert log
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13534.trc  (incident=9891):
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_9891/orcl_ora_13534_i9891.trc

### 3. 检查文件orcl_ora_13534.trc文件
查找关键字“not found”，获取异常对象信息

*** 2022-12-01 16:43:24.007
* kdsgrp1-1: *************************************************
            row 0x01c00083.bf continuation at
            0x01c00083.bf file# 7 block# 131 slot 191 not found  <--异常对象信息
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 191 ..... nrows: 269
kdsgrp - dump CR block dba=0x01c00083
Block header dump:  0x01c00083
 Object id on Block? Y
 seg/obj: 0x15543  csc: 0x00.10ab79  itc: 2  flg: E  typ: 1 - DATA   <--对象typ及obj
     brn: 0  bdba: 0x1c00080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.00a.0000037e  0x00c000fd.00a8.3b  --U-  269  fsc 0x0000.0010ad09
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c00083

### 4. 检查orcl_ora_13534_i9891.trc文件
查找关键字“not found”，获取执行计划信息
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | DROP_1  |     1 |    22 |     2 |  00:00:01 |
| 2   |   INDEX RANGE SCAN           | I_IDX1  |     1 |       |     1 |  00:00:01 |
-----------------------------------------------+-----------------------------------+

查找关键字“Current SQL Statement for this session (sql_id=”，获取异常SQL语句信息

*** 2022-12-01 16:39:24.925
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=czx9r5v5ysjbf) -----
select * from hsql.drop_1 where c_char1='999'

### 5. 查找异常对象
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);

或者 使用obj查找对象

SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_id=87363;

OWNER       OBJECT_NAME     OBJECT_ID DATA_OBJECT_ID
--------- ----------------  ---------- --------------
HSQL         DROP_1          87363          87363
SQL> select owner,index_name from dba_indexes where table_name='DROP_1' and owner='HSQL';

OWNER                          INDEX_NAME
------------------------------ ------------------------------
HSQL                           I_IDX1

SQL>

### 6. 分析表对象异常
Analyze table <OWNER>.<TABLE NAME> validate structure cascade online;


### 7. 内存异常恢复
The ora-600 [kdsgrp1] error is thrown when a fetch operation fails to find the expected row. 

如果只是内存异常，可以刷新buffer_cache进行恢复，但请评估对生产环境的性能影响。
alter system flush buffer_cache;

#### 8. 物理损坏恢复
SQL> alter index hsql.i_idx1 rebuild;  <--没有online时使用index信息重建索引，问题依然存在。

Index altered.

SQL> alter system flush buffer_cache;
select * from hsql.drop_1 where c_char1='999';

System altered.

SQL> select * from hsql.drop_1 where c_char1='999'
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []


SQL> alter index hsql.i_idx1 rebuild online; <--使用online时，使用table信息重建索引，可以修改该问题。

Index altered.

SQL> alter system flush buffer_cache;
select * from hsql.drop_1 where c_char1='999';

System altered.

SQL> 
no rows selected

SQL> select * from hsql.drop_1 where c_char1='998';

C_CHAR1    C_CHAR2
---------- ----------
998        orastar

### 9. 再次确认table是否正常
Analyze table <OWNER>.<TABLE NAME> validate structure cascade online;

复制

参考文档

Causes and Solutions for ora-600 [kdsgrp1] (Doc ID 1332252.1)
复制