RE: Data Migration options - oracle to oracle

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 6 Sep 2013 10:07:43 -0400
Message-ID: <03a701ceab0a$7567e990$6037bcb0$_at_rsiz.com>

The severity of the problem here is a little overstated, to 1 table at a time. It is, however, a very real problem.

Very likely a few sets of tables can be defined so that all "parents" can be loaded before children look for new references.

Some possibility you'll want stats updates on the parent loads to finish before the child imports, so having them in different import files for ordering those bits of the job may be useful so that you have some control over the ordering.

Depending on your disk farm and a few other things, designed multiple set exports can improve throughput as well, with multiple impdp jobs running in parallel as opposed to a single impdb running at a parallel degree. The statement "Your Mileage May Vary" is germane here.

Folks tend to figure this out one off for particular migrations. Would it be useful to have a tool to figure out and optimize table set migration?

mwf

PS: Without knowing the details of access on the target we cannot be sure, but partitioning at least by customer seems like it should be at least investigated. The idea that you've smooshed multiple schemas into one screams the *possibility* that a lot of your target system queries could use partition pruning by customer.

Whether this fits the "scaling to infinity" model with partition exchange is another consideration worth at least taking a look at (see Tim Gorman's stuff on this for a great explanation.) I'd presume the two level partitioning then would be customer and date (or some date proxy). Even if the partition exchange method cannot be used on the routine basis, the access pattern possibility of pruning by customer and date seems promising and information life cycle planning for older partitions could be effective and dovetail with Oracle's long term heat mapping.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
Sent: Friday, September 06, 2013 8:52 AM To: oracle-l_at_freelists.org
Subject: Re: Data Migration options - oracle to oracle

On 06/09/13 13:30, rjamya wrote:
> 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.

Good point! Exp and expdp export tables in alphabetical order, so if there are ref int constraints in place when importing, and the child table is earlier in the sort order than the parent, and there are no parent rows for the child rows coming in, there will be a constraint violation.

Even if the constraints are DEFERRED, the checking takes place on the next commit, so if you have commit=y assuming imp, or where impdp commits at the end of each table, you will still get the constraint violation.

:-(

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

Received on Fri Sep 06 2013 - 16:07:43 CEST