FAQ
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."

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • John Hallas at Sep 19, 2007 at 11:46 am
    Once you get to 10g on HP you can use datapump which is much faster

    Can a lot of the data be migrated in advance, i.e historical data,
    leaving the current data to be moved during the planned outage.





    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Syed Jaffar Hussain
    Sent: 19 September 2007 11:44
    To: oracle-l_at_freelists.org
    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."

    BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
    Registered in England with company number 2777575.
    http://www.bjss.co.uk

    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Sep 19, 2007 at 3:55 pm
    If the 3T of data is spread across numerous objects I would consider
    going back to the first plan but running a series of concurrent exports
    into named pipes. then I could grad these named pipes via a remote copy
    (rcp) command into a named pipe on the new server from which I would
    start a background imp task.


    You are now concurrently exporting and importing and you can run a set
    of these job tasks in parallel to reduce total necessary clock time.


    Mark D Powell --
    Phone (313) 592-5148

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of John Hallas

    Sent: Wednesday, September 19, 2007 7:47 AM
    To: sjaffarhussain_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Migrating 3TB size database from HP to AIX, also 9i

    to 10g.



    Once you get to 10g on HP you can use datapump which is much
    faster

    Can a lot of the data be migrated in advance, i.e historical
    data, leaving the current data to be moved during the planned outage.







    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Syed Jaffar Hussain

    Sent: 19 September 2007 11:44
    To: oracle-l_at_freelists.org
    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."



    BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1
    2TW.

    Registered in England with company number 2777575.
    http://www.bjss.co.uk

    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Sep 19, 2007 at 6:41 pm
    Just a word of caution - I haven't used datapump a lot, but the few
    times I have used it I was very disappointed with its performance - it
    took MUCH longer than good old exp/imp. I don't have time to review my
    notes right now and give you the specifics of exactly how I ran it with
    my timings and all the bugs and tuning notes I found, but just be sure
    to do some research first and beware that it's not necessarily true that
    datapump speed > exp/imp speed so test it for your specific situation -
    as usual, it depends.

    Regards,
    Brandon

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of John Hallas

    Once you get to 10g on HP you can use datapump which is much faster

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Khanna, Prem at Sep 20, 2007 at 8:28 am
    I agree with Brandon . expdp/impdp is NOT ALWAYS faster than exp/imp . I
    have seen it
    many times to be slower than the conventional exp/imp. Better test it .


    ~jp~

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Allen, Brandon
    Sent: Thursday, September 20, 2007 12:12 AM
    To: john.hallas_at_bjss.co.uk; sjaffarhussain_at_gmail.com;
    oracle-l_at_freelists.org
    Subject: RE: Migrating 3TB size database from HP to AIX, also 9i to 10g.

    Just a word of caution - I haven't used datapump a lot, but the
    few times I have used it I was very disappointed with its performance -
    it took MUCH longer than good old exp/imp. I don't have time to review
    my notes right now and give you the specifics of exactly how I ran it
    with my timings and all the bugs and tuning notes I found, but just be
    sure to do some research first and beware that it's not necessarily true
    that datapump speed > exp/imp speed so test it for your specific
    situation - as usual, it depends.


    Regards,
    Brandon
  • LS Cheng at Sep 20, 2007 at 9:38 am
    I have not seen DP performance problems...

    I have used it to migrate a E-Business Suite database, around 500GB. With
    exp it took days, with expdp it took hours. Whem import imp never ends (well
    cut off after 4 days) and impdp took around 24 hours.

    Thanks

    --
    LSC
    On 9/19/07, Allen, Brandon wrote:

    Just a word of caution - I haven't used datapump a lot, but the few times
    I have used it I was very disappointed with its performance - it took MUCH
    longer than good old exp/imp. I don't have time to review my notes right
    now and give you the specifics of exactly how I ran it with my timings and
    all the bugs and tuning notes I found, but just be sure to do some research
    first and beware that it's not necessarily true that datapump speed >
    exp/imp speed so test it for your specific situation - as usual, it depends.
    Regards,
    Brandon

    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *John Hallas

    Once you get to 10g on HP you can use datapump which is much faster

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do not
    consent to Internet email for messages of this kind. Opinions, conclusions
    and other information in this message that do not relate to the official
    business of this company shall be understood as neither given nor endorsed
    by it.
    --
    http://www.freelists.org/webpage/oracle-l
  • John Hallas at Sep 20, 2007 at 9:58 am
    Unquestionable one needs to test any mechanism that will be used for a
    large scale migration, especially if under tight time constraints.

    I do not doubt that expdp/impdb may perform worse than exp/imp under
    certain circumstances but I think it should be the first port of call if
    considering that method of data migration.



    On a large project a couple of years ago we used Shareplex to
    trickle-feed changes in to the new system. One outage to export the
    original data (8i, Tru64) using multiple parallel exports. Restart
    source database with Shareplex capturing changes.

    Build the new empty database (10g, Sun) and import at our leisure (circa
    36 hours).

    Multiple schema compares and then apply the captured Shareplex changes.



    On the night it was "just" a matter of stopping the source database,
    letting all the captured changes trickle-feed into the target database
    and then making all the DNS/ONS changes to allow connectivity to the new
    database.



    It took about 3 months of effort and a couple of attempts but the above
    solution did work.



    John



    From: LS Cheng
    Sent: 20 September 2007 10:39
    To: Brandon.Allen_at_oneneck.com
    Cc: John Hallas; sjaffarhussain_at_gmail.com; oracle-l_at_freelists.org
    Subject: Re: Migrating 3TB size database from HP to AIX, also 9i to 10g.



    I have not seen DP performance problems...

    I have used it to migrate a E-Business Suite database, around 500GB.
    With exp it took days, with expdp it took hours. Whem import imp never
    ends (well cut off after 4 days) and impdp took around 24 hours.

    Thanks

    --
    LSC

    On 9/19/07, Allen, Brandon wrote:

    Just a word of caution - I haven't used datapump a lot, but the few
    times I have used it I was very disappointed with its performance - it
    took MUCH longer than good old exp/imp. I don't have time to review my
    notes right now and give you the specifics of exactly how I ran it with
    my timings and all the bugs and tuning notes I found, but just be sure
    to do some research first and beware that it's not necessarily true that
    datapump speed > exp/imp speed so test it for your specific situation -
    as usual, it depends.

    Regards,

    Brandon



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of John Hallas

    Once you get to 10g on HP you can use datapump which is much faster

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.



    BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
    Registered in England with company number 2777575.
    http://www.bjss.co.uk

    --
    http://www.freelists.org/webpage/oracle-l
  • David Taft at Sep 19, 2007 at 3:16 pm
    Jaffar,

    Read the following "Ask Tom" forum at the URL below
    from: "migration across platform", January 19, 2004
    through: "migration across platform", February 04, 2004

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:744625626787

    Also, Johns suggestion about migrating historical data in advance makes good
    sense.

    Cheers,

    David
    On 9/19/07, Syed Jaffar Hussain wrote:


    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.

    Do you see any another solution for this task?
    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 19, 2007 at 10:12 pm
    Greetings

    Here is my 2 cents

    Upgrade the existing HP to 10g

    Use Transportable Tablespace option to move it to AIX (TTS and FTP)
    if you can cross mount the FS, it will be faster to move/copy instead of
    ftp/sftp

    in 10g TTS can be done across different platforms

    Search Metalink or this group for more details about cross platform porting.

    Regards
    BN
    On 9/19/07, Syed Jaffar Hussain wrote:

    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."
    --
    Regards & Thanks
    BN

    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Sep 20, 2007 at 12:44 am
    Sounds like a good idea to me too, but if you do go with this plan, be
    especially wary of using datapump to move the metadata for your TTS -
    that is one of the specific areas where I saw much worse performance
    with DP compared to exp/imp (HOURS vs. minutes!) - be sure to test
    though - YMMV.


    Regards,
    Brandon

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of BN




    Upgrade the existing HP to 10g


    Use Transportable Tablespace option to move it to AIX (TTS and FTP)

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Ray Feighery at Sep 20, 2007 at 6:54 am
    I carried out a similar project to this several years ago.

    The existing warehouse had a feed from the mainframe which was uploaded nightly.
    This was duplicated so that it was also loaded into the new system
    (which was precreated with empty objects). Both systems ran
    concurrently for a month, with the new data being loaded into both
    systems. During this time we migrated the historical (non volatile
    data).

    At the end of the month, the two databases were logically in synch
    leading to a very short downtime for migration.

    Ray
    On 19/09/2007, Syed Jaffar Hussain wrote:
    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."
    --
    http://www.freelists.org/webpage/oracle-l
  • Ken Naim at Sep 20, 2007 at 12:01 pm
    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_at_freelists.org
    On Behalf Of Syed Jaffar Hussain
    Sent: Wednesday, September 19, 2007 6:44 AM
    To: oracle-l_at_freelists.org
    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."

    --
    http://www.freelists.org/webpage/oracle-l
  • J.miranda_at_sermatica.es at Sep 20, 2007 at 1:59 pm
    This script start 4 simultaneous export-import.
    The export writes to pipe and then rsh´d to the other server.
    Import read the export´s flow from other pipe.

    There are no intermediate write between export and import.

    ##!/bin/bash -x
    #

    for tb in `cat tablas.txt`
    do

    echo "Lanzo import en LOCAL: " $tb
    echo mkfifo /oracle10/tmp/$tb.pipeimp
    echo imp file=/tmp/$tb.pipeimp log=/tmp/imp_$tb.log parfile=/tmp/imp.par

    echo "Lanzo export en REMOTOR: " $tb
    #echo mkfifo /tmp/$tb.pipeexp
    #echo exp file=/tmp/$tb.pipeexp log=/tmp/exp_$tb.log tables=DWP.$tb
    parfile=/tmp/exp.par & remsh hpdwprod 'cat > /tmp/$tb.pipeimp' <
    /tmp/$tb.pipeimp

    rsh -l oracle10 nodo1 ". /oracle10/.bash_profile; mkfifo
    /oracle10/tmp/$tb.pipeexp; exp br/br tables=$tb
    file=/oracle10/tmp/$tb.pipeexp owner=br & rsh -l oracle10 nodo2 'cat >
    /oracle10/tmp/$tb.pipeimp' < /oracle10/tmp/$tb.pipeexp" &

    echo -----------------------------------------------------

    echo "Compruebo numero de imports lanzados"
    ps aux |grep imp |grep -v grep > /tmp/import.txt
    imports=`wc -l /tmp/import.txt |awk '{print $1}'`
    echo "numero de import ejecutandose: " $imports

    #Si hay mas de 4 imports espero
    while [ $imports -ge 4 ];
    do
    echo imports $imports mayor que 4 .... duermo
    sleep 3
    ps aux |grep imp |grep -v grep > /tmp/import.txt
    imports=`wc -l /tmp/import.txt |awk '{print $1}'`
    echo $imports
    done

    done

    imp.par:
    USERID=system/passwd
    statistics=none
    fromuser=RESTORES
    touser=RESTORES
    buffer=50000000
    constraints=n
    grants=n
    indexes=n
    ignore=Y

    exp.par:

    USERID=system/passwd
    direct=y
    buffer=50000000

    recordlength=65535
    indexes=n
    constraints=n
    statistics=none
    grants=n

    We tried this method but the bottleneck is the redolog writing.
    There are a hidden parameter that disable overal logging but isn´t
    recomended, of course.

    Hope this help you.

    greetings.
    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_at_freelists.org
    On Behalf Of Syed Jaffar Hussain
    Sent: Wednesday, September 19, 2007 6:44 AM
    To: oracle-l_at_freelists.org
    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."
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 19, '07 at 10:43a
activeSep 20, '07 at 1:59p
posts13
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase