Database Refresh Part 3 – RMAN

This is the final part of the Database Refresh series, and you guessed it, it is about database refresh using RMAN duplicate methodology.

Just as we did in last week’s post, we are going to break down the refresh process, into things that change and things that do not change. Once we identify what changes, it is easier to start scripting the steps.

When you are running an RMAN duplicate command, you are creating an exact copy of your SOURCE database.

What does this mean?

This means that the original database you had, will get wiped out, and replaced with the duplicate one. If this sounds scary, it is scary.

If you are refreshing the test environment, you want to make sure you communicate to everyone who has access to the database, before the refresh, to save their work. If a user is working on a project in the test database, and his work is not saved anywhere else, guess what happens with the refresh? All the work is gone! The last thing you want, is to restore the test database from backup, to recover unsaved work of others.

Following is a bit of help for the refresh process with RMAN.

Things that will change when you refresh a database with RMAN:

• the whole database changes, it actually gets replaced.
•  users, and their passwords
• grants and roles
• database links (production database links point to production, test database links point to test)
• scheduler jobs (these jobs might be different between environments)
• directory objects
• any extra schema/object or data that is not in production will be wiped out.
• any application specific configuration that is maintained in the database.

Things that will not change when you refresh a database with RMAN:

• there is nothing that stays the same. Really everything gets replaced.

Now you have a better understanding for things that will change, therefore it is easier to come up with the automation of the process.

When refreshing a database with RMAN, the most important steps are the PRE steps, which will save lots of the settings of your test database.

The following should be scripted to be extracted in the AUXILIARY environment (the database where you are going to restore), before the refresh is started:

• all database links; this step is important because the database links in production usually point to production, and the database links in test point to test. You do not want database links in test to point to production, as production data might be changed accidentally.
• users’ DDL; users that do not own any objects will be recreated after the refresh. Also there might be users that don’t even exist in production, and they exist in test, these users will need to be recreated after the refresh.
• alter statement to alter user passwords for the users that own objects; The passwords for these users will be altered and reset to the original value. This step is especially important for the application schemas.
• roles; there might be roles that exist in test, but not in production. these roles must be saved. Same with roles that have passwords.
• user grants, object grants, role grants
• directory objects; directories might point to different location in the production database.
• anything else application specific that must be preserved, must be saved outside the database
• any customization or extra schemas/objects that are present in the test environment and not present in the production environment, must be saved outside the database.

Once all of the above are saved or extracted, you can proceed with the RMAN duplicate. You might need to transfer the RMAN backup files to be available for the test database to be restored. You run the RMAN duplicate command to complete the refresh process.

The following should be scripted to be run in the refreshed environment, after the RMAN duplicate completes:

• recreate all the database links
• drop and recreate the users that do not own objects.
• alter user passwords for users that do own objects.
• recreate roles if applicable
• apply the grants for users and roles, to reestablish user/role/object grants pre refresh
• recreate directory objects
• redeploy any customization or extra schemas/objects
• redeploy anything that is application specific.

And that’s it! Happy scripting!

 

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!