How long will your upgrade take?

It seems to be a simple question but to predict the upgrade duration is really hard to do.


Due to the fact that an Oracle database upgrade doesn't touch or change data the duration is totally independent of the size of the database. It won't make any difference if the database is 2 GB or 28 TB of size.

The main factors determine the upgrade duration in descending order are:

  • Number of installed database components and options - the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it'll take

  • Valid and non-stale dictionary statistics - even though creating dictionary statistics in some older releases of Oracle wasn't a brilliant idea since desupport of the rule based optimizer the data dictionary should be analyzed. Especially right before an upgrade. Otherwise this will happen during the upgrade while the database is started in a restriceted upgrade mode causing addtional downtime.

  • Number of rows in AUD$ if audit_trail is set to DB

  • Number of synonyms when upgrading from Oracle 9i - synonyms will be touched and get a new dependency in the dictionary in DEPENDENCY$ - if there's a high number (such as 100,000) this can eat up some time

  • Number of objects in XDB

  • At a very low rate if COMPATIBLE will be increased: Number of datafiles and the size of the redologs


though listed key factors may impact downtime during upgrade,but we still can give customer a clear answer how long their db upgrade will take.

Posted by on September 10, 2009 at 02:51 AM CEST #

Hi Weimin, would be interested how you do this - I can give an estimate, too - but no exact duration without having tested it - so feel free to let us know :-) Kind regards Mike

Posted by Mike Dietrich on September 10, 2009 at 09:31 AM CEST #

Hi Mike, You mention that ,"it would be a good practice to analyze the data dictionary right before an upgrade." I am curious to find out if this can adversly impact the performance of some the query in the database, by changing the execution plan etc. Is that something I should be worried about, or analyzing the data dictionary won't impact the execution plans of the SQL queries ? Regards Ashish

Posted by guest on March 05, 2010 at 01:35 PM CET #

Analyzing the Data Dictionary is not a great idea in Oracle 9i - but doing it right before the upgrade will just save upgrade time as otherwise it will be done during the upgrade and this adds downtime to the upgrade itselves. It won't effect execution plans. Kind regards - Mike

Posted by Mike Dietrich on March 08, 2010 at 05:12 AM CET #

Post a Comment:
  • HTML Syntax: NOT allowed

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING


« July 2015
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers