RE: 10g upgrade survey

See inline below...

Robert G. Freeman
Oracle Press Author
Principle Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints Collaborate '07
Come join my APEX University Session
and Join me to talk about Evolutionary Database Development!! The LDS Church is Hiring DBAs! Contact me if you are interested!

  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Allen, Brandon   Sent: Monday, March 19, 2007 5:39 PM
  To: oracledba.williams_at_gmail.com; Oracle Discussion List   Subject: RE: 10g upgrade survey

>> I'm reviving this topic because I happen to have a real-life need to
do another 10g upgrade and in planning
>> I came up with a couple more benefits of using exp/imp:

   >> 1) You can retain your old version of the database, completely unchanged, as a fallback in case of problems
>> during/after the upgrade.

  You can obtain this same benefit with a simple database backup, or if you just *must* have a second database, just duplicate it, which will be much faster. Either way, this will require 2 Oracle Homes of course.

   >> 2) You can go directly from any version to 10g, rather than having to be on 8.1.7.4, 9.0.1.4+, 9.2.0.4+ or
>> 10.2.0.2+, which are the prerequisites for a direct upgrade to 10.2.
In my case, I've currently got about 50
>> databases all running on 8.1.7.3 - do I want to go through the trouble
of patching them all to 8.1.7.4 just as a
>> stepping stone to 10g, or just go straight to 10g via exp/imp? I think
I'll go for the shorter path.
  If you are using an unsupported version of Oracle, then I agree that exp/imp becomes a viable alternative. Still you have to weigh the time it takes to export and then import the database against the time it takes to do, say, two upgrades. If the databases are fairly small, then it's a no brainier probably. If you have a 500TB database perhaps it's not such a no brainier.

   >> Also, I found these comments from the 10.2 upgrade guide http://download-
>>

west.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#BABFHFIJ:

   >> Export/Import Benefits
   >> Upgrading using Export/Import offers the following benefits:

    a.. >> Defragments the data - you can compress the imported data to improve performance.

    Yep, this is true. However, if your data is fragmented then you have bigger problems. I think it's silly to depend on your upgrade process to be responsible for defragmenting your database. Manage the thing properly and this should not be an issue, right?

    b.. >> Restructures the database - you can create new tablespaces or modify existing tables,

    >> tablespaces, or partitions to be populated by imported data.     True, but what does exp/imp really buy you here? What do I get using exp/imp that I don't get with the use of a simple alter table or rename command? Also, keep in mind that import (unlike Datapump import) does not have direct mode insert capability. If you need to restructure to the point that you are creating a totally new table, it will probably be faster to do so with direct mode inserts such as CTAS or using the /*+ APPEND */ hint, don't you think?

    c.. >> Enables the copying of specified database objects or users - you can import only the objects, users,

    >> and other items that you wish.
    I suppose if this is the requirement, then this makes sense. However, I would never mix and match upgrades with schema changes myself. An upgrade is a big enough event, why make it more complex?

    d.. >> Serves as a backup archive - you can use a full database export as an archive of the current

    >> database.
    as does a regular, good old fashioned physical backup.....plus with a physical you get all sorts of benefits that you don't get with exp/imp.... point in time recovery, datafile and tablespace recovery, blah blah blah..

    All in all, I don't see these as strong arguments for exp/imp myself.

  Regards,
  Brandon

--
  From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Dennis Williams
  Sent: Monday, March 05, 2007 6:31 PM
  To: Oracle Discussion List
  Subject: Re: 10g upgrade survey


  List,

  Here are the results as I tally them so far.

  Favorite upgrade method: Manual by a whisker (favored by 7 vs. 6 DBAs, as
I count them, double counting some people who favored both methods)

  A close runner-up was Exp/Imp.

  Naturally if you have a large database and a short downtime window,
exp/imp may not work for you.

  One person stated he had used dbua successfully many times. Three others
stated they were bitten by dbua.

  Thanks for everyone's input.

  Dennis Williams

  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