Upgrading an Oracle Database to version 11.2 – the parts that I didn’t expect

At the start of 2012 I was tasked to upgrade and Oracle 10.2.0.4 database running on HPUX 11.23 to Oracle 11.2.03.

The first step in any upgrade is to find the correct upgrade path and decide on which upgrade method best suits your specific needs. There are 4 main methods to choose between:

• Database Upgrade Assistant (DBUA)
• Manual Upgrade
• Export/Import
• Data Copying

As I was not moving platform it was a toss-up between the GUI and manual upgrade. I went with DBUA as I like the GUI and logging it supplies.

Another important issue at the planning stage is to make sure you plan the correct upgrade path.
See My Oracle Support note: Database Server Upgrade/Downgrade Compatibility Matrix [ID 551141.1]

In my case it was possible to upgrade directly from 10.2.0.4 to 11.2.0.3.

Upgrading

The steps on how to upgrade have been posted on the internet hundreds of times so there is no point in me describing the steps here. Instead I can list the most valuable links and experiences I used and had during my upgrade.

Click this link for Oracles 11g upgrade site. – This is the site of all sites and a great starting point to plan your upgrade. This is where you can expect to find all information on planning, methods, best practices, QA’s, testing, steps and back out methods. There are notes to My Oracle Support articles, powerpoints and other oracle documentation.

The main See Oracle Support Oracle 11 upgrade note is: DBUA Note 870814.1
and the 10.2 to 11.2 upgrade advisor Note is: 251.1
Here you will be able to step through the following: Evaluate, Plan, Configure, Test and Implement your upgrade.

One last link I used was Best Practices:
Best Practicies for 11g upgrades

My experiences and problems along the way

So you have done all your preparation and tested on another non production database and even have a good working backout plan (tested). So the next step is easy right? Start DBUA and press Next …. Wrong, well at least in my case it was…..

• When I started DBUA I got an error regarding OraInventory corruption. The only way around this for me was to restore the OraInventory from a backup. Lesson learned: backup your OraInventory directory and files.

• Again at the start of DBUA I got error ORA-0044 and DBUA hung. After raising an SR support told me to set STATISTICS_LEVEL=TYPICAL and TIMED_STATISTICS=TRUE and restart and this worked fine.

Note:

If you are using an spfile, simply:
create pfile from spfile;
Manually change the parameters and then:
create spfile from pfile;

• During the DBUA run at 86% the program hung at the stage, Configuring Database with enterprise Manager after the Post Upgrade section. It hadn’t written to the log file for 20 minutes. Again I raised an SR and was told the upgrade had completed successfully and to simply kill the unix process running DBUA. The problem was due to duplicate objects in the sys and system users which should have been cleaned up at the start manually. These duplicated had been there since at least a previous 10g upgrade. Read more about this here: How to Diagnose Invalid or Missing Data Dictionary (SYS) Objects [ID 554520.1]

• Another specific issue I had was with XDB. It was installed but unused in my installation and for some reason it was unable to upgrade so I had to uninstall and reinstall this module which was relatively simple. See how to do it here:
Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

Other than these problems the upgrade was very straight forward and 11.2 has been running well now for 9 months. So theres only 1 more thing to do …. Start planning for a 12C upgrade in 2013! 🙂

Useful points to remember

Plan and document your upgrade well

Test test and test again

Have a backout plan

Read up about Timezones and be sure to follow the instructions – it’s simple just be sure to follow the instructions oh

I am an Oracle DBA/Developer with experience in Oracle databases since 1996.