We discussed an interesting upgrade case last week together with our upgrade colleagues in Support – and learned the double-hop (or triple-hop) upgrade case is not described in the documentation somewhere.
What is a multiple-hop upgrade?
Triple Jump – Willie Banks – Olympics 1988 Seoul
Actually this describes the case where somebody can’t upgrade directly and has to do several upgrades in a sequence to reach the targeted release. For example, you start of with an Oracle 10.2.0.3 database – and your targeted release is Oracle 12.2.0.1 . A direct upgrade is not possible. I would opt now for Data Pump or TTS as you could jump directly. But lets assume Data Pump would take too long because your database is too big – and TTS would require too many steps and cause you more work than sequential upgrades.
Therefore you’d move from 10.2.0.3 to 11.2.0.4 directly. And then further in a second upgrade step to 12.2.0.1. Upgrades from 10.2.0.3 to 11.2.0.4 were supported as they are from 11.2.0.4 to 12.2.0.1. I’d call this a double-hop upgrade.
We have even seen cases with triple-hops needed, especially when one is starting off with an early Oracle 9.2 database. And the complication often is caused by the OS certification as such techniques often involve also a move of the database to a temporary server.
What has happened in this particular customer case?
In the case we’ve discussed last week internally the customer wanted to step from Oracle 10.2.0.5 via Oracle 11.2.0.4 to Oracle 12.1.0.2. Please don’t ask me why they did this intermediate (and unnecessary) step into 11.2.0.4 as direct upgrades from 10.2.0.5 to 12.1.0.2 are supported. But they ended with a situation where the catctl.pl tool raised an ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’) error and quit the upgrade from Oracle 11.2.0.4 to Oracle 12.1.0.2 before it even started. At this stage, there’s no way back into 10.2.0.5 as COMPATIBLE had been changed – and there’s no step forward possible as we don’t allow you to upgrade because of invalid table data which needed to be cleaned up before approaching the upgrade.
Only solution: restore your 10.2.0.5 backup and start off again.
Solution: Run the matching preupgrade scripts per step
The correct path would be to run the matching preupgrade scripts for each step you’d approach. This is not documented and my colleague Eric filed a doc bug for it so we’ll get it documented soon. As in both scenarios above the steps have to be seen as independent upgrades. For the upgrade from Oracle 10.2.0.5 to Oracle 11.2.0.4 the utlu112i.sql script had to be run prior to the upgrade. And once this upgrade is completed, preuprd.sql need to be run as the second step is an upgrade from Oracle 11.2.0.4 to Oracle 12.1.0.2.
Please note:
This is only necessary if you do multiple hops. If the customer here would have gone directly from Oracle 10.2.0.5 to Oracle 12.1.0.2 (which is supported) then ONLY the preupgrd.sql would have to be executed prior to the upgrade.
Always download the most recent preupgrade scripts
In any case, please remember to always download the most recent preupgrade script(s) from MOS Note:884522.1, regardless of upgrade to Oracle 11.2, 12.1 or 12.2 – and please use always the matching script.
MOS Note:884522.1 – Always get the newest preupgrade script
Simple reason why we ask you to download it:
We have included a lot of enhancements, fixes and improvements over the version you get with the base release or the patch set. And as the scripts are not part of PSUs and BPs at the moment (we are working on it) you should get the most recent one to benefit from all the changes we’ve done.
–Mike
