Re: Data Migration options - oracle to oracle

From: rjamya <rjamya_at_gmail.com>
Date: Fri, 6 Sep 2013 08:30:05 -0400
Message-ID: <CAGurbTOq6fm3c-J1Q8KDevnwXEsubb_-CrJqs3kfrv67yFH1vA_at_mail.gmail.com>



Import using impdp with triggers and constraints in place is a disaster waiting to happen, especially since you CANNOT control the order in which impdp will import table data. Unless you import one table at a time (so it will be non-starter due to time limitations). DBlink is an option, but it might be slow and possibly could cause contention for active users.

It might be easier to create shadow tables, load them using impdp in one shot, do your sanity checks and then use partition exchange to quickly load them into existing schema. Since you already say you have partitions, so licensing is not an issue here i suppose.

since you already have tables etc created, you should think about,

1. creating dummy tables (non partitioned) on target
2. export data for your x customers using expdp from source
3. import these into your shadow tables, enable constraints, re-create
indexes etc etc
4. run your scripts against these shadow tables to validate all data came in properly
5. if you need data transformation, you should be able to run your scripts (insert into select from) within same db, so it will be much faster, you can control the sequence of load to take care of constraints etc. 6. when no transformation is needed, use partition exchange with main tables 7. Most of this can be scripted

GoldenGate is a good option but it is many $ and if you are looking for a one time deal, it might not be cost effective.

Good luck

Raj

On Thu, Sep 5, 2013 at 6:53 AM, Karth Panchan <keyantech_at_gmail.com> wrote:

> Folks
>
> Sorry for blank mail earlier. Hit send by accident.
>
> 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
>
> 2) Need to do migration with index/constraints/triggers enabled on Target
> OLTP instance during other users using this instance at production mode.
>
> Recommended GoldenGate and Streams at my place. There is a lack of
> knowledge and no one want take risk.
>
> Written db package solution uses dblink for data migration using metadata.
>
> I have been told running SQL Plus using dblink is much faster then DB
> package. Waiting for benchmark.
>
> Anyone migrated data to live OLTP instance with index/triggers/constraints
> enabled in Oracle tools
>
> Appreciate your help
>
> Thanks
> Karth
>
>
>
> On Sep 5, 2013, at 6:29 AM, Karth Panchan <keyantech_at_gmail.com> wrote:
>
> >
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 06 2013 - 14:30:05 CEST

Original text of this message