Database Refresh Part 2 – Datapump

Last week’s post was about automating the database refresh process. I recommended you to document every detail you follow, when refreshing one of your databases. Did you write down the steps? If not, then now it’s the time, go ahead and do it!

Today we’ll focus on the refresh steps in general when refreshing a database with datapump.

When refreshing a database with datapump export/import, you are most likely refreshing one or two schemas. To simplify, we will assume that we are refreshing one schema only.

It is easier to focus on the refresh steps, once you figure out what will change and what will not change during the refresh process.

Things that will change when you refresh a schema with datapump

• the content (all objects) of the schema
• the password for the schema if you drop it before the import.
• grants on the schema objects will get wiped out.
• public synonyms on the schema objects will become invalid
• database links
• scheduler jobs

It is important to know what will not change during a datapump refresh, so you know what not to worry about.

Things that will not change when you refresh a schema with datapump.

• the content of other schemas
• passwords of other schemas
• grants on other schema objects
• database links in other schemas
• scheduler jobs in other schemas

Now you have a better understanding for things that will change and things that will not change, and it is easier to focus on the pre/post steps of the refresh.

When refreshing a schema with datapump, I personally prefer not to drop the schema in the target database, and only drop the objects owned by the schema (with a few exceptions).

The following should be scripted to extract in the TARGET environment, before the refresh is started:

• database links that the schema owns, especially if the db links have passwords in their definition. This step is a must if you are going to drop the schema.
• privileges on the schema object should be preserved, aka grants. When you drop a table, the permissions granted on the table are also gone. Since you have different level of security in the
  target database compared to the source, those grants cannot come across, and will be lost unless you extract them beforehand.
  To make more sense, think about the following. In the test environment, developers might have insert/update/delete permissions on a table. In production, they might only have select, or no privileges at all. If you refresh that table (drop it, then import it back in) then the insert/update/delete permissions will be gone. That is why you need to extract the privileges on the objects, prior to dropping them.
• get a list of invalid object prior to the refresh
• generate the drop statements for the schema objects. The things I’d like to exclude in the drop are database links, scheduler jobs, and sometimes database/application specific objects.
• run the generated drop statements from above, and confirm there are no objects in the schema, except the ones you wanted preserved.
• purge the recycle bin

Now you are ready for the actual refresh:

In the source database datapump export the schema. You need to transfer the dump file to the target server, especially if they don’t share the backup location.
You can automate the datapump import parameter file generation, with the appropriate remap options (for schema, for tablespace) and then run datapump import, to import the data.
This could be a time consuming step, depending on the amount of data you are importing.

The following should be scripted to apply in the TARGET environment, after the refresh is complete:

• run the script with the extracted grants that was generated in the pre steps.
• if needed recreate database links and scheduler jobs, from the scripts you generated above. Depending on how you decided to run the refresh (drop the schema, or just drop the objects), this may or may not be needed.
• compile invalid objects
• gather stats
• copy all the logs into a central location
• delete the datapump dump file, as you have no need for it.

Here is a visual of the datapump refresh, feel free to print it and use it!

Once you have these steps scripted, you can have a calling script, that will run each individual step, and there you have the refresh automated!

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!