Our largest, most critical systems had been converted from 32 bit to 64 bit at approximately Oracle version 8.0.5. An attempt to upgrade a copy of a development database that had been converted long ago resulted in multiple errors during execution of the catalog upgrade, and a complete failure of the process. Research on Metalink showed many potential bugs related to prior 64 bit conversions.
The Testing Process
Copies of each variant (Dev, Test, Prod) of all Oracle databases were tested for upgrade issues over the course of several months. Multiple copies the databases were tested multiple times using both DBUA and manual execution of scripts. (DBUA was abandoned early in the testing phase. Scripts resulted in a more consistent, trackable process.)
Every upgrade execution of a database that had been converted to 64 bit was impacted by significant bugs. Continued testing of various combinations of patches and workarounds showed the upgrade could be completed, however subsequent patches or upgrades would result in similar issues.
To make this situation even trickier, each database hit a slightly different combination of issues. For example, production SAP and test SAP had the same combination of bugs, yet the development instance manifested a different combination of issues. Since test had been cloned from production multiple times since the 64 bit conversion and development had not, this was explainable. Annoying, but explainable.
There was one big gotcha to the ‘could be completed’. If one of these bugs was hit during the upgrade process, the fix required applying patches or workarounds to the new executables, restoring a copy of the database from prior to the failed upgrade attempt and starting the upgrade over from the beginning. Therefore, it was critical to know exactly which bugs a specific database would encounter before starting the upgrade.
It was not possible to apply all patches for all potential issues, as some of them were not compatible.
Versions tested: 10.2.0.1, 10.2.0.2, 10.2.0.3
Bugs Hit: 3090963, 4860003, 5148850, 5255632, 5748280, 5755471
In order to complete the upgrade, various combinations corrections were needed: Patches, data dictionary script edits, remove XDB install from data dictionary build and odd additions to the process - like exiting sqlplus after every command.
All of that being said, this is what matters most:
Every database that had been converted to 64 bit during it’s life cycle was impacted by a bug that was not easily corrected.
Testing the patch on a test instance did not guarantee that you knew what to expect in production. The only way to know for sure which bugs would manifest in a database was to upgrade a copy of THAT specific database.
The patches and workarounds allowed the upgrade process to complete successfully, but left the database vulnerable to further issues on the next upgrade or patch set.
Just in case you glazed over the last bit, let me rephrase it:
Even if you get through an upgrade to 10.2.0.2, you will hit similar issues when you apply the 10.2.0.3 patch set. The patches may get you through the upgrade process, however they do not fix the underlying issue.
So what does this mean for those of you who are preparing to upgrade?
If your database is currently 64 bit, the first step in planning your upgrade should be determining if your database was converted from 32 bit.
If the database is currently 64 bit, execute this query:
select metadata from sys.kopm$ ;
If the database was created as 32 bit, you will see a B023 (shown in red) in your results and you should plan for multiple rounds of testing in your upgrade process. You may want to reconsider how you will migrate your database to the new version.
If the database was created as 64 bit instance, you will see a ‘B047’ in your results and you don’t have to worry about these bugs.
We decided our best option was to create a clean instance for any database that had been converted to 64 bit in it’s lifetime and migrate the data. Due to various application upgrade requirements, much of our data needed to be reloaded due to application upgrades and character set conversions, so this was a window of opportunity to make the change with minimal additional pain.
In particular, the SAP application upgrade requires the creation of a new ‘shadow’ instance with the new data structures, and the data is migrated from the old instance to the new instance. So, at the end of the upgrade process, the result is a shiny, new instance and no potential for future 64 bit conversion bugs.
Note: The SAP data migration from the old to the new requires both instances to be on the same version, so the 10g upgrade has to complete successfully. The upside is that the upgraded database only needs to survive long enough to get the data out.
An additional option proposed by Oracle support was to upgrade the database, create a new instance and use transportable tablespaces to migrate the data to a new instance. However, I did not test this option thoroughly as we had already decided on a different path.
As stated at the beginning of this post, these details were recorded last summer. Status of these bugs and the available patches has most likely changed - changes were occurring regularly as I worked on it. This post simply recommends that you run the query above, research any potential issues thoroughly and measure the risks in your specific case.