一次坏块的处理过程(一)

最近在一个数据库遇到了坏块，以下是处理过程。

一、坏块的发现及处理

[@more@] 首先是在做RMAN备份的时候，看到alert文件有坏块报错：
.......
Corrupt block relative dba: 0x00925026 (file 2, block 1200166)
Fractured block found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x00925026
last change scn: 0x0578.15b7ebf7 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x33da0602
check value in block header: 0xf8d0
computed block checksum: 0xd82e
Reread of blocknum=1200166, file=+DATA/dwrac/datafile/dwdata_1m01.dbf. found same corrupt data
Reread of blocknum=1200166, file=+DATA/dwrac/datafile/dwdata_1m01.dbf. found same corrupt data
Reread of blocknum=1200166, file=+DATA/dwrac/datafile/dwdata_1m01.dbf. found same corrupt data
Reread of blocknum=1200166, file=+DATA/dwrac/datafile/dwdata_1m01.dbf. found same corrupt data
Reread of blocknum=1200166, file=+DATA/dwrac/datafile/dwdata_1m01.dbf. found same corrupt data
......

用dbv检查也证实了这一点。

[oracle@dwdb02 admin]$ dbv file="+DATA/dwrac/datafile/dwdata_1m01.dbf" start=502554 end=1217134 userid=admin/sdoadmin123 blocksize=16384

DBVERIFY: Release 10.2.0.5.0 - Production on Thu Feb 17 14:50:13 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/dwrac/datafile/dwdata_1m01.dbf
Page 502554 is marked corrupt
Corrupt block relative dba: 0x0087ab1a (file 2, block 502554)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x00800001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0x85d8
computed block checksum: 0x0
......

DBVERIFY - Verification complete

Total Pages Examined : 714581
Total Pages Processed (Data) : 567369
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 143342
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3866
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 4
Total Pages Influx : 0
Highest block SCN : 0 (0.0)

10g以后rman备份发现的坏块信息可以在v$database_block_corruption查到。

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------
2 1217134 1 0 FRACTURED
508 2386314 1 0 FRACTURED
517 1115970 1 0 FRACTURED
2 1200166 1 0 FRACTURED
51 401698 1 0 FRACTURED
61 306642 1 0 FRACTURED
2 502554 1 0 FRACTURED
2 1213658 1 0 FRACTURED
34 701050 1 0 FRACTURED
60 347166 1 0 FRACTURED

FRACTURED意味着数据块是物理损坏，遇到这种情况，如果有备份的话最好是从备份恢复，甚至可以用rman做块级恢复，如
blockrecover datafile 2 block 1217134;
或者：
blockrecover corruption list;

不幸的是，这个数据库是一个新库，没有正式上线，也还没有上备份。没办法，只能看看损坏的对象是什么了。

--由于库太大，直接查dba_extents太慢，因此备份dba_extents到表dbextnet再查询。

SQL> select * from dbextent where file_id=2 and 1213658 between block_id and block_id+blocks-1;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
CREATER_USER WIDGET_NEWUSE_IX2 INDEX DWDATA_1M 995 2 1213637 1048576 64 2

SQL> select * from dbextent where file_id=2 and 502554 between block_id and block_id+blocks-1;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
CREATER_USER WIDGET_NEWUSE_IX2 INDEX DWDATA_1M 1787 2 502533 1048576 64 2


万幸的是，这里损坏的只是索引，把索引rebuild就可以。如果损坏的是表，则可能需要用到其他手段，如dbms_repare包来处理了。

SQL> alter index creater_user.WIDGET_NEWUSE_IX2 rebuild online parallel 16 tablespace dwdata_10m_1 nologging;

Index altered

......

注意：这里要用rebuild online，否则rebuild可能会读取原索引作为源来重建新索引，这样的话新建的索引也是损坏的。

全部处理完毕后，确认是否还有对象损坏。

SQL> select * from dba_extents E,v$database_block_corruption c where e.file_id=c.file# and c.block# between e.block_id and e.block_id+e.blocks-1;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ------------------ ---------------

以上查询返回结果为空，表示没有数据库对象损坏，第一阶段处理完毕。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-1046125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-1046125/