段頭損壞的處理

段頭部損壞後，表將無法讀取

如果使用DBV發現段頭壞塊，而此時該表仍然可以讀取（段頭在SGA中的資料還是好的），那麼馬上執行CTAS把資料給搞出來

如果表已經無法訪問，報ORA-01578錯誤

例子：

--建立表，獲取段頭部

create table test as select * from dba_objects;

DELETE test WHERE object_id IS NULL;

ALTER TABLE test ADD CONSTRAINTS pk_test PRIMARY KEY (object_id);

SQL> select header_file,header_block from dba_segments where segment_name='TEST' and wner='CTAIS2';

HEADER_FILE HEADER_BLOCK

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

5 331

--損壞塊

BBED> set dba 5,331

DBA 0x0140014b (20971851 5,331)

BBED> set offset 140

OFFSET 140

BBED> modify /x 0e

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf (5)

Block: 331 Offsets: 140 to 651 Dba:0x0140014b

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

0e00341f e41e8e1e 3e1ee81d 991d441d f41ca81c 521cf51b 941b3f1b f01a9b1a

4d1af919 ac195719 fc18aa18 52180518 b2175e17 0417b316 5c160f16 bc156815

0e15bd14 66141614 c0137113 1c13cd12 78122512 cc117f11 2d11d510 83102b10

d20f730f 1b0fbd0e 650e070e ad0d4d0d f40c950c 3c0cdd0b 870b2b0b d30a7e0a

230acc09 77091c09 c5087108 1708c407 6b071807 c0066f06 1806c805 72052205

cc047604 1a040000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set offset 160

OFFSET 160

BBED> modify /x 77777777

File: /u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf (5)

Block: 331 Offsets: 160 to 671 Dba:0x0140014b

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

77777777 941b3f1b f01a9b1a 4d1af919 ac195719 fc18aa18 52180518 b2175e17

0417b316 5c160f16 bc156815 0e15bd14 66141614 c0137113 1c13cd12 78122512

cc117f11 2d11d510 83102b10 d20f730f 1b0fbd0e 650e070e ad0d4d0d f40c950c

3c0cdd0b 870b2b0b d30a7e0a 230acc09 77091c09 c5087108 1708c407 6b071807

c0066f06 1806c805 72052205 cc047604 1a040000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

--查詢

SYS@SOURCE10 > ALTER SYSTEM FLUSH buffer_cache;

System altered.

SYS@SOURCE10 > SELECT /*+full(a) */COUNT(*) FROM ctais2.test a;

SELECT /*+full(a) */COUNT(*) FROM ctais2.test a

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 331)

ORA-01110: data file 5:'/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf'

--檢查

SYS@SOURCE10 > SELECT * FROM dba_extents WHERE file_id=5 AND 331 BETWEEN block_id AND block_id+blocks-1;

no rows selected

SYS@SOURCE10 > SELECT owner,segment_name FROM dba_segments WHERE tablespace_name='ZHANGQIAOC'

2 MINUS

3 SELECT DISTINCT owner,segment_name FROM dba_extents WHERE tablespace_name='ZHANGQIAOC';

OWNER SEGMENT_NAME

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

CTAIS2 BIN$b0bjYVgIIpngQKjAyHtslQ==$0

CTAIS2 BIN$b0hbilBCrXXgQKjAyHt5og==$0

CTAIS2 TEST

SYS@SOURCE10 > SELECT HEADER_FILE,HEADER_BLOCK FROM dba_segments WHERE segment_name='TEST' AND wner='CTAIS2';

HEADER_FILE HEADER_BLOCK

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

5 331

[oracle10@WESTZQ ~]$ dbv file=/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Wed Jul 22 17:30:51 2009

DBVERIFY - Verification starting : FILE = /u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf

Block Checking: DBA = 20971531, Block Type = KTB-managed data block

data header at 0xb7ea827c

kdbchk: row count in table index incorrect

Page 11 failed with check code 6125

Page 331 is marked corrupt

Corrupt block relative dba: 0x0140014b (file 5, block 331)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0140014b

last change scn: 0x0000.001d53af seq: 0x1 flg: 0x04

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

consistency value in tail: 0x53af0601

check value in block header: 0xe6b7

computed block checksum: 0x7fb

DBVERIFY - Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 729

Total Pages Failing (Data) : 1

Total Pages Processed (Index): 71

Total Pages Failing (Index): 0

Total Pages Processed (Other): 111

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 368

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 1928996 (0.1928996)

SYS@SOURCE10 > analyze table CTAIS2.TEST validate structure;

analyze table CTAIS2.TEST validate structure

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 331)

ORA-01110: data file 5:'/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf'

RMAN> backup validate check logical datafile 5;

Starting backup at 2009-07-22 16:58:57

using target database control file instead of recovery catalog

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=133 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0

channel ORA_SBT_TAPE_1: starting full datafile backupset

channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2009-07-22 16:58:59

SYS@SOURCE10 > SELECT * FROM v$backup_corruption ;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#

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

BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO

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

8 692904714 692904714 155 1 5 331

1 0 YES CHECKSUM

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

--恢復

CTAIS2@SOURCE10 > CREATE TABLE test_bak AS SELECT /*+index(a,PK_TEST)*/* FROM test a;

Table created.

CTAIS2@SOURCE10 > drop table test;

drop table test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 331)

ORA-01110: data file 5:'/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf'

CTAIS2@SOURCE10 > rename test to test_del;

rename test to test_del

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 331)

ORA-01110: data file 5:'/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf'

BBED> sum

Check value for File 5, Block 331:

current = 0xe6b7, required = 0xe14c

BBED> sum apply

Check value for File 5, Block 331:

current = 0xe14c, required = 0xe14c

SYS@SOURCE10 > ALTER SYSTEM FLUSH buffer_cache;

System altered.

SYS@SOURCE10 > SELECT * FROM ctais2.test;

SELECT COUNT(*) FROM ctais2.test

*

ERROR at line 1:

ORA-08103: object no longer exists

RMAN> backup validate check logical datafile 5;

Starting backup at 2009-07-22 17:03:32

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting full datafile backupset

channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/oracle10/app/oracle/oradata/SOURCE10/ZHANGQIAOC01.dbf

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2009-07-22 17:03:33

SYS@SOURCE10 > SELECT * FROM v$backup_corruption ;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#

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

BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO

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

8 692904714 692904714 155 1 5 331

1 0 YES CHECKSUM

9 692905138 692905138 156 1 5 11

1 1921046 YES LOGICAL

CTAIS2@SOURCE10 > rename test to test_del;

Table renamed.

CTAIS2@SOURCE10 > rename test_bak to test;

Table renamed.

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

l 但是這個時候有個問題，以前的表和索引都DROP不掉,不過表和資料都能正常恢復。

l 測試的要刪除這個損壞物件的方法只有刪除表空間（或者10g刪除資料檔案，未測試）

l 對於段頭損壞的表，可以通過索引得到其資料，如果沒索引，就去拼ROWID吧，不過我覺得這是不可能的事情，DBA_EXTENTS中更本就沒有該盤區的資訊

l 用bbed修復checksum可以改變壞塊的型別，完成RENAME操作

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

段頭損壞的處理

相關文章