When is the correct time to upgrade Oracle?

I get the question from my customers every now and then if they do need to upgrade all their databases to the latest and greatest release. You have three options here: Upgrade as seldom as possible, upgrade as soon as the possibilty is there, and finally, do nothing!

You as a customer may be happy with the way the database is working today – If it works, don’t touch, it is a saying out there. I usually start with asking which version of Oracle they do have, and how many different versions they have altogether. I may as well ask what their security policies are and if they care for getting appropriate support. The answer is often that they have a mixture of Oracle9i, Oracle10g R1 and perhaps also some Oracle10g R2 databases. Oracle9i hasn’t been supported by Oracle for a while and Oracle10g will soon loose its ordinary support status. Security is as always very good because they have invested in a firewall and DMZ and all that stuff, I’m told.

It is always interesting to see what literature they have in their book shelves. Sometimes I find Good Old Oracle7 or Oracle8i manuals. By the way, may favourite URL for Oracle documentation is http://tahiti.oracle.com. It has almost everything I’m looking for.

Now is the right time!

I could of course told you that you should upgrade because you will get access to all these nice and cute little features waiting for you, but the truth is that most customers doesn’t care!
Most applications are only using a few of the possibilities available in newer releases, that’s why they kept the old documentation. However, I have plenty of reasons why you should upgrade.

Support

First of all, you get much better support when you are on track with the releases. You are the King!

Oracle9i, Oracle10g R1, Oracle10g R2 and now Oracle11g R1 and R2

There are, for the moment two releases you should consider upgrading to: Oracle11g R1 version 10.1.0.7 or Oracle11g R2 version 11.2.0.1. That is, version 1 or 2 of Oracle11g. Looking back in time at the earlier releases of Oracle. Oracle8 version 0 (8.0.6) was only used by a handful customers in Norway, they waited until version 1 came along, named Oracle8i, The i indicated that it was a database for the Internet. Same story with Oracle9i version 0 (9.0.1), most customers waited for Oracle9i version 2. Exactly the same happened with Oracle10g where only a few customers went production with version 1 (version naming started with 1 not 0, for a change). g in the name, was that for Grid or GB! Now most customers are at Oracle10g version 2 as it has been out there for some time. Not many customers are running Oracle11g R1, they are instead going for R2.

Known errors

It could be of interest to see how many documented errors there is in a release. Let’s take a look at Oracle10g R2:

The table should be read like this: if you upgrade from version 10.2.0.3 to 10.2.0.4, there are 1974 documented errors which has been corrected in the Oracle server-code. Upgrading from version 10.2.0.1 to 10.2.0.4 shows that 3113 documented errors has been corrected. You will also notice that the rate which the errors has been corrected is accelerating in 10.2.0.4 compared to earlier releases. The reason for that is that the more customers using the product, the more errors get corrected.

Looking at the numbers for undocumented errors shows the same trend, highest number for the last version of Oracle 10g R2. It is also interesting to observe that the number of undocumented errors in the OLAP-module (Online Analytical Processing) went down from 100 to 1! This means that if you are using OLAP, go for 10.2.0.4 or higher.

So, if you haven’t upgraded to Oracle11g yet, now is the time.

How to upgrade?

Ii is possible in Oracle10g R2 and above to clone the Oracle software. That is, you can copy your software from one directory to another. That means that you can keep the old version and upgrade a copy of it. You can upgrade parts of your installation. Start with the listener process in SQL*Net, continue with ASM (if you are using ASM), upgrade a test database, then the least important production database. Each database one by one. This method is even more useful on Windows where you may have to reboot the system if upgrading the original software.

Cloning of the Oracle software is described here. This example is for Linux and Unix. Substitute …/10.2.0/db_1 with …/10.2.0.4/db. I prefer db instead of db_1 as it doesn’t really tell what the 1 is for, 10.2.0.4 indicates which minor release is in use also.

oracle@ferrari:~ $echo $ORACLE_HOME
/oracle/product/10.2.0/db_1
oracle@ferrari:~ mkdir /oracle/product/10.2.0.4/db
oracle@ferrari:~ cp -rp $ORACLE_HOME /oracle/product/10.2.0.4/db
oracle@ferrari:~ cd /oracle/product/10.2.0.4/db/clone/bin
oracle@ferrari:bin perl ./clone.pl ORACLE_HOME='/oracle/product/10.2.0.4/db' ORACLE_HOME_NAME='Oracle_10204'    
...
oracle@ferrari:bin sudo /oracle/product/10.2.0.4/db/root.sh
...

When you have cloned Oracle to a new directory, upgrade that one, not the original one. Start with a test or reference environment, zip it or create a tar-file, copy it over to your development and finally to your production environment.
This way, you 100% sure all environments are the same, and you save time. All these, without having started the Oracle Universal Installer – OUI, more than once. Move password file and server parameter file (spfile) to your newly created ORACLE_HOME before you upgrade the database. Isn’t it easy?

But, before you start, make sure you have read all relevant README documents, of course. I would highly recommend 11g Upgrade Companion in Metalink note 466181.1. This is mandatory reading planning to upgrade an Oracle database.

Anyway, a few surprises on your way may happen while upgrading. For instance, the CONNECT role has only CREATE SESSION privilege in Oracle10g R2. But that’s taken care of while testing.

Please do note that there is at least one important thing in Oracle11g. You have the option of separating lower case from upper case in passwords. This is a good feature but may be overlooked in different places such as database links. Suppose you write the SQL like this:

 ...IDENTIFIED BY password...;

Should have been:

 ...IDENTIFIED BY 'password'...;

That way, the link will still work in Oracle11g. You noticed the difference? Single quote around the password.

Oracle11g R1 or Oracle11g R2

The question is: should I upgrade to R1 og R2? Well, for Windows, you still have to wait for R2. Unix and Linux shops can choose and decide what is most appropriate for them.

Good luck with the upgrade and remember to double check your backup before you start…