Refresh test db with prod data

Posted on 2010-09-08
Medium Priority
Last Modified: 2013-12-19
Hi experts,

I need advice on refreshing a test db with prod data. We do a rman backup of both databases. The db version is 10G on linux redhat. Is it good to perform a rman restore of the prod on the test server for the test db? Will I encounter problems if yes, what kind of problems? And what is the best way to perform this rman restore? Do I need to remove schemas or data from the test db prior to perform such restore?
I thank you in advance for your advice.
Question by:sharscho
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5

Expert Comment

ID: 33634013
To setup Test db you can do restore of prod. database from its backup.
Restore all datafile, controlfile and logfile.

You can use RMAN duplicate database command also.

But, if your prod. database is bigger in size than you may don't want to allocate large space for test database. In this case you can write some query or procedure to get latest set of data from production database and insert it into test database. You can write some customize code for this. this code depend on your database design.

Author Comment

ID: 33634071
OK I want to do a rman restore from the prod db to the test db with a different name. There is enough space on the test server to do this and so I want to do the simple solution. The procedure and query option seems complicated. Can I get a restore rman command from you to compare with I learned from the books?

Expert Comment

ID: 33634128
Try your command, Its a test server so you can do it. and if you didn't succeed than you got valuable experience. :)

This is test server but later on in your life you may require to do in some more critical environment. So, do it and if it gets fail post your script here, We are here to help you.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 33634759
First on the test server I will shutdown the db and do a startup mount.
then issue the command:
rman target sys/password to login
@scritp_proddb (which includes the commvault script below)

The restore will occure from a different server. The rman backups are being made with commvault so I have this for the backup files of commvault:
run {
allocate channel ch1 type 'sbt_tape'
set newname for datafile '/data1/oracle/oradata/RSPALM/alm01.dbf' to '/opt/oracle/oradata/RSPALMT/alm01.dbf';
set newname for datafile '/data1/oracle/oradata/RSPSUMM/alm02.dbf' to '/opt/oracle/oradata/RSPALMT/alm02.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/alm03.dbf' to '/opt/oracle/oradata/RSPALMT/alm03.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/alm04.dbf' to '/opt/oracle/oradata/RSPALMT/alm04.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/sysaux01.dbf' to '/opt/oracle/oradata/RSPALMT/sysaux01.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/system01.dbf' to '/opt/oracle/oradata/RSPALMT/system01.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/undotbs01.dbf' to '/opt/oracle/oradata/RSPALMT/undotbs01.dbf';
set newname for datafile '/data1/oracle/oradata/RSPALM/users01.dbf' to '/opt/oracle/oradata/RSPALMT/users01.dbf';
 restore database ;
 recover database ;
 switch datafile all;
the path of where the backup files are is stated above. I want to put this in a file and do the following on the test server:
First I thought there is a tablespace or a datafile recovery needed. But then what can I do with the controlfiles of the test db? The prod db has more datafiles for the ALM tablespace then the test db does. The test db has only one datafile for it. the rest are the same. So my question is how to do this because I have never done it before like this. Hope you can help out with your expertise.

Author Comment

ID: 33635201
Can I get some advice please? I can not expirement on this even if it is a test server. It has to recover in one time so that the developers which are being paid for the new release can continue. They are only here for a day. Some help will be appreciated. Thank you!!

Expert Comment

ID: 33635261
Correction in your script:-
switch datafile all; comes before recover database;
means in you script, recover database should be last statement.

Refer below mentioned 2 cases.

I recommend to go for a fresh restore. do you need your current test database? No, then remove it and create test server by restoration of latest production backup.

If you want to keep test server as it is then go for tablespace transport.
for more information look here

Use rman and create new duplicate database on test server.

And ya, don't forget to take backup of your production database before doing any thing. :)

Author Comment

ID: 33636003
Poracle, I thank you very much for your response. I have been looking at the possibilities and I want to consider case:-1. But is seems I can not create the duplicate db with a new name and That is just what I want, a copy of the prod db but with a different name and a different pfile because the prod pfile is configured for dataguard. Is that so difficult? prior to oracle 10 (oracle 7 and 8) you can refresh a db with prod data under a different name without problems. Now with rman it got more difficult instead of easier. Do you think an export/import from the prod to the test db is easier?

Accepted Solution

POracle earned 1500 total points
ID: 33636150
Do you think an export/import from the prod to the test db is easier?

This depend on size of your database. Go for Transport tablespace with export/import
You can find out all detail for tablespace transport from here


This is simple and if database size is small then go for this.

because the prod pfile is configured for dataguard
You can modify/reset/delete parameters related to datagurad.


Author Comment

ID: 33689468
The prod db has to remain in use during the export. I decide to go for a owner ecport to export only the schema that the application uses. But thanks for your advices.

Author Closing Comment

ID: 33689486
What I wanted to accomplish was not understood. I wanted to restore a db copy under another db name. And after that an advice that will make my prod db readonly for a period of time what is not desireable.

Expert Comment

ID: 33689790
Transportable Tablespace with Backup datafile will not require to make your production database read only.
It is fast.
It uses export/import, but only for metadata of tablespace.

I post two link above one to understand what is transport tablespace is and another for, how to use backup file during transporting tablespace. I suggest you to read this. it will help you in achieving that you want.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question