Oracle壞塊問題處理

luckyfriends發表於2013-04-09

Oracle

曾經遇到過ORA-8103,01578的壞塊錯誤記錄下方法

首先，製造壞塊

select tablespace_name,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='ETMCDB';

TABLESPACE_NAME EXTENT_MAN SEGMEN

------------------------------ ---------- ------

ETMCDB LOCAL AUTO

create table t1 (id number, c1 char(2000), c2 char(2000), c3 char(2000)) tablespace ETMCDB;

insert into t1 VALUES (1, 'A', 'A', 'A');

insert into t1 VALUES (2, 'A', 'A', 'A');

insert into t1 VALUES (3, 'A', 'A', 'A');

insert into t1 VALUES (4, 'A', 'A', 'A');

insert into t1 VALUES (5, 'A', 'A', 'A');

insert into t1 VALUES (6, 'A', 'A', 'A');

insert into t1 VALUES (7, 'A', 'A', 'A');

commit;

select id,rowid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) from t1;

ID ROWID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)||'_'||DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

---------- ------------------ ---------------------------------------------------------------------------------

1 AAAOZnAAHAAAAAUAAA 7_20

2 AAAOZnAAHAAAAAVAAA 7_21

3 AAAOZnAAHAAAAAWAAA 7_22

4 AAAOZnAAHAAAAAXAAA 7_23

5 AAAOZnAAHAAAAAYAAA 7_24

6 AAAOZnAAGAAAABhAAA 6_97

7 AAAOZnAAGAAAABiAAA 6_98

7 rows selected.

SQL> select file#,name from v$datafile;

FILE# NAME

---------- -----------------------

7 /u02/oradata/ETMCDB02.dbf

SQL> shutdown immediate;

修改Checksum的值

匯出第31個塊（算上os header block，物理上是32個塊）來把它的變成壞塊，使用dd考出data block 【更多關於DD請參考這裡】

node1*orcl-/u02/oradata >dd if=/u02/oradata/ETMCDB02.dbf f=/u02/oradata/ETMCDB02_7_21.dd skip=21 bs=8192 count=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.00417351 seconds, 2.0 MB/s

這裡涉及到了notrunc模式，如果沒有指定notrunc的話那麼需要再dd出65505個塊，

node1*orcl-/u02/oradata >dd if=/u02/oradata/ETMCDB02.dbf f=/u02/oradata/ETMCDB02_7_65505.dd skip=22 bs=8192 count=65505

原因

node1*orcl-/home/oracle >ls -al /u02/oradata/

total 1159260

-rw-r--r-- 1 oracle dba 8192 Nov 15 15:23 ETMCDB02_7_21.dd

-rw-r----- 1 oracle dba 536879104 Nov 15 14:51 ETMCDB02.dbf

SQL> select (536879104-32*8192)/8192 from dual;

(536879104-32*8192)/8192

----------------------

65505

也可以不用這麼麻煩，可以在導回時使用notrunc方法

之後用Ultraedit開啟修改offset 16隨便一個>0的數值，然後傳回

node1*orcl-/u02/oradata > dd if=/u02/oradata/ETMCDB02_7_21.dd f=/u02/oradata/ETMCDB02.dbf seek=21 bs=8192 count=1 conv=notrunc

這裡還有個小技巧就是直接用Ultraedit開啟確定檔案7號ETMCDB02.dbf後在UE中按CTRL+G，輸入這個塊號*塊大小的結果就可以了，比如這個例子就是21*8192

SQL> startup

SQL> select * from t1;

ERROR:

ORA-01578: ORACLE data block corrupted (file # 7, block # 21)

ORA-01110: data file 7: '/u02/oradata/ETMCDB02.dbf'

no rows selected

SQL> SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1 ;

Enter value for file: 7

Enter value for block: 21

old 1: SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1

new 1: SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = 7 and 21 between block_id AND block_id + blocks - 1

TABLESPACE OWNER SEGMENT_NAME SEGMENT_TYPE

---------- ---------- -------------------- ------------------

ETMCDB XXD T1 TABLE

node1*orcl-/u02/oradata >dbv file=ETMCDB02.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Nov 17 18:53:08 2010

DBVERIFY - Verification starting : FILE = ETMCDB02.dbf

Page 21 is marked corrupt

Corrupt block relative dba: 0x01c00015 (file 7, block 21)

Bad header found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x01c00015

last change scn: 0x0000.005552a8 seq: 0x1 flg: 0x02

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x52a80601

check value in block header: 0x11

block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined : 640

Total Pages Processed (Data) : 461

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 1

Total Pages Failing (Index): 0

Total Pages Processed (Other): 45

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 132

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 5591720 (0.5591720)

使用DBMS_REPAIR “修復”壞塊

set serveroutput on

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'ETMCDB');

