oracle table 段头 block 坏块处理流程

--1、模拟segment header block 损坏
create table t1
   as
   select * from dba_objects where object_id is not null;
   
alter table t1 add constraint PK_t1 primary key (object_id);


alter system checkpoint;

select count(*) from  scott.t1;
  COUNT(*)
----------
     87336

select header_file,header_block from DBA_SEGments where segment_name='T1' AND OWNER='SCOTT';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4         5522
		  



alter system dump datafile 4 block 5522;
select * from v$diag_info;

----dump block 信息--PAGETABLE SEGMENT HEADER
Block dump from disk:
buffer tsn: 4 rdba: 0x01001592 (4/5522)
scn: 0x0000.00dc4bcd seq: 0x01 flg: 0x04 tail: 0x4bcd2301
frmt: 0x02 chkval: 0xc1fd type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1



--损坏段头
set dba  4,5522
set offset 8188
d
m /x 0123e491
sum apply 
verify

--重启库后访问表异常

SQL> select count(1) from scott.t1;
select count(1) from scott.t1
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 5522)
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'


select /*+ index(t PK_t1) */  count(rowid) from scott.t1 t;
COUNT(ROWID)
------------
       87336
	   

--基于rowid获取segment header 坏块对象数据  
create table scott.bad_rows (table_name varchar2(60),row_id rowid, oracle_error_code number);
--创建临时表
create  table scott.t1_tmp as select * from t1 where 1=2;


--根据rowid把插入到新表
SET SERVEROUTPUT ON 
DECLARE
 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
 CURSOR c1 IS  select /*+index(t PK_t1)*/ rowid from scott.t1 t; --修改地方1
 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 scott.t1_tmp select /*+ ROWID(A) */ * from scott.t1 A where rowid = r(i); --修改地方2
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
         error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
         myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
         bad_rows := bad_rows + 1;
         insert into scott.bad_rows values('scott.t1',myrowid, error_code);  --修改地方3
     END LOOP;
     END;
   END;
  commit;
 END LOOP;
 commit;
 CLOSE c1;
 dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/


Total Bad Rows: 0

PL/SQL procedure successfully completed.

select count(1) from scott.t1_tmp;
  COUNT(1)
----------
     87336
---至此数据全部恢复	 
----通过上面pl/sql，基于rowid成功获得segment header 异常对象中的所有数据记录.如果没有主键的表出现该问题,可以参考:使用plsql抢救数据