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

[Christophe MAILHE]

Hi Graham,

I agree with you. A production and a test database should never be on the same machine.

However, in the real world, dur to the price of a HP9000 box and the price of an Oracle license, we often don't have any other choices.

Christophe.

[Christophe MAILHE]

Dear all,

Thank you very much for your help.

Just a quick summary of what I did yesterday in order to copy this prodution database to the test database on the same system.

Please note the test database already exists !

------------ 1 ------------
Get a backup cpntrol file for the TEST database
Get the list of datafiles and controlefiles to delete from the system

export ORACLE_SID=TEST
sqlplus /NOLOG
connect internal;
alter database backup control file to trace;
SET HEADING OFF;
SET LINESIZE 255;
SET TRIMSPOOL ON;
SET PAGESIZE 0;
SET VERIFY OFF;
SET FEED OFF;
SET ECHO OFF;
SPOOL /tmp/remove_TEST_datafiles.sh;
select 'rm ' || NAME FROM v$datafile;
select 'rm ' || NAME FROM v$controlfile;
SPOOL OFF
exit;

------------ 2 ------------
Backup controle file to trace for PROD
Get list of datafiles to copy

export ORACLE_SID=PROD
sqlplus /NOLOG
connect internal;
alter database backup control file to trace;
SET HEADING OFF;
SET LINESIZE 255;
SET TRIMSPOOL ON;
SET PAGESIZE 0;
SET VERIFY OFF;
SET FEED OFF;
SET ECHO OFF;
SPOOL /tmp/list_PROD_datafiles.lst;
select NAME FROM v$datafile;
SPOOL OFF;
exit;

------------ 3 ------------
Edit the backup control file for TEST database
Remove all comments before CREATE CRONTROLFILE
Modify the CREATE CONTROLEFILE line :

CREATE CONTROLFILE REUSE set DATABASE "TEST" RESETLOGS NOARCHIVELOG

Note the REUSE set DATABASE instead of REUSE DATABASE and note the RESETLOGS instead of NORESETLOGS !

Logfiles are still the same. No change needed

Datafiles : Update accordingly.

Check the character set is the same than in the PROD database (use the backup control file of the PROD database to check this).

Remove all lines after the CHARATER SET line.

Save the edited file as /tmp/createTESTcontrolfile.sql

------------ 4 ------------

export ORACLE_SID=TEST
sqlplus /NOLOG
connect internal;
shutdown normal;
exit;

sh /tmp/remove_TEST_datafiles.sh

export ORACLE_SID=PROD
sqlplus /NOLOG
connect internal;
shutdown normal;
exit;

------------ 5 ------------
copy the PROD datafiles to TEST datafiles
(list of datafiles to copy in /tmp/list_PROD_datafiles.lst)

------------ 6 ------------
Restart PROD database

------------ 7 ------------
export ORACLE_SID=TEST
sqlplus /NOLOG
connect internal;
startup NOMOUNT;
@ as /tmp/createTESTcontrolfile.sql;
ALTER DATABASE OPEN RESETLOGS;

------------ E ------------

If someone has any comments, please feel free :)

Christophe.

[Massimo Bianchi]

Just a minor thing.

Usually production server are in archive log mode, and test no.

So remember to switch off the archive log mode !

OR

if also your test environtment is in archivelog mode, remember to cleae the directory, otherwise there may be some old arc from previous instances.

HTH,
Massimo

[Massimo Bianchi]

Hi,
another little thing.

You wrote:

"Logfiles are still the same. No change needed"

What do you mean ?

origlog/mirrorlog cannot be the same in prod and test, path must be different... am i missing something ?


BTW: O.K., you have no money to afford a new server, but at least, you can use different oracle owner. This can help you in preventing the access to the wrong database.

HTH,
Massimo

[Yogeeraj_1]

hi,

init.ora parameters that you may wish to change:

log_archive_dest_1 =
background_dump_dest =
core_dump_dest =
user_dump_dest =

Also, as Massimo said above, it is preferrable to create another schema similar to your production schema for test.

Hint:
1. import/export with rows=no to generate create objects scripts
(create tables with nologging)
2. Using Direct load insert to move data around

We have our test and production schema on database server.


regards
Yogeeraj

[Yogeeraj_1]

hi,

init.ora parameters that you may wish to change:

log_archive_dest_1 =
background_dump_dest =
core_dump_dest =
user_dump_dest =

Also, as Massimo said above, it is preferrable to create another schema similar to your production schema for test.

Hint:
1. import/export with rows=no to generate create objects scripts
(create tables with nologging)
2. Using Direct load insert to move data around

We have our test and production schema on database server.

regards
Yogeeraj

[Hank Eggers]

We have setup snapshot filesystems via onlineJFS to about 15% of the size of the production filesystems. So:

/u01 ---> /snapshot/u01
/u02 ---> /snapshot/u02
etc.

When we want to do a clone to a d.b on the same machine as PROD we do the following:

1) bring down PROD
2) take filesystem snapshots
3) bring up PROD
4) Start copy of datafiles from snapshots to TEST database.
5) When the copies are done, umount your snapshot files.

Using this method PROD is only down for about 5 minutes.

This method assumes you are using a vxfs filesystem and have enough disk space for the snapshot files.