Oracle Upgrade

By Edward Stoever

Notes Regarding Bugs when upgrading a database, or creating a new database. 

These notes are derived from my personal experience upgrading Oracle on VMS from version 8.1.6.0.0 to 9.2.0.5 

In these notes, test is the database name...

When you create an Oracle database in either one of these versions, DBCA will create a new listener.ora file for you located at

(oracle_home)[network.admin]listener.ora

Unfortunately, this file will not allow you to connect to the database. It will return an error like this:

ORA-12500: TNS: LISTENER FAILED TO START A DEDICATED SERVER PROCESS

After much fighting with this, I learned that you must add the following line right under the line (SID_NAME=TEST) to the listener.ora file:

(PROGRAM = DISK3:[ORACLE.V92.NETWORK.ADMIN]ORASRV_NETV2_TEST.COM)

The importance of including this is documented in this METALINK note. However, I have found no documentation that DBCA for VMS fails to include it.

 = = = = = = =
IMPORTANT
If you have any datafiles that are larger than 4 gigabytes, you will need to apply the patch #2673723 before you install the upgrade/patchset 9.2.0.5. If you do not, you will not be able to open your database again! You will get an error like so:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: 'DISK6:[ORACLE.V92.ORADATA.TEST]ORA_DEVELOP01.DBF'
ORA-01200: actual file size of 179712 is smaller than correct size of 704000 blocks

What I find most interesting about this patch is that it was released a few weeks after the patchset 9.2.0.5.

I suppose quite a few people were pretty angry at Oracle for this one!