RE: Cloning Data Only

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Wed, 6 Jul 2005 09:19:58 -0600
Message-ID: <OF767746E8.1DB04ED2-ON06257036.0053DDEC@usgs.gov>

Not sure of your environment (number of available servers, etc.), but how about if you periodically clone to a 'test_data' database instance, and your dev and test databases connect to that data via database links and synonyms?

That way your data is duplicated for testing purposes, and the code and other objects in the other instances wouldn't be overwritten or otherwise lost. There would probably be a period when nobody could connect to the test_data, but it seems like you already have that problem anyway.

Just an idea...

Bill Ferguson
U.S. Geological Survey - Minerals Information Team PO Box 25046, MS-750
Denver, Colorado 80225
Voice (303)236-8747 ext. 321 Fax (303)236-4208

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Bootsma
Sent: Wednesday, July 06, 2005 8:58 AM
To: oracle-l_at_freelists.org
Subject: Cloning Data Only

>From time to time we clone our PROD database to our TEST or DEV databases.
We currently do this using the RMAN duplicate command. Problem is, when we clone in this fashion, any programming, table, or other object changes that are in DEV or TEST, but not yet in PROD are lost. We use Harvest as our Change Manager tool and we try to minimize this problem by re-creating objects still in the pipe between DEV and PROD, but there always remain database objects that slip through the cracks.

As a result, our Systems Analysts and Developers would like us to refresh data only, not programs. This means I cannot use the RMAN duplicate command, because this will always copy the SYSTEM tablespace and all PL/SQL code and other objects stored in the SYSTEM tablespace.

Does anybody have suggestions or elegant solutions to achieve this? One solution is:

Export selected schemas and/or tables from source database (direct export for best performance).
Truncate tables in selected schemas and/or tables in target database.
Import table rows back into the target database from the dump file created earlier.

This mechanism will preserve developer coding changes, and structure changes such as adding a column, removing a column, changing the width or type of a column, and changing indexes; but if an index has been dropped in the target database, it would likely be recreated during the import.

However, there are some side-effects. For example, if a table has had a column removed, or if the column is narrower than before, import for that table may fail or not produce desired results. Also, if any indexes or other table dependent objects have been deleted on the target, the import will likely recreate them. Can anybody think of other side-effects associated with this approach?

We are running Oracle 9.2.0.6 on AIX 5.

Thanks!

Sam Bootsma

George Brown College

<mailto:sbootsma_at_gbrownc.on.ca> sbootsma_at_gbrownc.on.ca

416-415-5000 x4933

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 06 2005 - 10:33:45 CDT