RE: 10g upgrade survey

From: Ajay <ajay_1ajay_at_yahoo.com>
Date: Mon, 19 Mar 2007 18:09:56 -0700 (PDT)
Message-ID: <484213.72648.qm@web59012.mail.re1.yahoo.com>

Export/import does have benefits like defrag and it would help in most cases but please note that if you have some huge tables and you are doing defrag after a long time : some explain plan might change. most will change for good but there will be some that might change for bad.

If you do not want to wonder if performance issue is because of upgrade or defrag please take note. You can maybe retain the statistics in some way. You also do not want to have to wonder if you brought everything with the import or not. Maybe check the import logs 10 times and also have a count of all objects before and after.

So do upgrade in upgrade window and defrag in separate window. In some cases of nls char set I think the data can also change as a result of export/import (yes it can eg. from } to ý. from chr(125) to chr(253)) so if you decide to go the export/import please make sure you have this figured out.

"Allen, Brandon" <Brandon.Allen_at_OneNeck.com> wrote: 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:

You can retain your old version of the database, completely unchanged, as a fallback in case of problems during/after the upgrade.
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.

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:
      
       Defragments the data - you can compress the imported data to improve     performance.
    
       Restructures the database - you can create new tablespaces or modify     existing tables, tablespaces, or partitions to be populated by imported     data.
    
       Enables the copying of specified database objects or users - you can import     only the objects, users, and other items that you wish.
    
       Serves as a backup archive - you can use a full database export as an     archive of the current database.

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.

We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list.

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

Received on Mon Mar 19 2007 - 20:09:56 CDT