已解决：expdp导出带有lob字段的表，报ORA-01555错误的问题

expdp导出带有lob字段的表，报ORA-01555错误的问题

 

1、情况描述

某数据库一直用expdp备份导出，但某天巡检发现expdp导出报错，是ORA-01555快照过旧，具体报错如下：

ORA-39126: Workerunexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS[INDEX:"SAPSR3"."LXE_OBJ_EXP_LANG~0"]
ORA-01555: snapshottoo old: rollback segment number 11 with name "_SYSSMU11_2268899145$"too small
ORA-06512: at"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at"SYS.KUPW$WORKER", line 11259

2、问题解决方案

1)查看undo表空间，发现只占用了1%

 

 

2)查看undo_retention 是900秒(15分钟)， (undo_retention是undo数据可以保留的最长时间)

SQL> show parameter undo

NAME                                 TYPE        VALUE
----------------------------------------------- ------------------------------
_in_memory_undo                      boolean     FALSE
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      PSAPUNDO

3)关于undo_retention官方文档有如下两点说明：

 

1：For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specifiedby UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, thedatabase may begin to overwrite unexpired undo information.

大体意思是  数据库可以在undo_retention设置的时间里保留undo数据，当空间不足时，如果undo表空间是可以自动扩展的，那是可以自动扩展空间来保留更多的undo数据，而不是覆盖未到期(未到undo_retention设置的时间)的undo数据

 

2：If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention periodto be somewhat longer than the longest-running active query on the system. However,this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data isretained to support these operations. To better accommodate Oracle Flashback features, you can either set the undo_retention parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.

大体意思是  如果undo表空间的自动扩展的，那数据库可以动态的调整undo retention的保留时间来支持长查询。Retention保留时间可能不足以支持闪回操作时，会返回快照过旧的错误，必须要保留足够的undo数据来支持闪回查询。最好的建议是设置UNDO_RETENTION参数到一个支持最长查询的时间。

 

但官方文档这里有个关于lob的注释：自动调整对于lob字段的不支持

Note: Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undotablespace. For LOBs, the database attempts to honor the minimum undo retentionperiod specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten.

 

4)查看了下导出的表，确实是有包含有lob字段的表。上述的快照过旧问题，因为undo表空间足够用，这里就增大undo_retention时间即可，这里就手动修改undo_retention到1800(30分钟)

SQL> alter system set undo_retention=1800 scope=both;

修改后可以正常导出了。