END;

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'T1',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

corrupt_count => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'T1',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

SQL> select id from t1;

----------

6 rows selected

SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,repair_description from repair_table;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_CORCORRUPT_DESCRIPTION REPAIR_DESCRIPTION

----------- -------- ------------ ------------------------------ --------------------------

T1 21 6148 TRUE mark block software corrupt

SQL> alter system checkpoint;

System altered.

看看這個DBMS_REPAIR“修復”到底做了些什麼

node1*orcl-/u01/app/oracle/product/10.2.0/db_1/bin >bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed Nov 17 19:01:05 2010

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 7

FILE# 7

BBED> set block 21

BLOCK# 21

BBED> dump

File: /u02/oradata/ETMCDB02.dbf (7)

Block: 21 Offsets: 0 to 511 Dba:0x01c00015

------------------------------------------------------------------------

06a20000 1500c001 a8525500 00000102 11000000 01000000 67e60000 a4525500

00000000 02003200 1100c001 0a000300 1e0a0000 87008000 0a061500 01200000

a8525500 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00010100 ffff1400 19080508 05080000 01001908 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

對比這個資料庫沒有被破壞時的情況

BBED> dump

File: /u02/oradata/ETMCDB02.dbf (7)

Block: 21 Offsets: 0 to 511 Dba:0x01c00015

------------------------------------------------------------------------

06a20000 1500c001 a8525500 00000102 00000000 01000000 67e60000 a4525500

<32 bytes per line>

而這時候檢視Block內部，實際上DBMS_REPAIR沒有對塊做任何的修改只是跳過了該塊。實際情況下，checksum壞了往往意味著壞內的資料已經壞了因為我們無法的值正確的Checksum的值，只能跳過。

那麼看看RMAN能夠對壞塊做些什麼

首先使用rman檢查含有壞塊的資料檔案:

RMAN> backup validate datafile 7;

隨後檢視壞塊資訊v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

7 21 1 0 FRACTURED

使用rman進行塊恢復:

RMAN> blockrecover datafile 7 block 21 from backupset;

如果執行BLOCKRECOVER CORRUPTION LIST會自動按照V$DATABASE_BLOCK_CORRUPTION進行修復

RMAN> BLOCKRECOVER CORRUPTION LIST;

隨後確認壞塊資訊v$database_block_corruption

SQL> select * from v$database_block_corruption;

no rows selected

如果用RMAN備份該檔案，而後還原該檔案後，則這個壞塊的seq_kcbh則被設為0xff。

神器BBED或者DD+UltraEdit

大概說一下如果資料塊的損壞應該是offset@18也就是seq_kcbh被標記成0xff。這時檢查alert.log如果看到 computed block checksum那麼使用BBED其實很容易，進入然後開啟損壞的資料塊，offset到16，sum apply後oracle會算出正確的checksum值並且寫回去。具體演算法就是 computed block checksum後邊會有一個數值那麼轉換成二進位制和現有的offset 16 17做異或運算就可以算出正確的值。我也是最近才弄明白這種演算法，正好複習。

最後還有一種exp+10231事件的終極方法，具體看連結內的eygle的文章。不過對於大表就是個災難，希望能夠在面臨這類災難時有可靠的RMAN備份可用，不然為了幾個資料塊而去exp..........簡直就是侮辱了DBA這個職位。

參考文章

記一次ORA-8103錯誤的處理

Oracle怎樣標記壞塊及一次資料恢復

Oracle中模擬及修復資料塊損壞

利用dd修改checksum值的過程

利用BBED修改checksum值的過程

怎樣計算出正確的checksum值

http://www.cnblogs.com/buro79xxd/archive/2010/11/22/1884491.html

http://www.itpub.net/thread-991044-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-758050/，如需轉載，請註明出處，否則將追究法律責任。