sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00600 [kddummy_blkchk]位图block异常导致

时间:2016-12-10 19:45   来源:Oracle研究中心   作者:网络   点击:

Oracle研究中心案例分析:运维DBA反映Oracle数据库遇到报错ORA-00600 [kddummy_blkchk],分析原因为位图block异常导致。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: ora-00600 [kddummy_blkchk] solution

一个朋友的rac数据库遇到一个问题,报错ora-00600 [kddummy_blkchk],开始以为是普通的坏块,检查之后发现只是的,完整的错误如下:

Tue Jul 31 16:09:55 CST 2012
Corrupt Block Found
         TSN = 10, TSNAME = TBLSPA_ARCH_IND
         RFN = 23, BLK = 3, RDBA = 96468995
         OBJN = 1, OBJD = -1, OBJECT = , SUBOBJECT =
         SEGMENT OWNER = , SEGMENT TYPE =
Tue Jul 31 16:10:44 CST 2012
Errors in file /oracle/product/10.2/admin/e200pro/udump/e200pro2_ora_31401.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [23], [3], [18018], [], [], [], []
Tue Jul 31 16:11:13 CST 2012
Doing block recovery for file 23 block 3
Resuming block recovery (PMON) for file 23 block 3
Block recovery from logseq 6041, block 117822 to scn 10142128311834
Tue Jul 31 16:11:13 CST 2012

同如下sql去查询和dbv检查都是不行的:

SQL> select segment_name from dba_extents where file_id = 23 and 3 between block_id and block_id+blocks-1;

no rows selected

SQL> select file_name from dba_data_files where file_id = 23;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/datafile/archind3.dbf

[oracle@dbssvr-a ~]$ dbv file = /u02/oradata/datafile/archind3.dbf blocksize=8192;

DBVERIFY: Release 10.2.0.5.0 - Production on Tue Jul 31 19:38:23 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/oradata/datafile/archind3.dbf
DBVERIFY - Verification complete

Total Pages Examined         : 1048576
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 8046
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 154
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1040376
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 1656962073 (2361.1656962073)

这里我们先来回顾一下oracle LMT情况下,datafile的物理结构
file header,bitmapped file space header,head portion of bitmapp blocks以及useful file blocks,tail portail of bitmap blocks。

从上面的查询block_id=9 我们可以看出,oracle保留了前面9个block(block 0~8)

下面我们来看看这9个block的具体情况:

block 0: OS header
block 1: datafile header
block 2: bitmapped file space header
block 3~8: Head portion of bitmap blocks

这段话来自我2010年的笔记,从上面的错误我们可以知道这个block是file 23的第3个block,也就是这里的head portion of bitmap block,这是用来位图标记的,简单的说,就是通过这几个位图块来记录整个datafile 里面extent的分配使用情况。

所以,这几个位图块是不会出现在dba_extents里面的,查询当然也就查不到了,如下:

SQL> SELECT segment_name
  2    FROM dba_extents
  3   WHERE file_id = 5
  4     AND 3 BETWEEN block_id AND block_id + blocks - 1
  5  UNION ALL
  6  SELECT segment_name
  7    FROM dba_extents
  8   WHERE file_id = 5
  9     AND 4 BETWEEN block_id AND block_id + blocks - 1
10  UNION ALL
11  SELECT segment_name
12    FROM dba_extents
13   WHERE file_id = 5
14     AND 5 BETWEEN block_id AND block_id + blocks - 1
15  UNION ALL
16  SELECT segment_name
17    FROM dba_extents
18   WHERE file_id = 5
19     AND 6 BETWEEN block_id AND block_id + blocks - 1
20  UNION ALL
21  SELECT segment_name
22    FROM dba_extents
23   WHERE file_id = 5
24     AND 7 BETWEEN block_id AND block_id + blocks - 1
25  UNION ALL
26  SELECT segment_name
27    FROM dba_extents
28   WHERE file_id = 5
29     AND 8 BETWEEN block_id AND block_id + blocks - 1;

