故障描述:
db alert日志显示4月1日18:00后报错“DDE: Problem Key ‘ORA 600 [ktspgfb-1]’ was completely flood controlled (0x6)”
原因分析:
参照官方metalink 1968287.1所述,为主机异常重启时oracle系统视图位图信息产生损坏所致。
解决措施:
- 新建表空间EOMS35DB2
- 将可疑的lob字段迁移至新表空间EOMS35DB2
处理结果:
失败,迁移过程中报错“ORA-01555: snapshot too old: rollback segment number with name " " too small”
参照metalink 833635.1所述,主机异常重启时应用数据所导致的lob段也存在损坏,
以下为具体检测过程:
- 查询业务数据中所有含有lob字段的表
select A.OWNER,a.table_name,a.column_name,a.segment_name,b.bytes/1024/1024 M from dba_lobs a,dba_segments b
where a.segment_name=b.segment_name
and a.owner in ('BPEDB','EOMS35');
输出结果示例如下:
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------------------ -------------------
BPEDB ACTIVITY_INSTANCE_B_T UNHANDLED_EXCEPTION
- 运行plsql代码
- 创建用于存储检测结果的表
create table corrupted_lob_data (corrupted_rowid rowid);
- 执行检测
set concat off
declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
num number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/
输入以下变量:
Enter value for lob_column : UNHANDLED_EXCEPTION
Enter value for table_owner : BPEDB
Enter value for table_with_LOB: ACTIVITY_INSTANCE_B_T
3) 待上一步检测完成,查看结果
select * from corrupted_lob_data;
CORRUPTED_ROWID
------------------
AAAW3XAAtAAC+rMAA7
AAAW3XAAtAAC+urAAq
AAAW3XAAtAAC+urAAy
AAAW3XAAtAAC+urAAz
以上结果表明lob字段中确实存在部分数据损坏
解决措施:
与客户沟通后,将存在问题的数据行delete掉,重新执行数据导出,继而再导入至新建的数据库中
delete from BPEDB.ACTIVITY_INSTANCE_B_T where rowid in (
'AAAW3XAAtAAC+rMAA7',
'AAAW3XAAtAAC+urAAq',
'AAAW3XAAtAAC+urAAy',
'AAAW3XAAtAAC+urAAz');
commit;