segment header损坏如何修复

段头损坏，dbms_repair包，dbms_rowid，以及event 10231 都是没有作用的（存在非空索引的情况除外）。
但是基于rowid的操作方式可以的。 关于这点，oracle mos文档中也有提及.
This article describe a procedure that can be used to skip corrupted rows from a table when event 10231, dbms_repair or skipping the rows through an index cannot be used. This method builds the rowid’s for a table based on the information from dba_extents.

测试环境准备

SQL> conn / as sysdba
Connected.
SQL> create table zhuo.zhuo as select * from dba_objects where object_id is not null;

Table created.

SQL> select count(*) from zhuo.zhuo;

  COUNT(*)
----------
     80066
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';

SQL> col SEGMENT_NAME for a10

SEGMENT_NA HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
---------- ----------- ------------ ---------- ----------
ZHUO                 5         1410       1280         25

dump block

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 5 block 1410;

System altered.

SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_1584.trc

部分trace：
Block dump from disk:
buffer tsn: 5 rdba: 0x01400582 (5/1410)
scn: 0x0000.000ba855 seq: 0x01 flg: 0x04 tail: 0xa8552301
frmt: 0x02 chkval: 0x94ed type: 0x23=PAGETABLE SEGMENT HEADER

通过header_block和dump block确定block 1410即为PAGETABLE SEGMENT HEADER

损坏前查询

SQL> alter session set current_schema=ZHUO;

Session altered.
SQL> set autot trace
SQL> select count(*) from zhuo.zhuo;


Execution Plan
----------------------------------------------------------
Plan hash value: 1214160148

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   320   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ZHUO | 49886 |   320   (0)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1149  consistent gets
       1146  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

修复

存在非空索引-- 使用索引获取ROWID

1. 在11.2版本中，段头损坏的话，仍然是可以访问的(有主键索引的情况下)

SQL> alter table zhuo.zhuo add constraint PK_zhuo primary key (object_id);

Table altered.

破坏块，出现坏块：

BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1410

Block 1410 is corrupt
Corrupt block relative dba: 0x01400582 (file 0, block 1410)
Fractured block found during verification
Data in bad block:
 type: 35 format: 2 rdba: 0x01400582
 last change scn: 0x0000.000ba855 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xa9552301   ---根据tail的计算方式：23应该为type，跟前面的35对应不上。原因：35=0x23，type以10进制显示。后面的tail以16进制显示。
 check value in block header: 0x95ed
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

查询该表

SQL> alter system checkpoint;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from zhuo.zhuo;

  COUNT(*)
----------
     80066

SQL> set autot trace
SQL> alter session set current_schema='ZHUO' 
  2  ;
alter session set current_schema='ZHUO'
                                 *
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL> alter session set current_schema=ZHUO;

Session altered.

SQL> select count(*) from zhuo;


Execution Plan
----------------------------------------------------------
Plan hash value: 2127640271

-------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    50   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_ZHUO | 95893 |    50   (0)| 00:00:01 |
-------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
        187  consistent gets
          6  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select /*+full(t) */ count(*) from zhuo.zhuo t;
select /*+full(t) */ count(*) from zhuo.zhuo t
                                        *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'

SQL> select /*+index(t pk_zhuo)*/  count(rowid) from zhuo.zhuo t;


Execution Plan
----------------------------------------------------------
Plan hash value: 1209007207

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    12 |   180   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE  |         |     1 |    12 |            |          |
|   2 |   INDEX FULL SCAN| PK_ZHUO |  8168 | 98016 |   180   (0)| 00:00:03 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         29  recursive calls
         12  db block gets
        197  consistent gets
          1  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from zhuo.zhuo;
select * from zhuo.zhuo
                   *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'

发现不报错，根据前面的测试，当segment block出现坏块的时候，select和dml都是不能进行的，但是在此处却不报错。**在11.2版本中，段头损坏的话，仍然是可以访问的(有主键索引的情况下).**自动走索引访问数据.但是，不能通过索引获得的仍然报错。

1.1说明
针对段头损坏，如果你使用我们常规的查询data block 的sql，是查询不到的，如下：

SQL> SELECT tablespace_name, segment_type, owner, segment_name
 FROM dba_extents
  2    3   WHERE file_id =5
  4   and 1410 between block_id and block_id + blocks -1;

no rows selected

通常来讲，如果上面sql查询不到，那么该block很可能就是segment header或位图block：

SQL> SELECT owner, segment_name, segment_type, partition_name FROM dba_segments WHERE header_file =5
  2   and header_block=1410;

OWNER      SEGMENT_NA SEGMENT_TYPE       PARTITION_NAME
---------- ---------- ------------------ ------------------------------
ZHUO       ZHUO       TABLE

我们可以通过alert log或trace文件信息去识别。
alert部分信息：
Corrupt Block Found
TSN = 5, TSNAME = ZHUO
RFN = 5, BLK = 1410, RDBA = 20972930
OBJN = 80622, OBJD = 80622, OBJECT = ZHUO, SUBOBJECT =
SEGMENT OWNER = ZHUO, SEGMENT TYPE = Table Segment

2. 抢救数据

SQL> create table zhuo_new as  select * from zhuo.zhuo where 1=0;

Table created.
SQL> create table bad_rows (row_id rowid,oracle_error_code number);

Table created.

SQL> set serveroutput on;
set timing on;
DECLARE
   TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  CURSOR c1 IS select /*+ index(t pk_zhuo) */ rowid
   from zhuo.zhuo t;
