Many topics are to be found on the web about IMPDP and UNDO_RETENTION. This one will become a more theoretical one, around the central question: Why, please why does a huge IMPDP fail with ORA-1555 if there is too less UNDO_RETENTION configured?
Like so often, these questions come from a problem. I had to impdp about 30GB from one machine to another via database link. (For newcomers to this technology, this is done via impdp on the target machine, no expdp needed, no dumpfile is created.) The impdp failed with ugly ORA-1555s after about 2 1/2 hours. It happened after the import itself, but during the index creation. The web said in several places, that increasing the UNDO_RETENTION period will help, but I have not been able to explain it. For my understanding, UNDO_RETENTION was something to enable flashback-table-like operations over a longer period in a busy database.
Oracle Support
So I opened a Service Request in order to get some clarification. The result was:
QUESTION
——–
Why does the undo_retention affect the impdp behaviour?
ANSWER
——
<…>
For the runtime of import the Data Pump Import utility maintains some auxilliary tables which will be permanently updated. Due to the small undo_retention the some data required for read consistency was stored in a undo block which was reused after undo_retention has passed and hence ORA-1555 occured while attempting to read the “old” undo information.
For me, in translation, this means: “We are doing huge selects on temp tables and are overwriting our consistency information by the updates on the live tables.” At least, good to know.
The docs
So I finally did what I really should have done earlier: Searching what Oracle recommends using UNDO RETENTION for. I found it in the Oracle® Database Administrator’s Guide, on the page “About the Undo Retention Period“:
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. 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.
Seems I was wrong: The undo retention feature PRIMARYLY is for the protection of huge queries, and enabling flashback table over a longer period is only the second purpose.
Conclusion
In combination, from the characteristics of impdp and from the (non-side) effect of UNDO_RETENTION parameter, that makes older undo blocks (of course, me stupid!) usable for huge queries, we now know the connection between the parameter and data pump imports (not?) failing with ORA-1555.
How to set the parameter
The logical conclusion and field-tested best practice approved by Oracle Support is, to set the UNDO_RETENTION parameter to at least the estimated time for the data pump import. Don’t forget to size your UNDO tablespace accordingly, since the retention only works as long as there is enough undo space available.
Note
IMPDP uses flashback technology (flashback table) on the source database to achive consistency, so the UNDO tablespace there is worth a glance as well.
I hope my issue helps to avoid future confusion at least on your side.
Regards
Martin Klier