Best Practices to Use Database Upgrade Assistant

I’m going to upgrade one of our main databases with DBUA (Database Upgrade Assistant), and I take some notes to provide a smooth upgrade. Upgrading your database with DBUA seems a very easy and automated task but there are three important points you should consider when updating:

  • Downtime: You would probably like to keep downtime to a minimum
  • Errors while upgrading: It’s not possible to re-run DBUA if an error is encountered mid-upgrade!
  • Performance Degradations: Most of the upgrade problems appear as performance degradations after the upgrade operation completed

So you need to carefully evaluate, plan, configure, test and implement the upgrade.

PLAN: First of all, I recommend you to visit the upgrade guides and plan all steps of the upgrade process:

Upgrade Advisor: Database from 9.2 to 11.2 [ID 264.1]
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]

RTFM: Although everyone says the same, we all intend to pass this step. Read the manuals before it’s too late 🙂

Very important and useful documents:

Oracle 11gR1 Upgrade Companion [ID 601807.1]
Oracle 11gR2 Upgrade Companion [ID 785351.1]

Use Internet Explorer to read upgrade companion documents (because they are interactive and requires IE) and don’t forget to check “behavior changes”!

PRE-UPGRADE SCRIPT: Bedore you start to upgrade your database, make sure you run pre-upgrade script to determine any issues with the upgrading process:

How to Download and Run Oracle’s Database Pre-Upgrade Utility [ID 884522.1]

MOST UPDATED HOME: It’s better to patch your new Oracle Home before your upgrade. Install the latest opatch available for your platform and database version, the latest available PSU and CPU.

INVALID OBJECTS: You should check and fix Invalid Objects, specially on SYS and SYSTEM user schema.You can recompile invalid objects with utlrp.sql (or manually).

STATISTICS: Create dictionary statistics prior to the upgrade! When upgrading, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming.

After you upgraded the database, create fixed table statistics and system statistics during a regular workload period.

BACKUP: You should always have a fallback strategy. So you should take a backup before you start upgrading. Storage Box solutions (clone/snapshot) could be a useful option.

PARAMETERS: Before you upgrade, it’s recommended to remove old parameters, underscored parameters and events from the configuration file (init.ora/spfile).

NOARCHIVELOG: You can switch to NOARCHIVELOG mode to reduce the upgrade time and hence downtime unless you use a replication method based on archive logs (such as Data Guard).

You can switch to NOARCHIVELOG mode to reduce the upgrade time and hence downtime unless you use a replication method based on archive logs (such as Data Guard).

LIGHT DATABASE: If you have installed unnecessary components to your Database, removing them will help you to minimize downtime while upgrading your database. You can query which features are installed by the following query:

It’s also recommend to truncate audit trail table before upgrading (of course after taking backup).

And you can also purge recycle bin:

AVOID SURPRISES: Try to keep a backup copy of your current optimizer statistics and save execution plans:

How to: Manage CBO Statistics During an Upgrade to 10g or 11g [ID 465787.1]

COMPATIBILITY: When you upgrade your database from 10g to 11g, DBUA will not raise the COMPATIBLE parameter. Do not modify this compatible level and make tests on your newly upgraded databases. There’s no way back once you set compatible level to 11.1.0 or higher.

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.