SQL> SQL>   2    3    4    5    r RowIDTab;
  6     rows NATURAL := 20000;
  7     bad_rows number := 0 ;
  8     errors number;
  9     error_code number;
 10     myrowid rowid;
 11  BEGIN
 12  OPEN c1;
 13  LOOP
 14   FETCH c1 BULK COLLECT INTO r LIMIT rows;
 15   EXIT WHEN r.count=0;
 16   BEGIN
 17      FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
 18       insert into zhuo_new
 19      select /*+ ROWID(A) */ *
 20       from zhuo.zhuo A where rowid = r(i);
 21     EXCEPTION
 22     when OTHERS then
 23      BEGIN
 24       errors := SQL%BULK_EXCEPTIONS.COUNT;
 25       FOR err1 IN 1..errors LOOP
 26        error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
 27        if error_code in (1410, 8103) then
 28         myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
 29         bad_rows := bad_rows + 1;
 30         insert into bad_rows values(myrowid, error_code);
 31        else
 32         raise;
 33        end if;
 34       END LOOP;
 35      END;
 36     END;
 37     commit;
 38    END LOOP;
 39    commit;
 40    CLOSE c1;
 41    dbms_output.put_line('Total Bad Rows: '||bad_rows);
 42  END;
 43  /
Total Bad Rows: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.44
SQL> select count(*) from zhuo_new;

  COUNT(*)
----------
     80066

Elapsed: 00:00:00.05

可以看到，可以把数据全部抢救出来。没有损失。
具体脚本如下：
set serveroutput on;
set timing on;
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(t pk_zhuo) / rowid
from zhuo.zhuo t;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST…r.LAST SAVE EXCEPTIONS
insert into zhuo_new
select /+ ROWID(A) */ *
from zhuo.zhuo A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1…errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
注意source table和目标表的修改。

不存在非空索引–dbms_rowid.ROWID_CREATE来构造ROWID

重新创建表，没有非空索引：

SQL> drop table zhuo.zhuo purge;

Table dropped.

SQL> create table zhuo.zhuo 
  2   as
  3  select * from dba_objects where object_id is not null;

Table created.

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';

SEGMENT_NAME
--------------------------------------------------------------------------------
HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
----------- ------------ ---------- ----------
ZHUO
          5         1410       1280         25


SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from zhuo.zhuo;

  COUNT(*)
----------
     80069

制造坏块

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'

抽取数据：

SQL> create table bad_rows (row_id rowid,oracle_error_code varchar2(50));
SQL> create table zhuo_new as select * from zhuo.zhuo where 1=2;

SQL> set serveroutput on;
set timing on;
SQL> SQL> 
SQL> DECLARE
  2    TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  3    CURSOR Crowid_info IS
  4      select Do.DATA_OBJECT_ID dataid,
  5             DE.FILE_ID        fid,
  6             DE.BLOCK_ID       blkid,
  7             DE.BLOCKS         blkcnt
  8        from dba_objects DO, dba_extents DE
  9       where DO.OBJECT_NAME = 'ZHUO' 
 10         and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')
 11         and DO.OBJECT_NAME = DE.SEGMENT_NAME
 12         and DO.owner = 'ZHUO'
 13       order by 1, 2, 3 asc;
 14    bad_rows   number := 0;
 15    errors     varchar2(500);
 16    error_code varchar2(500);
 17    myrowid    rowid;
 18  BEGIN
 19    execute immediate 'alter session set commit_write=''batch,nowait'' ';
 20    for i in Crowid_info loop
 21      for j in 0 .. i.blkcnt - 1 loop
 22        for z in 0 .. 2000 loop
 23          begin
 24            myrowid := dbms_rowid.ROWID_CREATE(1,i.dataid,i.fid,i.blkid + j,z);
 25            insert into zhuo_new select /*+ ROWID(A) */ * from zhuo.zhuo A where rowid = myrowid;
 26          EXCEPTION
 27            when OTHERS then
 28              BEGIN
 29                errors     := SQLERRM;
 30                error_code := SQLCODE;
 31                if (error_code like '%1410%' or error_code like '%8103%' or  error_code like '%1578%') then
 32                  bad_rows := bad_rows + 1;
 33                  insert into bad_rows values (myrowid, error_code);
 34                  commit;
 35                else
 36                  raise;
 37                end if;
 38              END;
 39              commit;
 40          end;
 41        end loop;
 42      end loop;
 43    end loop;
 44    dbms_output.put_line('Total Bad Rows: ' || bad_rows);
 45    commit;
 46  END;
 47  /
Total Bad Rows: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13

SQL> select count(*) from zhuo_new;

  COUNT(*)
----------
         0

按照mos上面的文档操作，发现，居然没有数据被抽取出来。

手动构造的方式：

SQL> select object_id from dba_objects where object_name='ZHUO';

 OBJECT_ID
----------
     80630

SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO;
select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO
                                                               *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO.ZHUO;
select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO.ZHUO
                                                                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
SQL> select dbms_rowid.rowid_create(1,80630,5,1411,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAATr2AAFAAAAWDAAA

SQL> insert into zhuo_new select * from zhuo.zhuo where rowid >= CHARTOROWID('AAATr2AAFAAAAWDAAA');

insert into zhuo_new select * from zhuo.zhuo where rowid >= CHARTOROWID('AAATr2AAFAAAAWDAAA')
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'

大家可以看到，针对没有非空索引或主键存在的情况下，段头损坏，基于rowid的方式是没有办法处理的。
总结：
针对11gR2版本，segment header损坏的情况下，可以通过如下PL/SQL 构造ROWID的方式挽救绝大多数非坏块的数据， 一般分成 2种情况 ：
1、如果存在非空索引，可以使用索引查询出rowid，全部抽取出数据。
2、对于没有索引或者索引损坏的情况，基于rowid的方法没有办法处理。

参考：
http://www.askmaclean.com/archives/build-rowid-workaround-1578-1410-8103.html
SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE (Doc ID 1527738.1)