Refresh oracle 10g database from Production to Test

I have an Oracle 10g production database on one server and a test database on another server. Both databases are identical in structure and patchset, the only difference is the production datafiles are on G drive on the live server and the test database datafiles are on D drive of the test server. Both databases are in noarchivelog mode and I have cold backups of the production database (backed up using RMAN) and also an OS copy. I would like a simple quick method of refreshing test with the live database as I will need to do this on a regular basis. I have read articles that say you can use RMAN to duplicate the database but none give any details. My oracle skills are unfortunately limited, so if anyone can provide an idiots guide I would very much appreciate it.
Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
You can certainly use RMAN for this. Sorry I don't have an "idiot's guide" as you asked for but I can tell you the steps.

1) Its easiest if you have a common path on both machines (such as D:\STAGE)
2) Do RMAN backup on PROD to the D:\STAGE
3) Copy all files created in D:\STAGE to the DEV box and place under D:\STAGE
4) On DEV set these params:
alter system set db_file_name_convert = 'C:\oracle\prod\', 'C:\oracle\dev\' scope=spfile;
alter system set log_file_name_convert = 'C:\orace\prod\', 'C:\oracle\dev\' scope=spfile;
5) Startup dev nomount
6) Run RMAN on the DEV box, but connecting to the PROD service via TNS remotely, with DEV as auxiliary (DEV should be in nomount mode)
    rman target / auxiliary sys/admin@dev

7) RMAN> duplicate target database to dev dorecover;

Something like that. At that point the database should be in mount state and recovered with the archive logs from the backup.

Thats about as much as I can give you in this forum. I don't recommend it for an inexperienced Oracle DBA, but you sound willing. :)

jonathanallenAuthor Commented:
Many thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.