Liron Amitzi reported this on his blog a few days ago already. And it is a very nasty tiny little issue happening with RUs where the second number of the release enters the two-digit-space, such as 18.10.0. At this point, on Windows Only: Upgrade to Oracle 18.10.0 fails with ORA-01722. This happens late in the upgrade phase.
Photo by Twitter: @jankolario on Unsplash
What is the issue?
The problem here is a tiny little piece in %ORACLE_HOME\admin\catupstr.sql – a SUBSTR function is used in a way that it does not take into account a potential two-digit patch release. This was not caught as in the old days there was never a Oracle 11.2.0.10.0 release.
This problem happens on Windows only.
But when you run a database upgrade, regardless if you use AutoUpgrade, dbupgrade, catctl.pl or dbua, the upgrade will fail at the very beginning as catupstr.sql runs several checks before invoking the upgrade. Nothing is damaged at this point as long as you left COMPATIBLE untouched.
What is the workaround?
The workaround is a simple tiny edit you can do by yourself quickly.
In the target 18.10.0 (or higher) home, open %ORACLE_HOME\admin\catupstr.sql for edit and change the following part:
SELECT TO_NUMBER(
'MUST_BE_&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR')
FROM v$instance
WHERE substr(version_full,1,instr(version,'.',1,3)-1) !=
'&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR';
to:
SELECT TO_NUMBER(
'MUST_BE_&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR')
FROM v$instance
WHERE substr(version_full,1,instr(version_full,'.',1,3)-1) !=
'&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR';
You don’t spot the difference at first sight?
No worries – just substitute
“substr(version_full,1,'.',1,3)-1)” with
“substr(version_full,1,instr(version_full,'.',1,3)-1)“.
That’s it.
As there are no one-off’s on Windows, it doesn’t help to request a fix for bug 30870439. You will have to wait until the fix gets delivered with (I guess) 18.12.0 at the earliest opportunity.
When does it happen?
It happens with an upgrade to Oracle 18.10.0, Oracle 18.11.0, and most likely for Oracle 18.12.0 as well – but only on Windows.
When you check the Oracle 19c ?/rdbms/admin/catupstr.sql in your Linux environment, you will recognize that the fix isn’t present there either. But as RU 19.10.0 will arrive in January 2021, we will make sure the fix is present then.
So again, this problem does not happen with Oracle 18c on Unix ports but only on Windows right now.
Further Links and Information
- MOS Note: 2686934.1 – Database upgrade to 18.10 or higher fails with ORA-01722: invalid number MUST_BE_&C_ORACLE_HIGH_MAJ
- ORA-1722 during upgrade – Liron Amitzi’s Blog Post
–Mike
