ORA-1722 when upgrading to 19.4.0 or 19.5.0 on Windows

Oh boy – I try to avoid blogging about errors on a specific platform. But as this topic was brought to my attention now 3x in 2 weeks, I think I’ll send it out. And I hope you won’t hit this ORA-1722 when upgrading to 19.4.0 or 19.5.0 on Windows.

What is failing?

The database upgrade only on the MS Windows platform fails when you attempt to upgrade to 19.4.0 or 19.5.0. This means, you downloaded and installed 19.3.0. And then you followed our advice and applied one of the most recent Release Update – ideally 19.5.0, the October 2019 RU. Once the patch application completed, you take your “old” database or PDB and upgrade it to 19.4.0 or 19.5.0. And *BANG* you will see this error pattern:

22:14:04 SQL> Rem =====================================================================
22:14:04 SQL> Rem The following statement confirms that the script version identified by
22:14:04 SQL> Rem the &C_ORACLE_HIGH_ sqlplus variables matches the
22:14:04 SQL> Rem server version full value from v$instance. The C_ORACLE_HIGH
22:14:04 SQL> Rem sqlplus variables are defined in dbms_registry_basic.sql and contains
22:14:04 SQL> Rem a version value like 18.10.2.0.0. This value will be
22:14:04 SQL> Rem substituted in the query at run time, for example,
22:14:04 SQL> Rem TO_NUMBER('MUST_BE_18.10.2')
22:14:04 SQL> Rem =====================================================================
22:14:04 SQL>
22:14:04 SQL> SELECT TO_NUMBER(
22:14:04   2       'MUST_BE_&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR')
22:14:04   3  FROM v$instance
22:14:04   4  WHERE substr(version_full,1,instr(version,'.',1,3)-1) !=
22:14:04   5            '&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR';
old   2:   'MUST_BE_&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR')
new   2:   'MUST_BE_19.3.0')
old   5:            '&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR'
new   5:            '19.3.0'
  'MUST_BE_19.3.0')
  *
ERROR at line 2:
ORA-01722: invalid number

Now the ORA-1722 is kind of a universal upgrade error. It gets used to indicate one of the first checks failed.

Why is it failing?

It sounds odd but the reason for the failing upgrade is an incorrect entry in $ORACLE_HOME\rdbms\admin\dbms_registry_basic.sql:

define C_ORACLE_HIGH_VERSIONFULL=19.3.0.0.0
define C_ORACLE_HIGH_RU=3

As you can easily see, the values should be either 19.4.0.0.0 and 4 or 19.5.0.0.0 and 5, depending on whether you have applied 19.4.0 or 19.5.0.

What is the workaround?

First of all, this is not only happening with DBUA as the MOS note indicates. This happens with every technique to upgrade your database. And there is a simple (but annoying) workaround available.

Just edit: $ORACLE_HOME\rdbms\admin\dbms_registry_basic.sql

and change the values I marked in RED to match your RU version.

C:\>grep "^define" "C:\oracle\product\19\rdbms\admin\dbms_registry_basic.sql"
define C_ORACLE_HIGH_VERSION_4_DOTS=19.0.0.0.0
define C_ORACLE_HIGH_VERSION_3_DOTS=19.0.0.0
define C_ORACLE_HIGH_VERSION_2_DOTS=19.0.0
define C_ORACLE_HIGH_VERSION_1_DOT=19.0
define C_ORACLE_HIGH_VERSION_0_DOTS=19
define C_ORACLE_HIGH_VERSIONFULL=19.3.0.0.0
define C_ORACLE_HIGH_MAJ=19
define C_ORACLE_HIGH_RU=3
define C_ORACLE_HIGH_RUR=0
define C_ORACLE_HIGH_INC=0
define C_ORACLE_HIGH_NEXT=0
define C_ORACLE_HIGH_STATUS=Production
define C_LTZ_CONTENT_VER=32

Change them to either 4 or 5, depending on which RU you are upgrading to.

When will it be fixed?

I already talked to the developer owning the bug. He confirmed to me that this issue will be fixed with the January 2020 Windows RU (or BP) for Oracle 19c. But as many of you may use the quiet time over new year to patch your databases, you may hit this issue potentially with the July or the October 2019 RU.

Update: Oracle Database 18.10.0 and newer

Thanks to Liron Amitzi who blogged about two related issues with Oracle 18.10.0, the “10” (or “11” etc) can trigger this:

• https://gotodba.com/2020/07/02/ora-01722-during-upgrade-to-18-10/

Liron documented the bug 30870439 (unpublished) and the workarounds

More Information

You can find further information here:

• MOS Note: 2579192.1 – DBUA upgrade to 19.4 in Windows fails with Error:ORA-1722
• Bug 30155576
• Scroll down to the COMMENTS section at:
  https://mikedietrichde.com/2019/07/12/autoupgrade-analyze-fixups-upgrade-and-deploy-modes/
• https://gotodba.com/2020/07/02/ora-01722-during-upgrade-to-18-10/

–Mike