RE: IMPDP, ORA-1555 and UNDO_RETENTION

UNDO_RETENTION is about time, not size. So if the time to complete the impdp
including the index creation is longer in duration that UNDO_RETENTION, then
Oracle's RDBMS has been permitted to release rather than extend UNDO once
that amount of time has gone by, even if the retention column of
DBA_TABLESPACES for the relevant UNDO tablespace is GUARANTEE.

This, in turn, means that the relevant select may not be "huge," just long
running. I'm not sure WHY impdp has such a long running query on changing
underlying data, but making UNDO_RETENTION longer may well preserve this
information with relatively little addition to total UNDO. Having a small
UNDO_RETENTION just allows Oracle to skip adding more and re-use the expired
(that is older than retention and not involved in an active transaction)
bits of undo.

That said, it seems likely that running impdp without creating the indexes
and then creating the indexes separately could dramatically decrease the
time of the impdp run and possibly can provide you with faster completion of
the overall job, depending on how the indexes are then added.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Martin Klier
Sent: Wednesday, May 05, 2010 3:09 AM
To: Oracle-L Freelists
Subject: IMPDP, ORA-1555 and UNDO_RETENTION

Good CEDT morning, listers.

Something maybe considered as obvious caused me loosing time this week.
Importing with impdp failed with ORA-1555, and all sources said
"increase UNDO_RETENTION". But in my understanding, the undo retention
primarily was something to enable flashback table-like actions over this
amount of time. Okay, docs (11g admin guide) can be understood in a way
that this is merely a side-effect.  :)

"However, for consistent read purposes, long-running queries may require
this old undo information for producing older images of data blocks.
Furthermore, the success of several Oracle Flashback features can also
depend upon the availability of older undo information. "

But the real surprise is, how impdp works. Oracle support says - thats
my translation of it - "We are doing huge selects on temp tables and are
overwriting our consistency information by the updates on the live tables."

I've created a blog entry on this issue as well:
http://www.usn-it.de/index.php/2010/05/05/oracle-impdp-ora-1555-and-undo_ret
ention/

Sounds a bit like a weak flank in the concept. Any positions on that
from your side?

Thanks and best regards
-- 
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l


Other related posts: