Re: Data Migration options - oracle to oracle

On 05/09/13 11:53, Karth Panchan wrote:

> Need your suggestion to migrate data from oracle to oracle db instance for OLTP application.
>
> 1) This migration need 50% table data transformation from source db to target db

You can use datapump to export the data, indexes, constraints etc and exclude any already existing procedures, packages and functions in the target database - so yo avoid replacing ones that might be locked for use.

On importing into the target database, you can specify a transformation procedure that will do the table data transformation that you require.

If you have an "air gap" between source and target, you'll need to create a dump file and move it between the two servers. On the other name, if you don;t have an air gap, you can create a database link in the target and simply do the import from there, without needing a dump file.

> 2) Need to do migration with index/constraints/triggers enabled on Target OLTP instance during other users using this instance at production mode.

When you import the data with impdp, use the content and table_exists_action parameters to specify that you only want the data (content=data_only) and on finding an existing table, append the data to it (tables_exists_action=append).

> Recommended GoldenGate and Streams at my place. There is a lack of knowledge and no one want take risk.

Likewise, I know nothing about these, yet, but I've (coincidentally) put some stuff on Datapump on my blog at
http://qdosmsq.dunbar-it.co.uk/blog. I haven't got the transformation stuff up yet, so drop me an email if you need further info on this. (Via the list I suppose, in case anyone else is interested!)

HTH Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l