Copying an Oracle production database to a test database

[Christophe MAILHE]

Hi,

I need to refresh our test database, which is on the same server than our production database.

In the past, I used to export from the production database and import into the test one during weekends. The import into the test system is usually taking more than 20 hours.

But as my colleagues need the test system updated as soon as possible, I need to find another alternative.

I know it is possible to copy the data files from the production database to the test database and then to restart the instance using a backup of the test database control file.

However, I have never done it.

Can someone give to me the exact procedure to do something like this?

Many thanks,

Christophe.

[Ian Dennison_1 · Accepted solution]

Christophe,...

I do this 5 times a week. This process assumes a different Database SID.

On Production System as oracle User,
Svrmgrl
>connect internal
>alter database backup controlfile to trace;
>quit

Go to the trace directory and find the most recent file, copy it to '/tmp/createcontrolfile.sql'

Edit the createcontrolfile.sql file, make the following changes,...
All comments before CREATE CONTROLFILE command to be removed.
Modify first line as follows
CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS NOARCHIVELOG
Remove all lines after the CHARACTER SET keyword.

Copy this file to the new server for the test database.

Restore the prod database to the test database server, excluding Redo Logs and Control Files.

Change permissions on datafiles to match test servers UID

As Database owner on test server
Svrmgrl
>connect internal;
>startup nomount
>@/tmp/createcontrolfile.sql
>alter database open;
>quit

if the createcontrolfile fails, correct the problem, and be sure to remove the control files it created before trying again!

Share and Enjoy! Ian

[Ian Dennison_1]

Oh, forgot to say, change all occurences of old Production SID to new Test SID in /tmp/createcontrolfile.sql

Ian

[Duncan Edmonstone]

Christophe,

If you are using RMAN you should also look at the RMAN 'duplicate database' options.

Cheers
Duncan

[twang]

If your production instance and test instance are in different servers, you may simply copy the datafiles from production server to the test machine.
If you need to change your test instance's SID, you may change it by recreating the control file (the method from Dennison).
Remember that you should disabled the archive destinations if your production instance has standby instance.

[Ian Lochray]

Christophe,
will you have a cold backup of your production database to copy to the test database? If your production system is 24x7 then these can be very difficult to obtain. If you are using a hot backup then you may have to do some database recovery in order to open the database. This may require access to archive log files created during the hot backup.

[Christophe MAILHE]

Thanks for your answers guys :)

The production and the test database are on the same server, so I will have to work on the backup control file.

I have understood that I need to cold backup of my database. But this won't be an issue as this database is not a 24/7.

Thanks for everything.

Cheers,

Christophe.

[twang]

I suggest the following steps:
on production machine, run the following via svrmgrl or sqlplus
alter database backup controlfile to trace;

edit trace file:

1) strip out all lines prior to "STARTUP NOMOUNT..."
2) change all references to SID to test instance.
3) change all reference to file names to appropriate names for test
instance. I'm hoping I can eliminate the datafiles I don't want to keep available here. Is that true?
4) make appropriate changes if there are any differences in number and size
of online redo logs.
5) Remove these lines from end of trace file
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
6) copy hotbackup files only (do not copy online redo logs) to test file
directories.
7) remove all online redo logs and control files for test instance.
8) make sure number of rollback segments in init.ora for test instance
matches number of rollback segments in prod.
9) from svrmgrl or sqlplus
@
10) issue following command:
recover using backup controlfile until cancel;
11) when prompted for archive redo log, copy it over from production to test
(renaming it appropriately).
12) apply as many arhive redo logs that you feel you want
13) CANCEL (this ends recovery) if you get error, then you need apply more
logs (see 10).
14) alter database open resetlogs;

This will recreate online redo logs and startup the database;

[Yogeeraj_1]

Hi,

Since, the production and the test database are on the same server, you should not only review the control file but also the init.ora. because You would not like to allocate the same amount of resources to both the production and test environment...

note that with RMAN this process will be relatively easier to perform.

If you need any further help, let us know.

hope this helps

regards
Yogeeraj

[Graham Cameron_1]

My advice would be NEVER put non-production databases on the same machine as where production databases are hosted.
See my entry at
http://forums.itrc.hp.com/cm/QuestionAnswer/0,,0xa85d8b82cc91d711abdc0090277a778c,00.html
to see why.

-- Graham