Solved

Refresh test db with prod data

Posted on 2010-09-08
11
1,088 Views
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.
0
Comment
Question by:sharscho
  • 6
  • 5
11 Comments
 
LVL 8

Expert Comment

by:POracle
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.
0
 

Author Comment

by:sharscho
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?
0
 
LVL 8

Expert Comment

by:POracle
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.
0
 

Author Comment

by:sharscho
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'
PARMS="BLKSIZE=262144";
PARMS="SBT_LIBRARY=/opt/hds/Base/libobk.so"
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.
0
 

Author Comment

by:sharscho
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!!
0
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.

 
LVL 8

Expert Comment

by:POracle
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.

case-1:
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.
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1007814

case:-2
If you want to keep test server as it is then go for tablespace transport.
for more information look here
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/ontbltrn.htm#CACJAEFH

case:-3
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. :)
0
 

Author Comment

by:sharscho
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?
0
 
LVL 8

Accepted Solution

by:
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

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1281

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.



0
 

Author Comment

by:sharscho
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.
0
 

Author Closing Comment

by:sharscho
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.
0
 
LVL 8

Expert Comment

by:POracle
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.
0

Featured Post

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

9 Experts available now in Live!

Get 1:1 Help Now