Refresh test db with prod data

Posted on 2010-09-08
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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 42
help on oracle query 5 43
run sql script from putty 4 32
Oracle Insert not working 10 22
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 …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now