no ROWS selected

下面我这里来手工模拟下这个ora-00600错误。

SQL> conn roger/roger
Connected.

SQL> CREATE TABLE t (a INT)l

SQL> CREATE TABLE t (a INT);

TABLE created.

SQL> INSERT INTO t VALUES(1);

1 ROW created.

SQL> /

1 ROW created.

SQL> /

1 ROW created.

SQL> commit;

Commit complete.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2  dbms_rowid.rowid_block_number(rowid) blk#
  3  FROM t;

     FILE#       BLK#
---------- ----------
         4       7484
         4       7484
         4       7484

SQL> ALTER TABLE t move tablespace roger;
TABLE altered.

SQL> ALTER USER roger DEFAULT tablespace roger;
USER altered.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2  dbms_rowid.rowid_block_number(rowid) blk#
  3  FROM t;

     FILE#       BLK#
---------- ----------
         5         12
         5         12
         5         12

SQL> !
这里用bbed来适当破坏一下file 5的第3个block,其实破坏第3~8个block都是一样的效果。

BBED> modify /x 8018 offset 183
Warning: contents of previous BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 3                Offsets:  183 to  382           Dba:0x01400003
------------------------------------------------------------------------
80180000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000

<32 bytes per line>

BBED> modify /x 2233 offset 200
Warning: contents of previous BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 3                Offsets:  200 to  711           Dba:0x01400003
------------------------------------------------------------------------
22330000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> modify /x 8888 offset 100
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 3                Offsets:  100 to  611           Dba:0x01400003
------------------------------------------------------------------------
88880000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000080 18000000 00000000 00000000
00000000 22330000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 0Oracleо0000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 3:
current = 0x755f, required = 0x755f

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 3


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   : 0
Total Blocks Influx           : 0

BBED>
BBED> d /v
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 3       Offsets:    0 to  511  Dba:0x01400003
-------------------------------------------------------
1ea20000 03004001 42f94300 00000104 l ....@.B鵆.....
5f750000 05000000 09000000 00000000 l _u..............
01000000 fff70000 00000000 00000000 l ..............
00000000 00000000 01000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 88880000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000080 18000000 00000000 l ................
00000000 00000000 22330000 00000000 l ........"3......
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................

<16 bytes per line>
block破坏完成以后,启动数据库。

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             142607592 bytes
DATABASE Buffers           20971520 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
DATABASE opened.
SQL>  SELECT COUNT(*) FROM roger.t;

  COUNT(*)
----------
         3

SQL> conn roger/roger
Connected.
SQL> INSERT INTO t SELECT object_id FROM sys.dba_objects;
INSERT INTO t SELECT object_id FROM sys.dba_objects
            *
ERROR at line 1:
ORA-00607: Internal error occurred while making a CHANGE TO a DATA block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [5], [3], [18018], [], [], [], []

SQL> ALTER TABLE t move tablespace users;
TABLE altered.

SQL>
SQL> ALTER TABLE t move tablespace roger;
ALTER TABLE t move tablespace roger
            *
ERROR at line 1:
ORA-01578: ORACLE DATA block corrupted (file # 5, block # 3)
ORA-01110: DATA file 5: '/home/ora10g/oradata/roger/roger01.dbf'


SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
         3

SQL>
我们可以看到,虽然说该block有问题,但是针对该block的查询是正常的,但是涉及到dml操作就会报错,因为需要alloacion 空间,分配空间就需要去读和更改这几个bitmap block。

当然,处理方式也就不难了,我们可以直接把datafile上面的对象move到其他表空间,然后将这个表空间drop或datafile drop即可。

这里需要说明的是,这些位图block出问题,是不能通过dbms_repair包去进行fix的。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00600 [kddummy_blkchk]位图block异常导致

原文唯一网址:http://www.oracleplus.net/arch/1386.html

Oracle研究中心

关键词:

ORA-00600

Oracle报错ORA-00600 [kddummy_blkchk]的解决办法

Oracle 位图block异常的解决办法