Is there a way to manually clear UndoTablespace?

Is there a way to manually clear UndoTablespace.

For our Prod DB we take daily backup using Data Pump. Schema size is around 120GB.  Backup takes more than 12hrs to complete. We noticed that whenever we restart the DB and then run the backup it complete merely in 1.5 or 2hrs.

I suspect it might be due to uncommitted transactions which might be there in undotablespace and after restart it gets clear, which results in speed up the backup.

Could anyone please let me know what could be causing the issue.
cloudtechnicianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
What you are talking about is read consistency.  From the time the select statement that datapump is using starts to the time it completes you get the same view of the table that you are selecting from.  So, if the select statement takes 10 minutes, and 5 minutes into it a user updates and commits a change, the select will see the previous version of the row.

The only uncommitted transactions that would interfere with read consistency are ones that have been written to disk and you should only see this on a really active database.

Datapump is not a backup.  It is a method to copy data, but really shouldn't be considered a viable backup.  It cannot be rolled forward.  If you are OK with losing data then it can be viable, but I would only use it if the database was started with STARTUP RESTRICT so that no users could be doing anything while it was running.  You have a better chance for consistency that way.

FYI - you cannot force undo to be marked as unneeded or "flush" it.
0
slightwv (䄆 Netminder) Commented:
Remember from your previous questions but I have to state it again:  Export is not a backup.

To answer the first question:  I do not know of any way to 'purge' the undo short of a shutdown immediate.  Even if there was, it would be fairly dangerous to do and would likely result in some data issues.

Are there a lot of users on when you run your export?  It might not be uncommitted transactions.  Instead it might be committed ones.  If the export is trying to be consistent and a lot of DML happens while the export runs, it will have to go to the redo to grab the older blocks.
1
cloudtechnicianAuthor Commented:
slightwv, we'll be implementing RMAN backup in few months. I understand it's the only reliable and recommended way to take backups.

Are there a lot of users on when you run your export?  
No there are not many users when the script execute and perform export.

The only confusion is why does backup complete in 2hours(within maintenance window) after restarting DB which otherwise takes more than a day time.
0
slightwv (䄆 Netminder) Commented:
Not sure why it only takes two hours after a restart.

Are you just talking about just the export time or the complete end to end process?

I cannot think of anything in a newly started database that would affect export in such a way over one that has been running.

I'm pretty sure it isn't anything in UNDO.

I would look at what system resources are maxing out between the two different runs.  That should help narrow down where to look.  My gut feeling is memory not disk io.  See if you are swapping to disk.  That will slow things down to a crawl!!

>>I understand it's the only reliable and recommended

To clarify:  It is not the "only" way.  It is the recommended way.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.