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:  

 

1. Export selected schemas and/or tables from source database (direct
export for best performance).

2. Truncate tables in selected schemas and/or tables in target database.

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

sbootsma@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx> 

416-415-5000 x4933

Other related posts: