How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g

oracle-10goracle-11gupgrade

How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!

Best Answer

30-90 minutes according to Oracle's Best Practices for Upgrading. This is about the closest estimate you will get given all the unknowns in this situation.

The size of the database really matters very little in determining how long the upgrade will take. Here are the main factors effecting the duration (from the Oracle.com upgrade blog):

  • 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 restricted upgrade mode causing additional 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

Here are some additional factors you may want to consider that aren't related to the core of the upgrade itself:

  • Whether the pre-upgrade script has been run and issues resolved.
  • How many invalid objects there are.
  • If the upgrade is being done in place (vs. Import/Export, Streams, Data Guard, etc.)
  • Whether the DBUA or scripts are used to do the upgrade.
  • If new Oracle home pre-installed.
  • Disk speed and throughput.
  • Other CPU/Disk activity occurring at the same time.
  • Archive Log Mode.
  • Other changes being made with the upgrade.
  • If a cold backup is necessary before and/or after the upgrade.
  • Any patch sets or one off patches that will also be applied.
  • How much verification of the upgrade needs to be done before it can be made available.

Probably the biggest factor effecting the upgrade is the unknown factor. Even when the upgrade is practiced in advance on similar hardware with similar data sets, etc. things can still occur that were unanticipated and can drastically effect the duration. With that in mind you should mimic the production environment as closely as you can for the test upgrades. That is, as close as your budget will allow.

If space is the issue preventing you from testing the upgrade, consider restoring the database to a test box excluding some of the larger user tablespaces. This won't give you an exact feel for the time, but it should give you a closer ballpark and allow you to work through more of the unknowns.

Related Question