今天处理了一个致远OA系统报ORA-01555案例，把整个过程分享给各位小伙伴，希望能帮助到您。
问题现象
下午接到OA运维人员反馈，致远OA系统在处理流程的时候报错。报错如下：
org.springframework.jdbc.UncategorizedSQLException:Hibernate operation: could not load an entity: [com.seeyon.ctp.workflow.po.CaseRunDAO#-1651291315951811991]; uncategorized SQLException for SQL
[select caserundao0_.id as id785_0_, caserundao0_.casename as casename785_0_, caserundao0_.startuser as startuser785_0_, caserundao0_.lastperformer as lastperf4_785_0_, caserundao0_.state as state785_0_, caserundao0_.subprocess as subprocess785_0_, caserundao0_.processindex as processi7_785_0_, caserundao0_.processname as processn8_785_0_, caserundao0_.processid as processid785_0_, caserundao0_.startdate as startdate785_0_, caserundao0_.updatedate as updatedate785_0_, caserundao0_.caseobject as caseobject785_0_, caserundao0_.case_content as case13_785_0_ from wf_case_run caserundao0_ where caserundao0_.id=?];
SQL state [72000]; error code [1555]; ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小
ORA-22924: 快照太旧
; nested exception is java.sql.SQLException: ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小
ORA-22924: 快照太旧
问题分析
看到ORA-01555报错的时候，DBA通常的解决思路是增加回滚段和调整undo_retention。但根据OA系统运维人员提供的信息因为SQL语句的条件是主键，大致判断可能问题不是因为上述两点。
处理过程
我们先尝试增加回滚段和undo_retention，将默认的900增加到14400，同时添加5G回滚文件。
[oracle@OA dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 17 18:53:44 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention=14400 scope=both;
System altered.
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 14400
SQL> alter tablespace UNDOTBS1 add datafile '/data/gkxb6test/datafile/undotbs02.dbf' size 5g autoextend off;
Tablespace altered.
SQL>
加完以后，OA运维人员尝试报错依旧。再通过系统层面设置事件，生成相应trace文件继续找原因。
SQL> alter system set events '1555 trace name errorstack level 3';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OA dbs]$
[oracle@OA dbs]$ expdp \'/ as sysdba\' directory=dmpdir dumpfile=v3x20240617.dmp logfile=v3x20240617.log tables=V3XUSER.WF_CASE_RUN
Export: Release 11.2.0.4.0 - Production on Mon Jun 17 19:27:43 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=dmpdir dumpfile=v3x20240617.dmp logfile=v3x20240617.log tables=V3XUSER.WF_CASE_RUN
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.452 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "V3XUSER"."WF_CASE_RUN" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/v3x20240617.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Mon Jun 17 19:34:58 2024 elapsed 0 00:07:14
[oracle@OA dbs]$ cd /u01/app/oracle/diag/rdbms/gkxb6tes/gksxoa/trace
[oracle@OA trace]$ tail -20 alert_gksxoa.log
Mon Jun 17 19:22:32 2024
Dumping diagnostic data in directory=[cdmp_20240617192232], requested by (instance=1, osid=19303), summary=[abnormal process termination].
Mon Jun 17 19:23:30 2024
Errors in file /u01/app/oracle/diag/rdbms/gkxb6tes/gksxoa/trace/gksxoa_ora_19420.trc:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Mon Jun 17 19:23:31 2024
Dumping diagnostic data in directory=[cdmp_20240617192331], requested by (instance=1, osid=19420), summary=[abnormal process termination].
Mon Jun 17 19:27:44 2024
DM00 started with pid=32, OS id=19677, job SYS.SYS_EXPORT_TABLE_01
Mon Jun 17 19:27:44 2024
DW00 started with pid=33, OS id=19680, wid=1, job SYS.SYS_EXPORT_TABLE_01
XDB installed.
XDB initialized.
Mon Jun 17 19:34:57 2024
Errors in file /u01/app/oracle/diag/rdbms/gkxb6tes/gksxoa/trace/gksxoa_dw00_19680.trc:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Mon Jun 17 19:34:58 2024
Dumping diagnostic data in directory=[cdmp_20240617193458], requested by (instance=1, osid=19680 (DW00)), summary=[abnormal process termination].
[oracle@OA trace]$
根据报错提示的表我们发现expdp备份这个表的时候也发生ORA-01555，这就很像Doc ID 833635.1这个案例，LOB字段损坏造成。这里我们要注意的是和致远OA工作流相关有几个表LOB字段都有可能出现这样的报错，本案例是表WF_CASE_RUN，先定位产生损坏LOB字段的ROWID，然后通过ROWID来删除或者更新有问题那条记录，本次我们是将LOB字段设置为空。最后把特殊事件产生trace文件关闭。
WF_PROCESS_RUNNING表，PROCESSOBJECT字段BLOB类型，PROCESS_XML字段CLOB类型
WF_CASE_HISTORY表，CASEOBJECT字段BLOB类型，CASE_CONTENT字段CLOB类型
WF_CASE_RUN表，CASEOBJECT字段BLOB类型，CASE_CONTENT字段CLOB类型
SQL> create table corrupted_lob_data (corrupt_rowid rowid, err_num number);
Table created.
SQL> desc V3XUSER.WF_CASE_RUN
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
CASENAME VARCHAR2(100)
STARTUSER NOT NULL VARCHAR2(36)
LASTPERFORMER VARCHAR2(36)
STATE NOT NULL NUMBER(38)
SUBPROCESS NUMBER(38)
STARTSUBPROCESSNUM NUMBER(38)
PROCESSINDEX NOT NULL VARCHAR2(110)
PROCESSNAME NOT NULL VARCHAR2(100)
PROCESSID NOT NULL VARCHAR2(100)
STARTDATE NOT NULL NUMBER(38)
UPDATEDATE NOT NULL NUMBER(38)
CASEOBJECT BLOB
CASE_CONTENT CLOB
SQL> 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);
n number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
begin
n := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupted_lob_data values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data values (cursor_lob.r, 22922);
commit;
end;
end loop;
2 3 4 5 end;
/ 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
Enter value for lob_column: CASE_CONTENT
Enter value for table_owner: V3XUSER
Enter value for table_with_lob: WF_CASE_RUN
old 10: for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
new 10: for cursor_lob in (select rowid r, CASE_CONTENT from V3XUSER.WF_CASE_RUN) loop
old 12: n := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
new 12: n := dbms_lob.instr (cursor_lob.CASE_CONTENT, hextoraw ('889911')) ;
PL/SQL procedure successfully completed.
SQL> select * from corrupted_lob_data;
CORRUPT_ROWID ERR_NUM
------------------ ----------
AAAWBiAAFAAE0z/AAF 1555
SQL> select * from V3XUSER.WF_CASE_RUN where rowid in (select corrupt_rowid from corrupted_lob_data);
ERROR:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
SQL> update V3XUSER.WF_CASE_RUN set CASE_CONTENT=empty_clob() where rowid in (select corrupt_rowid from corrupted_lob_data);
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system set events '1555 trace name errorstack off';
System altered.
SQL>
至此，OA运维人员反馈问题解决。
