Re: snapshot too old error
Date: Tue, 1 Jun 2010 02:49:58 -0700 (PDT)
Message-ID: <f4126943-ccfe-4fff-824e-a90f31c16a80_at_m4g2000vbl.googlegroups.com>
On 31 Mai, 22:43, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I am on Oracle 11.1.0.7 and HP UNIX 11.23i.
> I am doing an expdp and get following error
> expdp system/password directory=expdp_dir dumpfile=expdp.dmp
> logfile=texpdp.log schemas=PQMS PARALLEL=1 exclude=statistics
> compression=all
> ORA-31693: Table data object ""COMPLAINT_LTR_ORIG" 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 6 with name
> "_SYSSMU6_1265054678$"
> My undo size can grow to 8G, and I see it initial size as 2G; so most
> likely rest of undo was not used and increasing undo size will not
> help.
> I have a 24x7 system, and I want to do an export data pump without
> error. How can I overcome this error? Will adding a flashback_time to
> expdp help. Based on my knowledge of exp, it will make things worse as
> for as ORA-1555 is concerned.
zigzagdna,
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 everythig 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.) Dont do transactions while running such an extended query (which will not be possible in real environments) b.) Change the undo_retention - pararameter 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. Received on Tue Jun 01 2010 - 04:49:58 CDT