一个朋友的rac数据库遇到一个问题,报错ora-00600 [kddummy_blkchk],开始以为是普通的坏块,检查
之后发现只是的,完整的错误如下:
同如下sql去查询和dbv检查都是不行的:
在老熊的指点下,我翻了去年的笔记,才发现了:
这里我们先来回顾一下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里面的,查询当然也就查不到了,如下:
下面我这里来手工模拟下这个ora-00600错误。
这里用bbed来适当破坏一下file 5的第3个block,其实破坏第3~8个block都是一样的效果。
block破坏完成以后,启动数据库。
我们可以看到,虽然说该block有问题,但是针对该block的查询是正常的,但是涉及到dml操作就会报错,因为
需要alloacion 空间,分配空间就需要去读和更改这几个bitmap block。
当然,处理方式也就不难了,我们可以直接把datafile上面的对象move到其他表空间,然后将这个表空间drop或
datafile drop即可。
这里需要说明的是,这些位图block出问题,是不能通过dbms_repair包去进行fix的。
之后发现只是的,完整的错误如下:
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
SQL> quit
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[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@dbssvr-a ~]$
复制
在老熊的指点下,我翻了去年的笔记,才发现了:
这里我们先来回顾一下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
SQL>
复制
下面我这里来手工模拟下这个ora-00600错误。
SQL> conn roger/roger
Connected.
SQL> create table t (a int)l
2
SQL>
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 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> 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的。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。