By Carol Palmer

To ensure a successful upgrade to Oracle Database 11g Release 2, there are important best practices to follow. Many of these best practices involve preparing for an upgrade; preparing is more time consuming than the upgrade itself.

This article talks about direct upgrades to Oracle Database 11g R2 using either the Database Upgrade Assistant (DBUA) or the manual upgrade (catupgrd.sql or command line script). These methods are straightforward and well-tested. Both require the upgrade to remain on the same operating system but will allow a change in word size, such as 32 bit Windows to 64 bit Windows. Database migration is not covered in this article. Database migration means changing operating systems, platforms, etc. and is out of the scope of this article.

Out-of-place upgrades have always been a best practices recommendation, but starting with Oracle Database 11.2.0.2, patch set installations are all out of place by default. This is where the patch set is installed into a new, separate Oracle Home. An out-of-place upgrade is safer because you are not applying a patch set to a production Oracle Home. It also minimizes downtime. An in-place upgrade is where the patch set is installed into the same existing Oracle Home, which is risky and not recommended.

Differences Between the DBUA and the Command Line Script — Choose Your Upgrade Method

The DBUA is a graphical tool that uses the same underlying command line script as the manual upgrade. It is a useful tool for those that prefer a graphical user interface. It’s also valuable in that it automates things for you, and it is less error-prone than the manual method.

There are two restrictions with using the DBUA. The first is that you can only use it on the same system; you cannot go to a new hardware platform. The other is that it is not re-runnable, so if you run into a problem during the upgrade, you would have to use the manual method from that point forward.

The manual method is useful for going to a new hardware platform with the same operating system. However, there is the potential for typos and errors, so be careful when going through all the steps.

In both cases, the usual amount of time for the upgrade is between 30 to 90 minutes. Tips to speed up the upgrade are listed later in this article.

For direct upgrades to Oracle Database 11gR2 for either method, you must be on Oracle Database 9.2.0.8, Oracle Database 10.1.0.5 or Oracle Database 10.2.0.2 or greater.

Prepare for the Upgrade — Gather Information

The first thing to do is to get information about the latest recommendations for upgrading.

If you prefer a web-based approach for information, read the online Database Upgrade Advisors that are available from 10.2 to 11.2 and 9.2 to 11.2. The upgrade advisors help you plan and execute your upgrade. They are available on My Oracle Support:

• Note 250.1: Upgrade Advisors
• Note 251.1: Database Upgrades from 10.2 to 11.2
• Note 264.1: Database Upgrades from 9.2 to 11.2

If you prefer reading the available documentation on upgrades, there are excellent materials to help you:

• Note 1152016.1: Master Note for Oracle Database Upgrades and Migrations
• Note 78535.1: Upgrade Companion 11g Release 2 (cookbook approach)
• Note 837570.1: Complete Checklist for Manual Upgrades to 11g Release 2
• Note 1320966.1: Things to Consider Before Upgrade to 11.2.0.3 Database Performance

Of course, the Oracle Database Upgrade Guide 11g Release 2 (E10819-02) is always good to have as a reference tool.

In addition, Oracle’s Database Upgrade Development Group has an upgrade site on the Oracle Technology Network (OTN) that has a wealth of information: webcasts, presentations, white papers, links to other upgrade materials, a forum and a blog for the latest on upgrade events: www.oracle.com/technetwork/database/upgrade/index.html.

Important Steps to Do Before the Upgrade

There are several important steps to complete before the upgrade is complete:

• Take a backup with Oracle Recovery Manager (RMAN). Make sure you have a fallback strategy and test it to make sure it works.
• Run dbupgdiag.sql to get a list of all components and objects and a general status of the existing Oracle server.
  • Note 556610.1: Script to Collect Database Upgrade/Migrate Diagnostic Information
• Gather performance data before you upgrade using Oracle Real Application Testing (RAT) or another tool. Take performance snapshots with Oracle Automatic Workload Repository (AWR) if you are licensed for Oracle Diagnostics Pack. Otherwise, use Oracle Statspack.
• Check for invalid objects. There should be no invalid objects, especially in SYS and SYSTEM schemas. You should fix them by recompiling them with the utlrp.sql script.
• Check for duplicate objects in SYS and SYSTEM and fix them before the upgrade.
  • Note 1030426.6: How to Clean up Duplicate Objects Owned by SYS and SYSTEM Schemas.
• Check for nonvalid components and fix them by recompiling them. If that doesn’t work, refer to the following notes:
  • Note 472937.1: Information on Installed Database Components
  • Note 753041.1: How to Diagnose Components with Non-Valid Status
• Purge the recycle bin if upgrading from 10g or 11g.
• Run the pre-upgrade script, which analyzes your source database and gives you recommendations on things to update or change before the upgrade: utlu112i.sql.
  • Note 884522.1: Download current version (utlu112i.3sql)
• Remove old parameters, underscores, and events from your init.ora/spfile.
• Create dictionary statistics the night before the upgrade.
  • 9i SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS;
  • 10g/11g SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
• If possible, leave COMPATIBLE at 10.1 for a week before changing to 11.2. Otherwise, you cannot downgrade back down to 10.1 if you run into a problem. Note that the DBUA raises COMPATIBLE only for 9i databases.

What Release Should You Go To?

