Wednesday, October 5, 2011

ORA-01555: snapshot too old during datapump export


Datapump Export can fail with the following error when the parameter FLASHBACK_TIME is used
ORA-31693: Table data object "SCOTT"."EMPLOYEES" 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 271 with name "_SYSSMU271$" too small
ORA-00604: error occurred at recursive SQL level 3
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1^@
ORA-00604: error occurred at recursive SQL level 3
ORA-08180: no snapshot found based on specified ti

The reason for this is the database parameter UNDO_RETENTION is set to low compared to the time set with FLASHBACK_TIME.

Example:

- export starts at 10:00 pm and ends at 00:00 am. Flashback time start at 10:00 pm of the same days when export starts.

Expdp may need 2 hours to complete and undo_retention is set to 2040 that means 34 minutes. So the information in undo is recycled.

Solution
Set UNDO_RETENTION to a value sufficient to get all info required by the flashback time.
Also consider increasing undo tablespace size.

0 comments:

Post a Comment

 

ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com