Possibly random thoughts of a oddly organized dba with a very short attention span

4.16.2008

Upgrades, conversions and ora-600's! oh my!

Last summer, I worked extensively on a project to upgrade all Oracle 9.2.0.8 instances to Oracle 10g. The initial test upgrade on a copy of a development instance was impacted by multiple bugs, some of which had no documented solution at that time. Recently several friends have experienced similar problems and the issue was mentioned on Oracle-L, so I thought I’d post a little history.

The Problem
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.)

The Results
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.

Ouch ...

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.

METADATA
--------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D06090708050F0505050F0505
0505050A050505050504050607080823472323081123081141B023008300
0107D0130000000000000000000000000000000000000000000000000
0000000000000000000000000

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.

METADATA
--------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F0505
0505050A050505050504050607080823472347081123081141B047008300
2707D0130000000000000000000000000000000000000000000000000
0000000000000000000000000


Our Solution

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.

5 comments:

Anonymous said...

Robyn, thanks for the "report". For those of you who are preparing for a conversion from 32-bit to 64-bit wordsize make sure you read Metalink note 412271.1. The minimum patch level to migrate to without hiting the 32->64bit conversion bug is 10.2.0.3 + Patch 5 or higher (with bare 10.2.0.3 release as a target you'll likely hit the bug).
Ales

Robyn said...

Hi Ales,

Thank you for the update and the latest on the metalink note.

In our case, SAP required the database to be at version 10.2.0.2 so we needed to get through an upgrade to that version. It was accomplished but it took a great deal of research and testing to get there.

I tested the databases that didn't have a vendor requirement for 10.2.0.2 with 10.2.0.3. I hit 2 bugs for that version, 5748280 and 5755471, but patches were eventually made available to us for both issues, and overall the process was much smoother with 10.2.0.3.

Robyn

Allen said...

Robyn - How timely! Not sure about you, but I think God directs us to do certain things in life in order to save us grief later. I happened to be surfing Cary's blog and saw the link to yours. I've been looking at your older posts and this one may have saved me some serious headaches in the coming months as we move to 64-bit.

- Allen

Robyn said...

Allen,

Depending on your upgrade path and the approach you take, you're much less likely to experience the headaches I did. In our case, the conversion to 64 bit had been done several years earlier on an early version of Oracle 8, and most of the pain came from inconsistencies in our instances.

Which version(s) will you be working with? Have you decided how you plan to migrate to 64 bit?

Allen said...

Kind of late responding to your reply, but hadn't checked back until now. We've got 2 databases at 10.2.0.2 on RHEL4 32 bit and one on Solaris 8 64 bit that I need to move to RHEL5, 64 bit. I'm concerned about the Solaris one since I migrated that from RHEL4, 32 bit (yeah, gotta love the changing of minds; I wanted to go with RHEL to begin with on the Solaris on). I'll probably patch to 10.2.0.4 on the RHEL4, 32 bit boxes, then upgrade to 11g, Release 2, converting to 64 bit in the process. They will be on new servers for all three, so I'll probably do a TTS to move the tablespaces for the RHEL4, 32-bit to RHEL5, 64 bit migrations and a TTS and conversion for the Solaris 8, 64-bit to RHEL5, 64 bit conversion.

Search This Blog

Loading...

My Blog List

disclaimer ...

The views expressed on this page are mine and do not reflect the opinions of my employer, former employers, other associates or the opinions I may hold 3 days from now.