Friday, December 10, 2010

Snapshot Too Old Error During Export

ORA-31693: Table data object 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 11 with name "_SYSSMU11_1238457026$" too small

This occurred after we began using the FLASHBACK_TIME parameter for exports.

Thank you to the person who posted the solution here:  http://www.eggheadcafe.com/software/aspnet/36119822/snapshot-too-old-error.aspx

This error occures, when at least 2 conditions are met:
1.) A long running Query (q)
2.) At least 1 short transaction (t) that modifies AND commits data
which will be selected by (q)

When (q) starts, Oracle guarantees read consistency, meaning you can
be sure that the result set of (q) reflects the state the db was in at
the start of (q).

If transaction (t)  - started AFTER you started (q) - modifies data
that will be selected by (q) later on (because (q) is a long running
query), (q) will read the old, unmodified data out of the undo
segments and everything is as it should be.

The problem now is, that after (t) commits, the undo segments are
still holding the consistent data block(s), but are released and may
be used (and overwritten) by other transactions.

When (q) discovers the situation above, it will terminate with
ORA-1555.

There are basically 2 solutions to this problem:
a.) Don't do transactions while running such an extended query (which
will not be possible in real environments)
b.) Change the undo_retention - parameter of your database.
UNDO_RETENTION (the default is 900 seconds)  tells the system how
long to wait before reusing the undo - segments after they have been
released by the transaction.

So if your pump-export needs i.e. 1 hour you should set this parameter
to 3600 or higher.

Note however, that undo_retention will be set for ALL undo - segments
in your system, so - depending on your transaction structure - you
might need a bigger undo-tablespace.

No comments:

Post a Comment