Oracle
11.2 Datapump ORA-31693 ORA-02354 ORA-01555
Error Description:
ORA-31693: Table data object
"TRIP"."BOOKINGCHANGELOG" 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 60
with name "_SYSSMU60_502175397$" too small
Reason:
There can be multiple reasons for it.
1.
MSSM (Manual Segment Space Management)
Reference link Metalink Note
800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a
MSSM tablespace
ORA-1555/ORA-22924 may occur
when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.
This may occur when LOB column resides in a MSSM (Manual Segment Space
Management) tablespace.
2.
Export has been started with Flashback_TIME option
to get consistent copy of data which makes database to keep data in UNDO
segments for longer duration. In other words, The reason for this is the
database parameter UNDO_RETENTION is set to low compared to the time set with
FLASHBACK_TIME.
Solution:
1.
Increase UNDO_RETENTION parameter to a value
sufficient to get all info required by the flashback time.
2.
Also, consider increasing undo tablespace size.
In theory, Snapshot too old error is caused only because of Oracle's multi-version concurrency control. Any other database implementation that does not implement multi-version concurrency will never have this error right? This particular error has probably occurred several million times in Oracle databases in production. Having large undo tablespace will prevent this error to some extent. concurrency control with Oracle comes with a price: It links successful execution of Oracle's transactions with size of rollback segment for maintaining read consistency. I am no more a fan of multi version concurrency control though it sounded nice initially.
ReplyDeleteIncrease UNDO_RETENTION parameter SIZE I ALSO ADD UNDO TABLESPACE FILE IN ORACLE 10.2.0.5 BUT SAME PROBLEM OCCORS IN EXPDP ORA-31693,ORA-02354,ORA-01555
ReplyDelete