ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 段头损坏的处理

段头损坏的处理

原创 Linux操作系统 作者:westzq1984 时间:2009-07-24 10:10:35 0 删除 编辑

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

 

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

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 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

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 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

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

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    948561