ORA-01578 ORA-01110 坏块解决方法

一个案例,查看跟踪文件发现如下错误信息

d:\oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_5888.trc

Corrupt block relative dba: 0x09848269 (file 38, block 295529)

Bad header found during buffer read

Data in bad block:

 type: 1 format: 5 rdba: 0x30322d2d

 last change scn: 0xfffe.fffefffe seq: 0xfe flg: 0xff

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

 consistency value in tail: 0x2d2d3533

 check value in block header: 0xfffe

 computed block checksum: 0x91f9

Reread of rdba: 0x09848269 (file 38, block 295529) found same corrupted data

Fri Dec 27 08:28:08 2013

Corrupt Block Found

         TSN = 36, TSNAME = HGHIS

         RFN = 38, BLK = 295529, RDBA = 159679081

         OBJN = 119544, OBJD = 119544, OBJECT = EMR_MONITOR_RESULT, SUBOBJECT = 

         SEGMENT OWNER = HGHIS, SEGMENT TYPE = Table Segment

Fri Dec 27 08:54:18 2013

Hex dump of (file 38, block 295561) in trace file d:\oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_6796.trc

Corrupt block relative dba: 0x09848289 (file 38, block 295561)

Bad header found during buffer read

Data in bad block:

 type: 48 format: 0 rdba: 0x020cc100

 last change scn: 0xb500.080cc100 seq: 0xa5 flg: 0xba

 spare1: 0x34 spare2: 0x2 spare3: 0xb0b8

 consistency value in tail: 0x395e3031

 check value in block header: 0xcfcb

 block checksum disabled

Reread of rdba: 0x09848289 (file 38, block 295561) found same corrupted data

Fri Dec 27 08:54:19 2013

Corrupt Block Found

         TSN = 36, TSNAME = HGHIS

         RFN = 38, BLK = 295561, RDBA = 159679113

         OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT = 

         SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob Segment

Fri Dec 27 09:11:20 2013

Hex dump of (file 38, block 295563) in trace file d:\oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_5584.trc

Corrupt block relative dba: 0x0984828b (file 38, block 295563)

Bad header found during buffer read

Data in bad block:

 type: 71 format: 7 rdba: 0x064d0001

 last change scn: 0x3038.43584400 seq: 0x31 flg: 0x07

 spare1: 0x44 spare2: 0x4c spare3: 0x771

 consistency value in tail: 0x000a0000

 check value in block header: 0x7800

 computed block checksum: 0xcbf8

Reread of rdba: 0x0984828b (file 38, block 295563) found same corrupted data

Fri Dec 27 09:11:21 2013

Corrupt Block Found

         TSN = 36, TSNAME = HGHIS

         RFN = 38, BLK = 295563, RDBA = 159679115

         OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT = 

         SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob Segment

Fri Dec 27 09:27:59 2013

根据上述信息得知38号数据文件的295529、295561、295563为坏块，可以使用DBV工具或者RMAN来检查坏块信息

DBV FILE="d:\oradata\DATA.DBF"  blocksize=8192

or

rman target /

backup validate check logical database;

select * from V$DATABASE_BLOCK_CORRUPTION ;

可以根据文件号和块号查出损坏的是对象,表还是LOB segment

select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=38 and 295529 between block_id AND block_id + blocks - 1;

38是文件号,295529是block号

如果是对象，可以重建

alter index indexname rebuild

如果是表，可以使用10231事件忽略坏块，然后使用CTAS方式重建表最后rename table，别忘记rebuild index

alter session SET EVENTS '10231 trace name context forever,level 10';

create table tab_new as select * from tab;

rename tab to tab_bak;

rename tab_new to new;

alter index indexname rebuild;
alter session SET EVENTS '10231 trace name context off';

如果损坏的是LOB segment,先找出segment信息

select owner, segment_name, segment_type from dba_extents where file_id = 38 and 295563 between block_id and block_id + blocks - 1;

输出如下

owner=HGHIS
segment_name=SYS_LOB0000119493C00006$$
segment_type=LOBSEGMENT

找到表明和LOB字段

select table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000119493C00006$$' and owner = 'HGHIS';

输出如下

table_name  = EMR_CASE
column_name = WORD

找到坏块的bad rowid,使用以下plsql脚本

create table bad_rows (row_id ROWID,oracle_error_code number);

set concat off
set serveroutput on


declare
  n number;
  error_code number;
  bad_rows number := 0;
  ora1578 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora1578, -1578);
begin
   for cursor_lob in (select rowid rid, &&lob_column from &&table_owner.&table_with_lob) loop
   begin
     n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
   exception
    when ora1578 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,1578);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,error_code);
     commit;   
   end;
  end loop;
  dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
end;
/

Enter value for lob_column: WORD
Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE 
可以查询bad rowid
select * from bad_rows;

更新空LOB字段来避免ORA-1578,ORA-26040,如果是CLOB类型，将empty_blob()改为empty_clob()

set concat off
update &table_owner.&table_with_lob set &lob_column = empty_blob() where rowid in (select row_id from bad_rows);

将bad rowid lob块移到其他表空间

alter table &table_owner.&table_with_lob move LOB (&&lob_column) store as (tablespace &tablespace_name);

最后别忘记rebuild index