ORA-01555案例分享

一、通过修改参数

最近对某数据库执行数据泵导出操作时碰到如下错误：
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ZXUSER"."T_MX_ATTACHMENT" 9.452 GB 9549 rows
ORA-31693: Table data object "SGBIZ"."T_MX_ATTACHMENT" 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_FULL_01" successfully loaded/unloaded

首先修改undo_retention=10800重新导出问题依旧。
alter system set undo_retention=10800 sid='*';
$ oerr ora 22924
22924, 00000, "snapshot too old"
// *Cause: The version of the LOB value needed for the consistent read was
// already overwritten by another writer.
// *Action: Use a larger version pool.

--才明白这个跟lob字段有关。

select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';
col COLUMN_NAME for a30
select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';

COLUMN_NAME PCTVERSION RETENTION
------------------------------ ---------- ----------
ATT_FILE 900

--这个表本身不大，但是lob字段占用的空间很大，可以发现即使改动了参数undo_retention=10800，表lob的属性
--RETENTION依旧900，必须增加该参数，或者使用PCTVERSION参数。

alter table SGBIZ.T_MX_ATTACHMENT modify lob(ATT_FILE)(retention);
alter table SGBIZ.T_MX_ATTACHMENT modify lob(ATT_FILE)(PCTVERSION 20);

select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';
COLUMN_NAME PCTVERSION RETENTION
-------------------- ---------- ----------
REPORTPRINT 10800

修改后重新导出，导出正常。
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ZXUSER"."T_MX_ATTACHMENT" 9.452 GB 9549 rows
. . exported "SGBIZ"."T_MX_ATTACHMENT" 21.30 GB 35716 rows
Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded

二、查看是否存在log坏快

使用数据泵导出数据库数据时，发现如下错误提示：

 

1 2 3 4

ORA-31693: Table data object "CAMS_CORE"."BP_EXCEPTION_LOG" 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

 

1.查看表空间使用率

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名", 　　D.TOT_GROOTTE_MB AS "表空间大小(M)", 　　D.TOT_GROOTTE_MB-F.TOTAL_BYTES AS "已使用空间(M)", 　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比", 　　F.TOTAL_BYTES AS "空闲空间(M)", 　　F.MAX_BYTES AS "最大块(M)" 　　FROM (SELECT TABLESPACE_NAME, 　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 　　FROM SYS.DBA_FREE_SPACE 　　GROUP BY TABLESPACE_NAME) F, 　　(SELECT DD.TABLESPACE_NAME, 　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 　　FROM SYS.DBA_DATA_FILES DD 　　GROUP BY DD.TABLESPACE_NAME) D 　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;

2.看到ORA-01555错误，还以为是经典错误，尝试调整undo_retention参数 

 

1	SYS@cams>alter system set undo_retention=30000 scope=both;

修改后再次导出，问题依旧存在，显然问题和 undo_retention没关系，再把参数改回去。 

 

3.猜测是表空间有问题，这里尝试对 CAMS_CORE下的索引和LOB 进行表空间迁移。

（1）新建新的表空间

（2）拼接表空间迁移语句，前面已有文章写到了表空间迁移方案

（3）执行表空间迁移语句

 

1	alter table CAMS_CORE.BP_EXCEPTION_LOG move lob(EX_STACK) store as (tablespace cams_core_lob);

执行到该语句的时候提示错误： 

 

1 2	ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小 ORA-22924: 快照太旧

这里，问题应该比较明显了，有部分 LOB数据有问题。

 

4.寻找问题解决方案（MOS）

使用关键字 “expdp ORA-01555 ORA-22924  LOB”进行查找：

Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (文档 ID 833635.1)

 

 

5.参考MOS给出的解决方案，动手处理问题

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

set concat off    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 table_owner: EX_STACK Enter value for table_owner: CAMS_CORE Enter value for table_with_lob: BP_EXCEPTION_LOG old   6:   for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop new   6:   for cursor_lob in (select rowid r, EX_STACK from CAMS_CORE.BP_EXCEPTION_LOG) loop old   8:       num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ; new   8:       num := dbms_lob.instr (cursor_lob.EX_STACK, hextoraw ('889911')) ;    PL/SQL procedure successfully completed.

 

查看存在问题的数据记录：

 

1 2	select * from CAMS_CORE.BP_EXCEPTION_LOG where rowid in ( select * from CAMS_CORE.corrupted_lob_data );

确实存在 3条数据， CLOB 字段数据大小为 ，显然有问题。 

MOS上给出的导出方案是将问题数据exclude掉，这里为了彻底解决问题，将3条数据导出为csv文件，然后删除。然后再次导出数据库数据，不再提示报错。

 

6.结合应用分析问题的由来。

根据有问题的数据，让开发人员去检查应用日志。检查时发现对应时间点的应用日志有残缺，不能继续往下分析。同时，根据问题发生的时间点，了解到当时工程师在给服务器做迁移，结果服务器强制重启（应用和数据库一起），导致了部分数据损坏。