Oracle Database 9i Upgrade: Oracle Database 9i 32-bit to Oracle Database 12c 64-bit on Windows Part 1

On a recent client engagement, I was tasked with performing an in-place upgrade of an Oracle 9i 32-bit database on Windows Server 2003 Enterprise SP 2 32-bit to Oracle Database 12c on Windows Server 2008 R2 Enterprise SP1 64-bit as part of a proof of concept (POC) to determine whether the application software would be compatible with Oracle database 12c.

The Windows 2003 Server was configured with 8G of memory and was configured with AWE (Address Windowing Extensions) to allow the 32-bit 9i database to utilize the available memory on the server. The database was allocated 3TB of data storage, of which 2.1TB was used. Since this was Oracle 9i 32-bit, the database was configured to utilize filesystem storage for the physical datafiles and was configured with a 4K block size.  Oracle on Windows Server fun begins!

 

The version of the 9i database was Oracle 9.2.0.3.  The direct upgrade path to Oracle 12c is 9.2.0.3 to 9.2.0.8 to 12.1.0.2.  However, Oracle 12c on Windows is only compatible with Windows Server 2008 and above.  Oracle 9i is not compatible with a Windows OS above Windows 2003.  The way to bridge this issue is to use and intermediate upgrade to Oracle 11.2.0.4.  Oracle 11.2.0.4 is compatible on Windows Server 2003 and Windows Server 2008.
So to perform an in-place upgrade from 9i 32-bit to 12c 64-bit, I had to use the following process:

  • Create a snap copy of the filesystem storage holding the database data files. This copy included the database control files and the database password file.
  • Install Oracle 9.2.0.3 32-bit on a Windows 2003 64-bit VM server, attach the snap copy of the database files to it, use the ORADIM utility to create an Oracle Service for the database and start the database.

At this point I ran into the issue of the AWE (or lack thereof) for the 9i 32-bit memory configuration.  AWE cannot be configured for the Windows 2003 64-bit OS; yet, the 9i 32-bit database required it to be able to start up with the memory that it was configured for.  I ended up having to reduce the memory configuration for the 9i database overall to a value less than 300M and was able to successfully bring the database up.

  • Patch the Oracle 9i 32-bit software to version 9.2.0.8 32-bit.
  • Install Oracle 11.2.0.4 64-bit software on the Windows Server 2003 64-bit.
  • Perform an in-place upgrade from Oracle 9.2.0.8 32-bit to Oracle 11.2.0.4 64-bit.
  • Take a new snap copy of the database datafile storage on the Windows 2003 64-bit server.
  • Install Oracle 11.2.0.4 on a Windows Server 2008 R2 SP1 64-bit VM server, attach the 2nd copy of the database files to it, use the ORADIM utility to create an Oracle Service for the 11g database and start the database.
  • Install Oracle 12.1.0.2 64-bit software on the Windows Server 2008 R2 64-bit.
  • Perform an in-place upgrade from Oracle 11.2.0.4 64-bit to Oracle 12.1.0.2 64-bit.

 

The upgrade from 9.2.0.8 32-bit to 11.2.0.4 64-bit got a little tricky.

DBUA wants to be able to connect to the database to be upgraded as SYSDBA via a TNS connection.  This requires that you create a listener from the new Oracle Home.  However, when I attempted to connect to the 32-bit Oracle 9i database as SYSDBA using a TNS connection string using the 64-bit 11g Listener, I encountered a TNS-12518 error (“TNS: listener could not hand off client connection”).

This was very puzzling.  I could connect to the database as SYSDBA using a local connection from the server.  Yet, I received the TNS-12518 whenever I tried to connect using the TNS connect string from the same server.

After trying several solutions with no success, I finally hit on a solution that worked.  I created a new DBA account in the database and granted the SYSDBA privilege to it.  Then I able to log on to the database as SYSDBA using the new DBA account with the TNS connection string.  SUCCESS!!

Once I got past this issue, I was able to use DBUA to perform an in-place upgrade of the database from 9.2.0.8 32-bit to 11.2.0.4 64-bit.  The DBUA process took about 1 hour to complete.

Once the initial upgrade from 32-bit to 64-bit was successful, the process steps from there forward went off without any more hiccups.

The in-place upgrade process proved to be a very straight forward effort with just a couple of speed bumps to overcome.  Both times that DBUA process was used, it took only 1 hour to complete the upgrade.  Regarding the 9i 32-bit to 11g 64-bit upgrade, the process went very smoothly once the TNS-12518 error was resolved.

The only downside of the in-place upgrade was the fact that the original 9i 32-bit database was created with a 4K block size.  That did not change throughout the upgrade process, so the I ended up with a 12c 64-bit database with a 4K block size, which is not ideal.  The good news is that this effort was only a POC so that the developers could determine if the old application layer software would be compatible with the 12c database.  When the database upgrade happens for real, the 12c database will be created from scratch with the proper configuration and block size setting.  The database data will be migrated using a method that will allow for a near-zero downtime cutover; but, that is a story for another blog…

Hope this helps.