oracle出现数据坏块ORA-01578 ORA-01110 ORA-26040解决办法。

问题描述：

Caused by: java.sql.SQLException: ORA-01578: ORACLE 数据块损坏 (文件号 4, 块号 234413)
ORA-01110: 数据文件 4: 'D:\APP\ADMINISTRATOR\ORADATA\OA\USERS01.DBF'
ORA-26040: 数据块是使用 NOLOGGING 选项加载的

解决办法：

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

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

①、如果是对象，可以重建

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 tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=4 and 234413between block_id AND block_id + blocks - 1;

输出如下

owner=EZOFFICE
segment_name=SYS_LOB0000119493C00006$$
segment_type=LOBSEGMENT

a.找到表明和LOB字段

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

输出如下

table_name = OA_MAILINTERIOR
column_name = MAILCONTENT

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

create table bad_rows (row_id ROWID,oracle_error_code number);
set concat off
set serveroutput on

c.执行下面语句

declare
      n number;
      error_code number;
      bad_rows number := 0;
      ora1578 EXCEPTION;
      ora600 EXCEPTION;
      PRAGMA EXCEPTION_INIT(ora1578, -1578);
      PRAGMA EXCEPTION_INIT(ora600, -600);
   
   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 ora600 then
        bad_rows := bad_rows + 1;
        insert into bad_rows values(cursor_lob.rid,600);
        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;
   /

d、提示输入信息

Enter value for lob_column: MAILCONTENT
Enter value for table_owner: EZOFFICE
Enter value for table_with_lob: OA_MAILINTERIOR

e.输入结果如下

old 11: for cursor_lob in (select rowid rid, &&lob_column from &&table_owner.&table_with_lob) loop
new 11: for cursor_lob in (select rowid rid, B from SYS.LOBDEMO) loop
old 13: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
new 13: n:=dbms_lob.instr(cursor_lob.B,hextoraw('889911')) ;
Total Rows identified with errors in LOB column: 1

PL/SQL procedure successfully completed.

f.可以查询bad rowid

select * from bad_rows;

输出结果：

ROW_ID             ORACLE_ERROR_CODE

------------------ -----------------

AAAVfyAABAAAXlJABG              1578

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

set concat off
update EZOFFICE.OA_MAILINTERIOR set MAILCONTENT= empty_clob() where rowid in (select row_id from bad_rows);

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

alter table EZOFFICE.OA_MAILINTERIOR move LOB (MAILCONTENT) store as (tablespace sysnux);

最后别忘记rebuild index

alter index 索引名称 rebuild;

否则会报错

ORA-01502: 索引或这类索引的分区处于不可用状态