Copying an Oracle production database to a test database

[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.