ora-00600 [kddummy_blkchk] solution
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
一个朋友的rac数据库遇到一个问题，报错ora-00600 [kddummy_blkchk]，开始以为是普通的坏块，检查
之后发现只是的，完整的错误如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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检查都是不行的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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里面的，查询当然也就查不到了，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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错误。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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都是一样的效果。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
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破坏完成以后，启动数据库。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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的。
3 Responses to “ora-00600 [kddummy_blkchk] solution”
太牛了，要是我想不到这个方法！
膜拜大师！
牛人，讲解得很清楚
Leave a Reply
You must be logged in to post a comment.