Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle migration vs Exp/Imp

Re: Oracle migration vs Exp/Imp

From: koert54 <koert54_at_nospam.com>
Date: Sat, 13 Jul 2002 10:09:26 GMT
Message-ID: <q%SX8.2535$8o4.382@afrodite.telenet-ops.be>


> doesn't feel like that at the time). It is also extremely slow.

Depends on which box :-) did an 80GB export/import in less than 1h last week with parallel export/import (http://sourceforge.net/projects/pepi http://pepi.sourceforge.net)
It was a 6way 600Mhz RS6000 box with SSA disks (I/O I/O SSA is the way to go :-) ) and system and redo on raw devices ...

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:agor3b$d1v$1_at_lust.ihug.co.nz...
> Personally, I wouldn't touch export and import with a barge pole, unless
it
> was a trivially small database. It can be very picky at times, and throw
> exceptions without apparent cause (there always *are* explanations, but it
> doesn't feel like that at the time). It is also extremely slow.
>
> Regards
> HJR
>
>
> "Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message
> news:7ca2852.0207130056.15483c8c_at_posting.google.com...
> > Hi
> > Thanks for your views. I infer the following from your postings:
> > 1.The method we choose is entirely dependent on the db size and
> > downtime only. 2.Technically it doesn't matter whether we use exp/imp
> > or mig utility, the changes that occur in the db are the same either
> > way and hence we need not have undue worries about the data integrity
> > and the application, whatever it is can continue to run smoothly for
> > all db operations.
> >
> > Thanks & regards,
> > Ashok
> >
> > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
> news:<3D2F2DAF.48A7_at_yahoo.com>...
> > > Pete Sharman wrote:
> > > >
> > > > In article <cnxX8.33467$Hj3.101010_at_newsfeeds.bigpond.com>, "Richard
> says...
> > > > >
> > > > >Just to add a couple of points.
> > > >
> > > > And one more. In my experience, DBA's have not used the migration
> utility for
> > > > three reasons (so maybe that should be three more?):
> > > >
> > > > 1. In some releases, it just didn't work. That left a bad memory
and
> DBA's
> > > > don't like being burnt once, let alone twice.
> > > >
> > > > 2. Just in general, DBA's don't trust the utility. "You mean
you're
> going to
> > > > change my database file headers on me? Oh no you're not!" Ensuring
a
> backup is
> > > > done beforehand should be enough to get rid of this fear, but
> generally doesn't
> > > > (DBA's also being somewhat cynical!)
> > > >
> > > > 3. Many times, migration time is the only downtime available to
> DBA's. So
> > > > let's use EXP/IMP so we can re-spread the I/O and so on. You also
> sometimes
> > > > still hear the old urban myths about many extents yaddah yaddah at
> this time,
> > > > but thankfully that's starting to go away.
> > > >
> > > > Pete
> > > > >
> > > > >I've asked this question several times in the past and surprisingly
> (to me
> > > > >anyway) the more common answer is the exp/imp method.
> > > > >
> > > > >This reason for this appears to be that emp/imp is much like an old
> friend.
> > > > >Most DBAs are comfortable with it, knows what it does, how to use
and
> so on.
> > > > >The migration utility / manual migration is all a bit "spooky" and
so
> is
> > > > >often not used. Now I'm not saying this is a legitimate reason but
it
> is a
> > > > >common one (the fear of the unknown, comfort factor etc). One
> advantage of
> > > > >using exp/imp is that the database is reorganised as a result
> (tablespaces
> > > > >become defragmented, HWMs are reset, indexes rebuilt etc.). This
> could be of
> > > > >benefit (or it might not).
> > > > >
> > > > >The migration utility / database convert is much faster as Howard
has
> said
> > > > >and that it's key advantage. You must take a backup mind you (else
> Clint
> > > > >Eastwood type voices start in your head "Do you feel lucky punk, DO
> YOU !!")
> > > > >and some other pre work so that needs to be factored in.
> > > > >
> > > > >Read up on all the possible methods (and posting here is a good
> start) and
> > > > >pick the one that's right for you.
> > > > >
> > > > >Cheers
> > > > >
> > > > >Richard
> > > > >"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > > >news:agm1d3$hqh$1_at_lust.ihug.co.nz...
> > > > >>
> > > > >> "Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message
> > > > >> news:7ca2852.0207112332.20f1908e_at_posting.google.com...
> > > > >> > Hi
> > > > >> > Under what circumstances do we have to follow the export/import
> method
> > > > >> > of upgrading or migrating a database ? When is it advisable to
> use
> > > > >> > odma and run the migration utility of oracle or do the
> > > > >> > upgrade/migration manually? I think the easiest method is
> > > > >> > export/import for any platform and version. Kindly share your
> > > > >> > opinion/knowledge in this regard.
> > > > >> >
> > > > >> > Cheers,
> > > > >> > Ashok
> > > > >>
> > > > >> It's certainly *not* the easiest method. For a start, assuming
you
> don't
> > > > >> trash your old database the moment it's been exported, your
machine
> needs
> > > > >> the resources to run two databases. Second, export does selects
and
> import
> > > > >> does inserts -neither of them the fastest operations under the
sun.
> On any
> > > > >> reasonably-szed database, the export can take hours, and the
import
> more
> > > so.
> > > > >> Then there's the issue of how you propose to export a 120Gb
> database...
> > > that
> > > > >> dumpfile is going to be pretty big! Yes, there are ways around
that
> on
> > > Unix
> > > > >> (pipes etc), but it's not exactly "easy".
> > > > >>
> > > > >> By contrast the migration utility does an in-place migration of
an
> > > existing
> > > > >> database. It's not especially fast, either, but at least there's
> only one
> > > > >> database in existence, you are modifying the database in situ,
and
> you
> > > don't
> > > > >> have to read/write all the data twice.
> > > > >>
> > > > >> And from that distinction flows the usual recommendations: use
> whichever
> > > > >> method is appropriate. If you can't afford downtime on the
> production
> > > > >> database, export-import is a good bet, because you can prepare
the
> new
> > > > >> database whilst the old one is still in use (presumably not
> permitting
> > > fresh
> > > > >> DML, however). If it's a small database, export-import is also a
> good bet,
> > > > >> because the dumpfile sizes aren't a consideration. If it's a
large
> > > database,
> > > > >> or you've not the resources to have two versions of the database,
> or
> > > > >> downtime is not a consideration, then the migration utility is
the
> better
> > > > >> choice.
> > > > >>
> > > > >> Regards
> > > > >> HJR
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > > HTH. Additions and corrections welcome.
> > > >
> > > > Pete
> > > >
> > > > SELECT standard_disclaimer, witty_remark FROM company_requirements;
> > >
> > > I like using "both" approaches...Namely
> > >
> > > a) You say that you need to unload/reload to database to get the 'n'
> > > hours of downtime you want
> > > b) You use migration
> > > c) You use the remaining spare time to issue 'alter table move',
'alter
> > > index rebuild' et al to reorg/relocate/etc/etc/etc/ as needed
> > >
> > > hth
> > > connor
>
>
Received on Sat Jul 13 2002 - 05:09:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US