Database Refresh Part 1 – What Method To Use?

Refreshing databases is part of the DBA’s job.
Since this is a repetitive task (you execute it many times, again and again), the best approach is to automate it, or automate as much as possible out of it.

Depending on the refresh method you choose, you would have two or three options that need automation: RMAN cloning, datapump refreshes, and cloning pluggable databases.
I am sure that people can come up with other methods to do refreshes, but I’ll stick with the above for now.

When looking at automating the refresh process, go through all the steps you perform manually. Best is to write everything down, documenting the refresh. Maybe you already have a document. In that case you can start automating.
If you just started a new job, and there is no documentation for a refresh, then this is your opportunity to create one.

Any database refresh, no mater the method, will have three main parts:

PRE-refresh steps: everything you do before the actual refresh.
Actual Refresh steps: everything you do to refresh the data with either rman or datapump.
POST-refresh steps: everything you do after the actual refresh.

Any manual steps you curently execute, can be automated. Some things might be more complicated, but they can be automated or scripted.

How do you determine what type of refresh to use, RMAN or datapump?

There are a few factors that will determine the method:

a) The purpose of the refresh.

Are you testing your production backups and must prove that you can recover from them? Is the purpose of refresh for SOX, CSOX or other type of compliance?
If you need to prove you can recover from existing backups, you must use RMAN.
If you don’t need to prove anything, then maybe datapump is the way to go.

b) How many schemas (applications) does the database host?

Is your database hosting only one application or multiple applications? If you have multiple applications running out of a single database, you might not be able to refresh the whole database.
You will only refresh one or two schemas. In that case the method to use for the refresh will be datapump.
If you need to refresh the database as a whole, because of dependencies between schemas, then the way to go is an RMAN refresh.

c) Duration of the refresh.

If you can choose any way to refresh, because you are not constrained by a) or b), then the speed of the refresh might come into play.
Depending on the size of the database or schema, datapump could be faster than RMAN or vice versa. How much time do you want to spend on the refresh?
If datapump refresh is much faster than RMAN, then choose datapump. If RMAN is faster, then choose RMAN.

I am sure there are other factors that come into play when determining the method! If you think of another one, leave a comment below! I read all the comments.

Since I like to keep articles readable and short, I invite you to check back next week for the refresh steps whether you choose RMAN or datapump!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!