ORACLE LOB段损坏导致应用频繁报错：ORA-00600 [25027][6]

mahan

应用频繁报错：ORA-00600 [25027][6]

检查报错的trc文件发现是一条INSERT语句导致

INSERT INTO MCS_CA_AUTHENTICATE_202412 .......

检查是否存在虚假索引
SQL> select do.owner,do.object_name, do.object_type,sysind.flags
     from dba_objects do, sys.ind$ sysind
     where do.object_id = sysind.obj#
     and bitand(sysind.flags,4096)=4096;

no rows selected
并没有

确认该表的用户
select owner,table_name,tablespace_name from dba_tables where table_name='MCS_CA_AUTHENTICATE_202412';

找到报错表的大对象字段(COLUMN_NAME)
select * from dba_lobs where table_name='MCS_CA_AUTHENTICATE_202412'

建立临时中间表
create table corrupt_lobs (corrupt_rowid rowid, err_num number);

找到坏块
declare
  error_25027 exception;
  error_1555 exception;
  pragma exception_init (error_1555,-1555);
  pragma exception_init (error_25027,-25027);

  n number;
begin
  for cursor_lob in (select rowid r,JSON_DATA from LUNA_MCS_CQERTONG.MCS_CA_AUTHENTICATE_202412) loop
      begin
        n:=dbms_lob.instr(cursor_lob.JSON_DATA,hextoraw('889911'));
        exception
          when error_1555 then
            insert into corrupt_lobs values (cursor_lob.r, 1555);
           commit;
          when error_25027 then
            insert into corrupt_lobs values (cursor_lob.r, 25027);
           commit;

       end;
   end loop;
end;

查询临时表
select * from corrupt_lobs

将坏块置空
update LUNA_MCS_CQERTONG.MCS_CA_AUTHENTICATE_202412 set JSON_DATA=empty_clob()
where rowid in (select corrupt_rowid from corrupt_lobs)

检查索引状态并重建索引
select index_name,status from dba_indexes where table_name='MCS_CA_AUTHENTICATE_202412'