Prev: Our customer websites are modern, fresh and accessible.
Next: We are here for all your events!
From: zigzagdna on 31 May 2010 16:43
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.
From: phil_herring on 31 May 2010 21:08
This could be due to rollback space issues, or it could be due to
delayed block cleanout, a fairly complex issue that Tom Kyte explains
here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923


-- Phil
From: Carlos on 1 Jun 2010 03:26
On Jun 1, 3:08 am, "phil_herr...(a)yahoo.com.au"
<phil_herr...(a)yahoo.com.au> wrote:
> This could be due to rollback space issues, or it could be due to
> delayed block cleanout, a fairly complex issue that Tom Kyte explains
> here:
>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2....
>
> -- Phil

Ah! The good old ORA-01555!

It's good to know that some things never change. Even in the all-new
Oracle 11g with all these buzz and whistles!

Cheers.

Carlos.
From: magicwand on 1 Jun 2010 05:49
On 31 Mai, 22:43, zigzagdna <zigzag...(a)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.
From: Mladen Gogala on 1 Jun 2010 05:58
On Mon, 31 May 2010 13:43:55 -0700, zigzagdna wrote:

> 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.

Impose a shared lock on the table.



--
http://mgogala.byethost5.com
 |  Next  |  Last
Pages: 1 2 3
Prev: Our customer websites are modern, fresh and accessible.
Next: We are here for all your events!