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
  • 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.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

601 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