Upgrade Headaches

When you upgrade a lot of things will be different. Your hope is that they will at least average out, and if they do, or things even get better, you are in good shape. If not, it is nice to know what may have gone wrong.

First, before you upgrade, why are you doing it? New features in Oracle (will your application use them?). A new application that needs the new features? (a good reason). Oracle says they will no longer support the old version? (if you don't have it running well now, a new version is not going to help, you need something more). One rule for an upgrade is that we are doing it now, and we will not do it again next year. No program is so bad that it needs have a year's work put into it so that we can start tearing it out a year from now. Benefit: $0.

If you are updating the OS at the same time, you need to test each part, frequently (and often sadly) the old OS and database do not fit in the partitions you used five years ago. Test it all out.

Next, can Oracle's tools help you? Oracle's Database Upgrade Assistant (DBUA) can walk you through an upgrade but first make sure that your current database can upgrade to your new edition; for instance, early versions of 9 have to upgrade to a later version of 9 before they can upgrade to 11.

Database Versions Supported by DBUA

DBUA supports the following versions of Oracle Database for upgrading to

Oracle Database 11g Release 1 (11.1):

• Oracle9i Release 2 (9.2.0.4) and beyond

• Oracle Database 10g Release 1 (10.1)

• Oracle Database 10g Release 2 (10.2)

If your database version is not in this list, then you need to upgrade first to the closest release listed. You can then upgrade the database to Oracle Database 11g Release 1 (11.1).

Next, what can go wrong?

Well, even if Oracle says it all went well, there are things that can make that a bit less correct, like things that are working in a different way. Here is possible list:

DBMS_STATS - covered elsewhere in this blog.
FREQUENCY HISTOGRAMS Been using the analyze command? This is different.
CPU costing can change CBO plans
ROUNDING ERRORS the way the optimizer deals with rounding varies with the edition.
BIND VARIABLE PEEKING some code may change its execution path
(about 20 other things to look at)

What can go right?

If you have tables that are read four or more times to each write, they should be moved to IOTs (Index Organized Tables) which will be a lot faster; slower to write to, but for look up tables, they can be way faster.

Getting rid of chained rows in old tables that are still being read. Getting rid of chained rows is just like getting 10% off; chained rows make data retrieval tough.

Test code and rewrite bad code. Old applications can have a lot bad code, if for no other reason than SQL has got a lot faster and more powerful. Also, a lot of old code was never tested at all, it just did something so they let it go.

Better security. We know more about security today.

Multi block reads for quick full scans of indexes (used to be a hint, now mostly automatic).

Better comments.

Better instrumentation: a way to see what code is doing when we need it.