Link to home
Start Free TrialLog in
Avatar of sachin_dba
sachin_dbaFlag for India

asked on

Oracle UNDO retention

Database export is failing due to Snapshot too old.

Expdp runs for 10 hours to backup 200GB database before it fails.

We have sufficient space on default undo tablespace. I have changed the retention from 900 to 2400 but it is still failing.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     2400
undo_tablespace                      string      UNDOTBS3

UNDOTBS2                       NOGUARANTEE
UNDOTBS3                       NOGUARANTEE
UNDOTBS4                       NOGUARANTEE

Please suggest.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>10 hours to backup 200GB

Since you used the term 'backup', are you trying to use expdp as a database backup?  If so, I would not.  It reallsy isn't meant to be a backup method.  I would switch to RMAN.

To the question: Increase the retention to a larger number.
If your process is running for 10 hours, then undo_retention should be at least 36000.

That being said, there is still no guarantee that it will be able to go the full 10 hours without having to reuse some of the space.

Can you break up your export into multiple exports to get done what you need?  That would help.
Another valid approach may be use Dataguard to maintain a standby copy; then perform your backup off of the copy -- assuming that the production impact of the extended outage is what you want to correct.
Avatar of sachin_dba


I have increased the  undo_retention to 36000 and will execute expdp tonight.

Still failing.

ORA-01555: snapshot too old: rollback segment number 65 with name "_SYSSMU65_710321539$" too small

We are using parallel=4 but one dump file is 175GB and rest on 2GBs
Avatar of johnsone
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now it is working fine however the dump file sizes are not even.

We are using parallel=4 but one dump file is 175GB and rest on 2GBs. Shall I change something here?
My guess is one dump file has a single large table in it.
Nods, "if it ain't broke don't fix it".
After setting retention time to very high number. Exports are working,