执行expdp导出时报错-01555快照过旧

2019年10月11日 4005点热度 0人点赞 1条评论

■表RESACTIVEINFO导出失败,提示01555快照过旧
ORA-31693: Table data object "XXX"."RESACTIVEINFO" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3590435561$" too small

■表RESACTIVEINFO含有4个clob对象,查看dba_lobs视图,确认属性

select COLUMN_NAME,SEGMENT_NAME,INDEX_NAME,PCTVERSION,RETENTION from dba_lobs t where owner='XXX' and table_name='RESACTIVEINFO';
COLUMN_NAME          SEGMENT_NAME                   INDEX_NAME                          PCTVERSION       RETENTION
-------------------- ------------------------------ ------------------------------ --------------- ---------------
ACTIVEXML            SYS_LOB0000207899C00021$$      SYS_IL0000207899C00021$$                                   900
ACTIVERESPONSEXML    SYS_LOB0000207899C00022$$      SYS_IL0000207899C00022$$                                   900
RETURNXML            SYS_LOB0000207899C00023$$      SYS_IL0000207899C00023$$                                   900
RETURNRESPONSEXML    SYS_LOB0000207899C00024$$      SYS_IL0000207899C00024$$                                   900

注:LOB不支持undo的自动调整,对LOB字段undo保留值的调整默认基于UNDO_RETENTION参数
表的lob字段保存策略可以设置为retention/pctversion,默认是retention
alter table t modify lob(col1) (pctversion 10);
alter table t modify lob(col1) (retention);

■使用sysdba查询相关的clob字段使用什么保存策略

select decode(bitand(flags, 32), 32, 'Retention', 'Pctversion') ||
       ' policy used'
  from lob$
 where lobj# in
       (select object_id
          from dba_objects
         where object_name in (select segment_name
                                 from dba_lobs
                                where table_name in ('RESACTIVEINFO')
                                  and owner = 'XXX'));
DECODE(BITAND(FLAGS,32
----------------------
Retention policy used
Retention policy used
Retention policy used
Retention policy used

■数据库retention,默认15分钟

SYS@db1> show parameter retention
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_flashback_retention_target        integer     1440
undo_retention                       integer     900

■数据库undotbs大小

TABLESPACE_NAME             TOTAL(G)         USED(G)         FREE(G)        USAGE(%)
-------------------- --------------- --------------- --------------- ---------------
UNDOTBS1                       63.87              .9           62.97            1.41
UNDOTBS2                       63.87             .16           63.71             .25

■数据库retention,调整为1小时
alter system set undo_retention=3600 scope=both sid='*';
然后修改表的lob字段retention属性即可生效。
alter table RESACTIVEINFO modify lob(ACTIVEXML) (retention);
alter table RESACTIVEINFO modify lob(ACTIVERESPONSEXML) (retention);
alter table RESACTIVEINFO modify lob(RETURNXML) (retention);
alter table RESACTIVEINFO modify lob(RETURNRESPONSEXML) (retention);

■再次备份,完全成功
. . exported "XXX"."RESACTIVEINFO" 24.48 GB 43011846 rows
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Oct 12 00:03:48 2019 elapsed 0 00:58:45

liking

这个人很懒,什么都没留下

文章评论

  • liking

    注:前提是undo足够大,将保留时间调整为4倍大小即1小时,看看今晚自动备份的执行情况吧

    2019年10月11日