oracle壞塊Block Corruptions

物理壞塊與邏輯壞塊(Physical and Logical Block Corruptions)

壞塊被分為物理壞塊和邏輯壞塊

(一) 物理壞塊

物理壞塊通常會報ORA-1578錯誤,並在alert.log中記錄詳細資訊

常見的形式如下:

Bad header - the beginning of the block (cache header) is corrupt with invalid values
The block is Fractured/Incomplete - header and footer of the block do not match
The block checksum is invalid
The block is misplaced
Zeroed out blocks

詳細描述如下:

1) Fractured Block

表明block的頭部和尾部不匹配,證明該block的資訊是不完整的,trace包含如下資訊

Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380e573
last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
consistency value in tail: 0x00780601
check value in block header: 0x8739, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data

2) Bad Checksum

Block 在寫入的時候會進行block的Checksums(該功能由引數db_block_checksum控制預設為true) 並把效驗值記入block頭部,每次block由硬碟讀入記憶體的的時候,oracle計算block效驗值是否和block的頭部的資訊是否一致。

Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data
A value different than zero (0x0) in "computed block checksum" means that the checksum differs and the result of this comparison is printed.

3) Block Misplaced

Checksum有效但是block的內容屬於其他block,trace 包含如下資訊

Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08 ----&gt Block is different than expected 0x0d805a89
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

(二) 邏輯壞塊

Checksum有效，但是block內容不一致,通常引起ORA-600錯誤

如: ORA-00600 [13013], [5001] (Doc ID 816784.1)
邏輯壞塊常見形式:

row locked by non-existent transaction - ORA-600 [4512], etc
the amount of space used is not equal to block size
avsp bad
etc.

當db_block_checking設定為true時,會報如下錯誤

ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].如果磁碟上的block已經有邏輯壞塊,該block下次被更新的時候，將被標記為Soft Corrupt 將來被讀的時候報ORA-1578, DBVerify將報錯誤"DBV-200: Block, dba , already marked corrupted".

效驗工具

1) RMAN方式

資料庫

backup validate database;--不加check logical選項僅檢測物理壞塊

backup check logical validate database;

壞塊資訊記錄到V$DATABASE_BLOCK_CORRUPTION

資料庫+歸檔日誌

BACKUP VALIDATE DATABASE ARCHIVELOG ALL

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

針對備份檔案

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE;

壞塊恢復恢復

BLOCKRECOVER DATAFILE 3 BLOCK 121;

BLOCKRECOVER CORRUPTION LIST; 恢復V$DATABASE_BLOCK_CORRUPTION中所有壞塊

2) DBVerify 方式

預設檢測物理和邏輯壞塊

dbv file= blocksize=

3) ANALYZE .. VALIDATE STRUCTURE方式

analyze table

validate structure cascade ;

4) DBMS_REPAIR 方式

發現、標識並修改資料檔案中的壞塊,但使用這個包的同時會帶來資料丟失、表和索引返回資料不一致，完整性約束破壞等其他問題,因此，dbms_repair只是在沒有備份的情況下使用的一種手段，這種方式一般都會造成資料的丟失,dbms_repair包的工作原理比較簡單，是將檢查到的壞塊標註出來，使隨後的dml操作跳過該塊
詳細操作:參考