Log in

Sangfor Support Center

Table of Contents
< All Topics
Print

[aCloud] Oracle database expdp exports a table with lob fields, reporting an ORA-01555 error

Posted
Updated
Byadmin
Views2

Problem Description

Frontline feedback: When a user uses expdp to backup and export a table with LOB fields, an ORA-01555 snapshot outdated error is reported.

execute the command:
expdp  "'/ as sysdba'" directory=DIR1 dumpfile=OA_APP_FORMMAIN_0090_20170522.dmp tables=OA_APP.FORMMAIN_0090

The specific error is as follows:
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  

Alarm Information

Check the errors reported when exporting, as follows:
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  

Process –

  1. Error analysis
    According to the above error message ORA-01555 snapshot is too old, it is determined that it is due to insufficient undo table space or undo_retention is too small.
    select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space
    where tablespace_name='UNDOTBS1' group by tablespace_name; found that there was 32g of undo tablespace remaining, through
    show parameter undo found that undo_retention is 900 seconds, which is a bit small and needs to be adjusted.

  2. Adjust parameters
    Adjust the parameters through the following statements:
    alter system set undo_retention=1800 scope=both;

  3. Re-execute expdp
    The test is normal after re-executing expdp.

Root Cause

The root cause of this problem is that the value of undo_retention is set too small.

Solution

Adjust the parameters through the following statements:
alter system set undo_retention=1800 scope=both;

Suggestions and Summary

During the maintenance of the Oracle database, the appropriate parameter values should be adjusted according to the specific business situation.

Original Link

https://support.sangfor.com.cn/cases/list?product_id=33&type=1&category_id=15631&isOpen=true