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:
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:
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:
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:
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.