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_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

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