Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: August 29, 2017 - 6:34 am UTC

Last updated: August 30, 2017 - 4:39 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Team,

I am taking expdp for schema backup now from last 4 Days we are getting error for one of table ORA-01555 snapshot too old.
My undo_retention =

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1


And undo size is
TS NAME size MB Used MB Used % Autoextend Free MB STAT # of datafiles TS type EXT MGMT Seg Sp
------------------------------ ---------- ---------- ---------- ---------- ---------- --------- -------------- --------- ---------- ------
UNDOTBS1 8860 278.75 3.14616253 YES 8581.25 ONLINE 1 UNDO LOCAL MANUAL


And i have
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE

I have a space issue so i can not change this to guarantee and resize the undo tablespace.

And i am using expdp command is like -

expdp user/passw directory=JSPL dumpfile=user.dmp logfile=user.log consistent=no compression=ALL

How to avoid this snapshot too old error pls let me know ?

Thanks

and Connor said...

Without an undo retention guaranteed, your undo_retention is very much a "loose" guideline. You can see what kind of retention you are actually getting by looking at v$undostat

SQL> select begin_time, end_time, TUNED_UNDORETENTION
  2  from v$undostat;

BEGIN_TIME          END_TIME            TUNED_UNDORETENTION
------------------- ------------------- -------------------
30/08/2017 12:24:11 30/08/2017 12:33:41                 900
30/08/2017 12:14:11 30/08/2017 12:24:11                 900
30/08/2017 12:04:11 30/08/2017 12:14:11                 900
30/08/2017 11:54:11 30/08/2017 12:04:11                 900
30/08/2017 11:44:11 30/08/2017 11:54:11                 900
30/08/2017 11:34:11 30/08/2017 11:44:11                 900
30/08/2017 11:24:11 30/08/2017 11:34:11                 900
30/08/2017 11:14:11 30/08/2017 11:24:11                 900
30/08/2017 11:04:11 30/08/2017 11:14:11                2005
30/08/2017 10:54:11 30/08/2017 11:04:11                1397
30/08/2017 10:44:11 30/08/2017 10:54:11                1388
30/08/2017 10:34:11 30/08/2017 10:44:11                1443
30/08/2017 10:24:11 30/08/2017 10:34:11                2037
30/08/2017 10:14:11 30/08/2017 10:24:11                2629
...


My undo_retention is 900, and sometimes I get that...sometimes I get a lot more (because my laptop database is mainly idle with minimal activity).

So you need the undo_retention to be higher than the time to scan your largest table in the datapump. So it is either a higher retention or a faster data pump. You could look at using parallelism or see if avoiding the compression can speed up the datapump elapsed time.

Also, check to see if you are exporting LOB's. If you are, then check MOS note 12656535.8 for details which might be impacting you.

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.