段头部损坏后，表将无法读取

如果使用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博客

段头损坏的处理

最新文章