You always want to install the latest patch set that is out and have a fully patched Oracle Home before you upgrade. Right now, the latest patch set is Oracle Database 11.2.0.3. This means after installing 11.2.0.3, you install Patch Set Updates (PSUs), any bundled patches and one-off patches.

Before downloading and installing the software, check the certification of 11.2.0.3 with your platform/operating system. Go to My Oracle Support and the certification tab.

Order of Installation

1. Download 11.2.0.3 from My Oracle Support. There is no need to install 11.2.0.1 first. Go to the “Patches and Updates” tab and you will see
   “Patching Quick Links.” The latest patch sets are listed. By default, starting with 11.2.0.2, all patch set installations are out of place and require a new Oracle Home. It’s much less risky to have an out-of-place install than applying a patch set to a production Oracle Home.
   • If you need to do an in-place install, however, perhaps because of a storage issue, it is possible. The Oracle Database 11g Release 2 Installer issues a warning telling you an in-place upgrade is not recommended but gives you the steps to follow to do so.
2. Install the recommended database patches, starting with the latest patch set update. PSUs are released quarterly, just like critical patch updates (CPUs): January, April, July, and October. PSUs contain the security fixes of CPUs as well as other important fixes, so it is recommended that you install the PSUs versus the CPUs.
3. Install any bundled patches.
4. Install any one-off patches.
5. Install recommended operating system patches.

Note that a great tool to use is the upgrade planner on My Oracle Support. The upgrade planner takes the guesswork out of identifying all the patch recommendations for your platform. You can download it all in one operation. It requires the use of Oracle Configuration Manager 10.3.2 or newer to be installed on your system.

In summary, until 11.2.0.4 comes out, download 11.2.0.3 with the latest PSU.

• Note 1189783.1: Important Changes to Oracle Database Patch Sets Starting with 11.2.0.2
• Note 756671.1: Recommended Database Patches
• Note 854428.1: Introduction to Database Patch Set Updates
• Note 1227443.1: Patch Set Updates Known Issues
• Note 161818.1: Oracle Database Releases Status Summary with important alerts and one-off patches
• Note 169706.1: Operating System Installation and Configuration

Testing

Perform a test upgrade to make sure it completes successfully. Make sure your applications run correctly. Did the upgrade time meet your time frame? Do more performance testing and compare it to the pre-upgrade performance data.

Perform the upgrade with either the DBUA or command line script.

Important Steps to Do After the Upgrade

There are several important steps to complete after the upgrade is complete:

• If upgrading from 10.1 or later, run catuppst script to perform post-upgrade actions. This script updates AWR baseline information, ADDM task metadata, and OLS policies. It can be run in normal mode.
• Create system statistics during a regular workload period.
  • SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS(‘start’);
  • SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS(‘stop’);
• Create fixed table statistics directly after catupgrd.sql has completed and again after a week with regular production workload.
  • SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
• Apply the time zone patch if recommended by the pre-upgrade script. If you are upgrading from 11.2.0.2 to 11.2.0.3, there is no need to apply the time zone patch, as both releases are on Version 14.
  • Note 1358166.1: Actions for DST Updates When Upgrading to or Applying the 11.2.0.3 Patch Set.
• Create an editable init.ora from the current SPFILE.
• Run the dbupdiag.sql script or the utlu112s. SQL script to determine the success of the upgrade.

Length of the Upgrade

The size of a database has nothing to do with the length of the upgrade. The length of the upgrade is mainly dependent on the number of installed components and options. The more components and options, the more scripts have to run. It also matters if the data dictionary statistics are stale. If they are, those will be gathered during the upgrade and will increase the upgrade time.

In addition, if you are upgrading from Oracle Database 9i and have a large number of synonyms, they have to be recompiled, so that will add some time to the upgrade.

What can be done to speed up the upgrade?

• As mentioned above, run dictionary statistics the night before an upgrade.
• If possible, switch off archiving. Remember to put it back on after the upgrade is complete. The risk associated with not being able to recover to a particular point has to be weighed against any time gained from this recommendation. Only testing in your environment can answer that question.

Troubleshooting

If something goes wrong, look at the following:

• Before and after runs of dbupdiag.sql
• Alert log from 11.2.0.3
• Output from ORACLE_HOME/rdbms/admin/utlu112isql
• Output from catupgrd.sql (DBUA spools the output to the Oracle_Server log – $ORACLE_HOME/cfgtoollogs/dbua

Summary

The key to a successful upgrade to Oracle Database 11g Release 2 is to prepare for the upgrade by following the best practices outlined in this paper. I encourage you to review the references in My Oracle Support and the Oracle Technology Network Upgrade site for the most up-to-date information on upgrades.

About the Author

Carol Palmer is a principal product manager in software development for Oracle Corporation. She is responsible for the product management of Oracle Database Upgrade. She works with various Oracle product teams with a goal of providing a simple, reliable upgrade solution for Oracle Databases. Palmer is also responsible for the product management of Oracle Data Pump and SQL*Loader, a set of tools that allow fast and easy data transfer, maintenance and database administration of Oracle databases. Palmer has been with Oracle for 17 years. She has studied management at M.I.T. Sloan School in Cambridge, Mass., and business administration at the Whittemore School of Business and Economics/University of New Hampshire.