RE: Moving database from HP to IBM-AIX
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dubey.sandeep@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Oct 2005 11:30:21 -0400
This is usually a balancing act between "death by details" and parallelism
on re-creation of indexes on your largest objects. It is very good that you
know your target window, so you can execute a test with a minimum of effort
an see if that is good enough.
If the full export/full import runs in under 24 hours, why worker harder?
Unless you're running a MAID disk farm you probably don't even save any
electricity.
Let's assume for the moment though, that is not fast enough.
I'm not sure whether you have a full clone testbed of your warehouse from
which to get a testing source, but even if you don't, getting a dump of what
is for testing will be well worth a short additional outage so you can test.
Interim changes for a warehouse between the test runs and the real cut over
should not be material.
So, you get a full no-rows export for starters. Get the data exports by user
and/or by user/table leaving out the big ones. Generate create table, index,
trigger and constraint scripts for the big ones.
Unload the data from the big ones in a format that loader will swallow well.
Consider whether there is a predominant order of access supported by an
index for any of these big guys that might be worth the trouble to reorder,
and if there are any serious outliers and especially with partitioning that
you could load in parallel, you might want to also unload in pieces. You
said warehouse, so I'm guessing your rows no longer change in length, so if
you're not already aggressive with percent free you might want to get
aggressive with a small percent free. If older stuff is row length stable
and younger stuff is not, consider unloading pieces by age so you can load
the older stuff denser and then adjust percent free for the younger stuff.
You *may* benefit from ordering the unload if there is either a significant
pattern of access that will cause corelation between block selectivity and
row selectivity in future use of the database, or if it will allow you to
build an index using the already sorted option.
Run the no-rows import. Muck around and disable what you need to so you can
start the big boys loading in parallel streams (separate runs of sqlloader
to a reasonable load average on your CPUs rather than parallelism in Oracle,
so you don't pay the slave co-ordination overhead). As each big boy
finishes, and taking into account max IOPs on your temporary space and a
reasonable load average on your CPUs, start indexing it. Again, you probably
win by multiple sqlplus sessions over parallelism.
Good luck, and don't do more work than you have to.
------------------------------------
Rightsizing, Inc.
Mark W. Farnham
President
mwf@xxxxxxxx
36 West Street
Lebanon, NH 03766-1239
tel: (603) 448-1803
------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sandeep Dubey
Sent: Friday, October 28, 2005 10:41 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Moving database from HP to IBM-AIX
Hi,
We need to move 2 TB data warehouse on Oracle 9.2 HP to Oracle 9.2
database on AIX. These operating systems has opposite endian. Down
time should be less than 24 hrs.
Please let me know what will be faster and optimum way to do this. I
am thinking of sqlloader as the fastest way to go. Are there any
better alternative?
If you have undergone such exercise please share your lesson learnt.
Regards
Sandeep
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
