1

ORA-01555: snapshot too old故障排查

麦华锦 2021-03-15
2310

故障现象
上报程序报错:

数据库中alter日志报错:

对数据参数进行调整:
1、把undo_retention字段设置成1800、5000、18000;
2、重建undo表空间,并将表空间增大到30GB,并设置数据默认回滚表空间为新表空间UNDOTBS2。

原因分析:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later Information in this document applies to any platform.
SYMPTOMS
ORA-1555 or snapshot too old error reported on a LOB segment. The error message looks like:
ORA-01555: snapshot too old: rollback segment number with name “” too small and sometimes followed by ORA-22924 error.
ORA-01555: snapshot too old: rollback segment number with name “” too small ORA-22924: snapshot too old
CAUSE
LOB data doesn’t use the Undo segment for retaining the read consistent images. The old versions are stored in the LOB Segments itself, before any DMLs.
The ORA-01555 on a LOB segment is reported generally in two cases:
a) The query is accessing a LOB segment which is corrupted
OR
b) Read consistent images of the LOB data is not available in the LOB Segment. This happens due to the wrong setting of PCTVERSION / RETENTION attributes of the LOB segment.

解决方法:
SOLUTION

  1. The first step to resolve ORA-1555 on LOB column is to check for corruption. This is the most common case.
    1.a) Create a dummy table for storing all rowids of the corrupted LOBs. Let’s call it “corrupt_lobs”
    SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
    1.b) Find the column names containing LOB data. You can DESCRIBE the table encountering the error and note down the columns names with datatype CLOB and BLOB.
    1.c) Execute the following PL/SQL block to identify the corrupted rows. Ensure to replace and with the respective LOB column and table name.
    查找已损坏lob字段记录脚本:
    declare
    error_1578 exception;
    error_1555 exception;
    error_22922 exception;
    pragma exception_init(error_1578,-1578);
    pragma exception_init(error_1555,-1555);
    pragma exception_init(error_22922,-22922);
    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_1578 then
    insert into corrupt_lobs values (cursor_lob.r, 1578);
    commit;
    when error_1555 then
    insert into corrupt_lobs values (cursor_lob.r, 1555);
    commit;
    when error_22922 then
    insert into corrupt_lobs values (cursor_lob.r, 22922);
    commit;
    end;
    end loop;
    end;

    ----数据库中包含LOB字段的表(content)
    COMMON_PICTURE_THUMBNAIL、COMMON_BIGTEXT_CLOB、B_COMMON_FILE、COMMON_ATTACHMENT COMMON_PICTURE;

    -----发现损坏的rowid

    ----具体记录

    select * from COMMON_ATTACHMENT where rowid=‘AAAVeeAAdAAFT7dAAS’;

    【备注】content未处理前显示为
    select * from common_picture where rowid=‘AAAVenAAeAAEERNAAW’;

    【备注】content未处理前显示为

    ------------------------把有问题的Rowid中的content字段置为空
    update common_picture
    set content = empty_blob()
    where rowid=‘AAAVenAAeAAEERNAAW’;
    commit;

    update LOBDATA set document = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);

    问题得到解决。

    1、问题的定位方向非常重要,不要被网络上的凌乱信息扰乱思路;
    2、善于发散思维、猜测问题;
    3、通过Oracle metalink或者通过google查找深度资料。

    「喜欢文章,快来给作者赞赏墨值吧」
    【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

    评论