RE: Data Migration options - oracle to oracle
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Sep 2013 10:07:43 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Norman Dunbar
Sent: Friday, September 06, 2013 8:52 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
