RE: Migrating 3TB size database from HP to AIX, also 9i to 10g.

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: <sjaffarhussain@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Sep 2007 08:01:43 -0400

I migrated an Oracle Apps database 2 weeks ago from Dell to PowerPC IBM
hardware which had a different endianness so we had to use export/import as
Oracle Apps is not self contained so we couldn't use transportable
tablespaces. We used datapump which took 2 hours for the expdp and 12 hours
for impdp. Out of the 12 hours, less than 2 hours was actually used for
copying rows. The rest of the time was used for building all other objects.
We used the parallel setting in datapump set at 64 and while testing noticed
that many operations are not run in parallel. Each table is loaded during a
single thread so 99% of our tables were loaded in under 30 minutes and 2
threads remained running for our large 200+ million row tables which
contained lobs. Large heap non-lob tables loaded quickly.

 

Indexes are built one at time with the parallel setting number of slaves.
This may be to your advantage as I suspect it performs better with fewer
large indexes rather than many smaller ones. Package headers are loaded in
parallel which can cause deadlocks at which point impdp dies but can be
restarted. Package bodies are loaded serially which was our major source of
pain as it took many hours to load and later compile 40,000 large package
headers and bodies.

 

Make sure to set your streams size, as the default is 16m which will blow
out on your first large table and it will die, although it an be restarted
it will continue from the beginning of the data load and it will load
duplicate data. 

 

I don't remember if table stats are run in parallel or serially but I don't
recommend letting impdp run them as it may not use the method you typically
use and it is better run post migration. 

 

Advanced queuing can be a real pain with impdp, if you use this feature
(headache) make sure to test it very well.

 

In short if your application is self contained which is likely for a data
warehouse, I'd prefer to use the transportable tablespace as impdp has many
pitfalls.

 

Ken

 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Syed Jaffar Hussain
Sent: Wednesday, September 19, 2007 6:44 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Migrating 3TB size database from HP to AIX, also 9i to 10g.

 

Hello list,

 

I have a uphill taks of migrating our datawarehouse Oracle 9i database, 3TB
size, from HP Superdom to AIX, Oracle 10g.

Since the migration is across platofrms, exp/imp is the obvious option. But,
3TB size databse, exp/imp may take just too much time.

 

I have thought the following:

 

Upgrading 9i database on AIX to 10g (in place upgrade)

Then, using the method of database cross platform conversion from HP to AIX.

 

The problem with this method is that the temporary space required to hold
the converted data files, it willd be difficult to have 3tb temporary sapce.

 

Do you see any another solution for this task?

 

Regards,

 

 

Jaffar

-- 
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA

http://jaffardba.blogspot.com/
http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:126
----------------------------------------------------------------------------
------
"Winners don't do different things. They do things differently."

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2007