RE: Data Migration options - oracle to oracle
- From: <rajendra.pande@xxxxxxx>
- To: <keyantech@xxxxxxxxx>, <oracle@xxxxxxxxxxxxxxx>
- Date: Fri, 6 Sep 2013 07:49:51 -0400
If I understand correctly the issue is data transformation and the DB
links.
Assuming that to be the case I would look into using online redef to put
data into temp tables on the source database and then use exp/imp to
move data on the day of
I suspect additionally with proper partitioning scheme you should be
able to move the data in quickly
With online redef you can do the transformation on the source without
incurring the overhead of db links
HTH
Regards
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Karth Panchan
Sent: Friday, September 06, 2013 12:14 AM
To: oracle@xxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx>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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Please visit our website at
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
for important disclosures and information about our e-mail
policies. For your protection, please do not transmit orders
or instructions by e-mail or include account numbers, Social
Security numbers, credit card numbers, passwords, or other
personal information.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
