We help IT Professionals succeed at work.

Refresh test db with prod data

sharscho
sharscho asked
on
1,128 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.
Comment
Watch Question

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

Commented:
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?

Commented:
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.

Author

Commented:
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.

Author

Commented:
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!!

Commented:
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. :)

Author

Commented:
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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Commented:
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.

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.