Re: Oracle migration vs Exp/Imp

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

1. You say that you need to unload/reload to database to get the 'n' hours of downtime you want
2. You use migration
3. 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

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."