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:
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
Mike,
The bigger question here is: how does such code get released? Is any kind of testing done?
Thanks,
Arun
Hi Arun,
that’s very simple:
The RU has been applied (this works perfectly well), regression tests have been done (a complex suite) but obviously in the Windows case, no upgrade tests done (I guess).
Cheers,
MIke
Hi Mike,
just for info about 19.5. I noticed that after applying Windows Database Bundle Patch 19.5.0.0.191015 one file is in the bad location:
file mgr_help.jar is here:
“C:\oracle2\product\19c\db2\network\tools\help\mgr\help\mgr_help.jar\mgr_help.jar”
but should be here:
“C:\oracle2\product\19c\db1\network\tools\help\mgr\help\mgr_help.jar”
Due to this bad path next Windows Database Bundle Patch 19.6.0.0.200114 could not be installed.
Best regards
Matěj.
Hi Matej,
thanks 🙁
And again another flaw in the windows bundle patch – very bad 🙁
Thanks for sharing!
Cheers,
Mike
Hi Mike,
just for info, the issue is described in Doc ID 2628965.1.
Best regards
Matěj.
Thanks a lot, Matěj!
Mike
Hi Mike, this issue has indeed been fixed in the jan 2020 bp (19.6.0.0.).
In return, we now get the information that a file is missing in rdbms/admin/backport_files after dbupgrade has completed. The file is called bug_29766207_apply.sql and is indeed missing – however, MOS research shows that the underlying bug has been superseded by another bug and that THAT one is fixed in 19.6.0.0 (jan 2020 bp), which is exactly the one installed on the system we are upgrading on. So I chose to ignore this error – but it would be nice if the check for that script was removed from the dbupgrade routine.
Best regards,
Susanne
Hi Susanne,
it’s actually not the upgrade process but the patch process which does the noise here.
I found an – right now unpublished – note about this recommending the following w/a:
There is no solution as of 19.6DBRU . This will be fixed in 19.7DBRU
You can apply the workaround as below :
The file is a sql file and is same in Windows and Unix servers .
If you have a Linux 64 bit server with Oracle RDBMS 19c installed and 19.6DBRU applied , you can copy the file bug_29766207_apply.sql from Linux server to the required destination on the Windows server and perform the upgrade again
If customer does not have linux box , then you can provide the file to the customer via SR . The file is attached to this doc .
I mailed you the file contents.
Thanks – and sorry for the inconvenience … 🙁 I’m speechless, too!
Cheers, and Happy Easter!
Mike
Susanne,
I have news:
The Windows BP 19.6.0.0.200114 (Patch 30445947) has been superseded by the Windows BP 19.7.0.0.200414 (Patch 30901317). For Patch 30901317 availability, see section 2.2 of Note 2633852.1.
Just fyi – cheers,
Mike
Hi Mike,
thanks for the update – I have seen it, too! We will see what lies in store for us within that one 😊!
Cheers,
Susanne
But alas – it is only the link which is available, but not the patch… click on link opens a page saying: Details for Patch 30901317 not found. *sigh*
Hi Susanne,
I see this as well 🙁
🙁 🙁 🙁
Mike
Mike or Susanne: lack of bug_29766207_apply.sql is also botching my upgrades. I don’t have a Linux box to pull the file from. Could I please bother one of you to e-mail it to me? I’m sorry to ask but it would be very helpful. Thank you.
Mike,
I can’t unfortunately – only Support can 🙁
Cheers,
Mike
Thanks and makes sense. Plus: Support should hear from customers on this issue. I opened and SR and obtained the file. Sorry for pestering you to do that. You do all the important work of keeping us informed and for that I’m very thankful. Thanks again for this information.
I have opened an SR because the April BP is still not out. MOS told me ETA was now April, 28th and that the January BP should not ne used anymore. This one has also been removed from the Download Page, so I suggest to seit until Tuesday and hope the forecast is true 😳 – that is what I will do, too.
Cheers,
Susanne
Thanks and makes sense. Plus: Support should hear from customers on this issue.