Upgrade Oracle database from 9.2.0.7 to 9.2.0.8

Question

We are planning to upgrade from Oracle 9.2.0.7 to 9.2.0.8. Main reason of the proposed upgrade is to address the issue in relation to exception "terminated with error: ORA-00904: "T2"."SYS_DS_ALIAS_4": invalid identifier" when we try to execute DBMS_STATS.GATHER_SCHEMA_STATS.

We are concerned that the proposed upgrade may have negative impact on our Java application or in the worst case may not even support by our Java application.

What are the possible approaches or strategies that we can take to ensure the upgrade from Oracle 9.2.0.7 to 9.2.0.8 will not have adverse impact on our Java application or will not cause our Java application to function incorrectly. Essentially we just want to confirm that our application will still support Oracle 9.2.0.8.

Thank you.

Answer 1

Your first step should be to ensure you set up a test system with your exact production layout and current software (9.2.0.7).

Run it for a bit to make sure it's okay, then perform the upgrade on your test system and run it for as bit longer to ensure it hasn't broken anything. I'm not talking about the cowboy-developer "if it runs for five minutes, that's okay" type of test. It should be a thorough test of all functionality and performance if possible.

Once you're happy with the level of testing, you can plan to do the same thing to production.

This isn't rocket science, you should always have a production mirror on which you can test upgrades of software, both your own and third-party stuff. And you should have backout strategies on the off-chance that the production upgrade fails anyway despite your testing.

We're pretty paranoid so we actually set up a whole new machine well in advance doing as much as possible. Then, at cutover time, we disable current production, perform whatever transfer is still required to the new machine, then bring that up and test it. If at any point during testing something cannot be fixed in the upgrade window, the old machine is put back online and we try again later, with appropriate kicks in the rear end for those responsible for the failure :-)

Answer 2

I've upvoted Paxdiablo's answer - there are few shortcuts around testing with as much application coverage as possible on a full-size copy of your production system.

I think you're generally looking to answer two questions with an upgrade:

• Have new bugs been introduced in the Oracle functionality used by the application?
• Have changes in the optimizer changed the execution plans (for the worse!) for any application queries?

I believe that as early as 9.2 the optimizer would include system stats in determination of execution plans, so you want to at least bring that information over into the test system to reduce that variable to the optimizer if your test system hardware differs from production.

If you upgrade to Oracle 11g and have the $$$$, you can license and configure Real Application Testing. This will let you essentially record and play back database activity in a test instance to answer these two questions.

Answer 3

In addition to the excellent answers by dpbradley & paxdiablo, before the database is patched it is worth looking on the Oracle support site, support.oracle.com, at the known issues that this patch may introduce which may stop you losing more than you gain!

You will need a valid support license to log into the Oracle support site but there is a note for 9.2.0.8 filed under:

9.2.0.8 Patch Set - Availability and Known Issues [ID 388281.1]