Skip to Main Content
  • Questions

  • Basic question to Undo/Undo Retention regarding EXPDP with Flashback_Time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Markus.

Asked: March 08, 2017 - 2:33 pm UTC

Last updated: March 09, 2017 - 3:37 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hey guys,

first thanks for the incredible work here. I searched the blog for an answer to my question but I feel like it's kinda pretty basic and therefore probably not there already.

So, from my understanding if I do a datapump export with flashback_time the expdp will export all the data from a schema "snapshotted" state, which it gets out of the Undo Tablespace.

The most important settings to this are the undo retention, which would set the time how long this snapshot can be stored in the Tablespace and of course the size of the tbs, so that the snapshot can idle in the Tablespace for the retention time, while new data can allocate the Tablespace.

I hope until here I am correct. Now I've encountered a situation with a test database where absolutely nothing was going on - no jobs, no sessions - just me.

The undo retention was set to the default of 900 (which is incredibly low in my opinion if you ever want to do a expdp?) and I ran into an snapshot too old error. After I increased the value to 7200 it worked out but also took 3 hours - which is longer than the 7200 seconds I entered. So I am kinda lost here.

So long story short, here are my questions about this behaviour:

- Does the undo_retention triggers an snapshot too old error if the expdp runtime is longer than the undo_retention time - no matter if there is no new data at all for the undo tbs? (How could it run for 3 hours then, if the retention was set to 7200)
- Why is there such a low default value of 900 for this important parameter?
- Does the export itself or regular rdbms background jobs/processes produce such undo that this could've be the cause of my problem?

Best regards and thanks in advance,
Markus

and Connor said...

undo_retention = x is like you saying to us,

"As a guide, I'm letting you know two things

- I'm unlikely to ask for data older than 'x'
- Once undo is older than 'x', I dont mind if you ditch/resuse it"


So if your undo retention is set to 900, whilst we wont *aggresively* go hunting around for anything older than that...BUT at the same time, we'll have no compunction to hang on it to either. We wont create/consume *more* undo space, because its generally quicker and more efficient for us to recycle stuff that is now out of date ... because after all, you *told* us we could reuse it :-)

So it *might* take only a few small new transactions to grab (and reuse) some older undo information that just happened to be important to your datapump job. By the same token, those few transactions might have just as likely grabbed some other undo slots and left the ones you needed untouched. Bit of a lottery there - so if your export runs for 'n' seconds, and you *always* want it to work, you'd strongly consider having undo_retention of at least that.

There's somewhat of an irony here - because datapump jobs themselves perform transactions :-)

A nice Oracle magazine article on the topic here

https://asktom.oracle.com/magazine-archive.htm

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.