RE: Data Migration options - oracle to oracle

First datapump tables that migrate easy over, ie no foreign keys or transformation.

Then

I would use option #1 ie SQL Scripts INSERT as SELECT using DBLink and PL/SQL blocks for data transformation for the remaining tables.

Maybe you can write some self-generating sql code to code most of the work for you then tweak each insert/select stmt for the transformations.

Example write some code that will generate the insert code as below:

Select 'insert into '||table_name||' select * from '||table_name||'_at_Source_DB;' from user_tables;

Now the code above is very simple but it's just to give an example of generating code to help write your procedure. To make it work you will want to join it with user_tab_columns and list the column names.

There is also the copy command. Copy is a nice way to move data from one database to another without a db_link. But be careful with number fields if you have copy create the target table as it will strip out decimal points. Does not work with lobs but will move long data over. Can do simple transformations too.

Example if on target database

set copycommit 500 arraysize 500 long 9999999

Copy from nabil/pass_at_source_db insert emp using select * from emp;

help copy

 COPY

 Copies data from a query to a table in the same or another  database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

 COPY {FROM database | TO database | FROM database TO database}

            {APPEND|CREATE|INSERT|REPLACE} destination_table
            [(column, column, column, ...)] USING query

 where database has the following syntax:
     username[/password]_at_connect_identifier

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karth Panchan
Sent: Thursday, September 05, 2013 11:14 PM To: oracle_at_dunbar-it.co.uk
Cc: oracle-l_at_freelists.org
Subject: Re: Data Migration options - oracle to oracle

Norm/Raj
Thanks very much for response and details.

Here is little more background information.

Old application data reside in around 600 tables on each schema for our 1000+ customers.(1000+ schema)
New application need only data get migrated from 450 tables into Single Schema on target.
Data on tables range from 1 record to 1 Million, not more than that. We plan to migrate 100 customers per month, but we get only 24hours to migrate those 100 customers.

Both application running database on Oracle 11g. New(Target) running on RAC 2 Node in Linux 64 bit.

50% of table data to target database need transformation. Like 1 source table insert into multiple target tables (or) Multiple source tables to 1 target table.

Most of the target tables have Triggers enabled, that need to capture migration data for audit.

Given that we need to migrate every month after we go-live, there will be existing customers who will be using this application tables. So, we will not able to disable constraints/indexes/triggers on Target database.

If we had all tables partitioned by customer, this would have been easier to load staging table and exchange partition. Unfortunately, I was told that option is maintenance nightmare.

Currently, we have 2 solutions both using DBLink. 1) SQL Scripts INSERT as SELECT using DBLink and PL/SQL blocks for data transformation
2) DB package INSERT as SELECT using DBLink using Metadata table where SQL was defined.

    Package execute dynamically.

Both options are not much scalable based on expectation from production team.

Using Datapump, I need to find out how much transformation we can achieve.

Bottomline, we need to migrate only data from source to target db without impacting production system with performance to migrate 100 customers data(450 tables for each customer) in 24 hours.

Thanks very much for your points. I will look into that.

Appreciate any further pointers.

Karth

On Thu, Sep 5, 2013 at 7:12 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk>wrote:

> Hi Karth,
>
> 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
>
>
>

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




-- 


------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
------------------------------------------------------------------

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