These days I’m in the progress of patching all databases for a specific client to 18.10 (from 18.9 as we need to be up to date). However, we realized that there are quite a few dev/qa environment that are still 12.2, so it was time to upgrade those as well (we decided to go straight to 18.10 on these environments).
ORA-01722
The process was quite simple. This is Windows and as I wanted to go straight to 18.10 I simply installed 18.3, patched it with the latest BP (18.10 for Windows) and then wanted to run dbua and upgrade the database.
It didn’t take a long time to get an error from dbua, and the catupgrd0.log contained the following error:
22:09:05 SQL> SELECT TO_NUMBER(
22:09:05 2 'MUST_BE_&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR')
22:09:05 3 FROM v$instance
22:09:05 4 WHERE substr(version_full,1,instr(version,'.',1,3)-1) !=
22:09:05 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_18.10.0')
old 5: '&C_ORACLE_HIGH_MAJ..&C_ORACLE_HIGH_RU..&C_ORACLE_HIGH_RUR'
new 5: '18.10.0'
'MUST_BE_18.10.0')
*
ERROR at line 2:
ORA-01722: invalid number
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0
Start of Trace Message
------------------------------------------------------
Unexpected error encountered in catctlMain; Error Stack Below; exiting
Died at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 9041.
at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 9041.
main::catctlDie("\x{a}Unexpected error encountered in catconExec; exiting\x{a} No such"...) called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 5535
main::catctlExecutePhaseFiles(0, 1, undef, undef, undef) called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 2019
main::catctlRunPhase(0, 1, undef, undef, undef) called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 2163
main::catctlRunPhases(0, 109, 109, undef, undef, undef) called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 2787
main::catctlRunMainPhases() called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 1439
main::catctlMain() called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 1348
eval {...} called at C:\app\Administrator\product\18c\db_home\rdbms\admin\catctl.pl line 1346
I checked MOS and Google, and obviously found this post by Mike Dietrich. But this post talks about upgrading to 19.4 or 19.5, but I’m upgrading to 18.10. However, it really looked like the problem is in the same area, so I checked the file (ORACLE_HOME/rdbms/admin/dbms_registry_basic.sql) and this is what I have:
define C_ORACLE_HIGH_VERSIONFULL=18.10.0.0.0
define C_ORACLE_HIGH_MAJ=18
define C_ORACLE_HIGH_RU=10
define C_ORACLE_HIGH_RUR=0
define C_ORACLE_HIGH_INC=0
define C_ORACLE_HIGH_NEXT=0
As you can see, the values are correct, so what’s the problem?
In order to figure our what’s going on I looked at the query. It checks the version from V$INSTANCE against the values from the file, so I executed this query:
SQL> select substr(version_full,1,instr(version,'.',1,3)-1) from v$instance;
SUBSTR(VERSION_FULL,1,INSTR(VERSION,'.',1,3)-1)
--------------------------------------------------------------------
18.10.
A-ha! That’s it! The script cuts part of the version based on the size of the string, and 18.10 is one character longer than 18.9, so it doesn’t take the last digit (configured in C_ORACLE_HIGH_RUR).
The Insane Solution
In order to solve this, I simple left the C_ORACLE_HIGH_RUR parameter empty, so concatenating the variables will be “18.10.”, like this:
define C_ORACLE_HIGH_VERSIONFULL=18.10.0.0.0
define C_ORACLE_HIGH_MAJ=18
define C_ORACLE_HIGH_RU=10
define C_ORACLE_HIGH_RUR=
define C_ORACLE_HIGH_INC=0
define C_ORACLE_HIGH_NEXT=0
I clicked “retry” in dbua, and the upgrade continued.
HOWEVER, towards the end of the upgrade stage I got another error:
Validating logfiles...done
Patch 30901451 apply: WITH ERRORS (RU)
logfile: C:\app\Administrator\cfgtoollogs\sqlpatch\30901451\23453832/30901451_apply_ATERNITY_2020Jul02_22_58_49.log (no errors)
ru_logfile: C:\app\Administrator\cfgtoollogs\sqlpatch\30901451\23453832/30901451_ru_apply_ATERNITY_2020Jul02_22_58_46.log (errors)
-> Error at line 322: script dbmscr.sql
- SP2-0137: DEFINE requires a value following equal sign
I suspected that this is my change so I changed it back and retried, but then got “end of file communication channel” and I couldn’t continue with the upgrade using dbua.
The error looked like it was from datapatch, so I decided to run that manually to see what happens. This is what I got:
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
C:\app\Administrator\product\18c\db_home\cfgtoollogs\sqlpatch\sqlpatch_4328_2020_07_02_23_11_48\sqlpatch_invocation.log
for information on how to resolve the above errors.
At that point I couldn’t trust the upgrade process as it seemed like the database is half upgraded so I had to try and continue manually. I manually started the database in UPGRADE mode used the dbupgrade utility but it got the same error.
My suspicion is that the upgrade checks for the version and fails (because of the ORA-1722), but then the datapatch or something else fails because the variable doesn’t have a value. So I did this crazy thing: I deleted the value from the variable in the dbms_registry_basic.sql, started the dbupgrade utility, and once it was running, I added the value of zero back to the file and saved it. The upgrade completed successfully!
The Real Solution
My solution worked, but it was really crazy, so I also opened an SR. Apparently there is a bug about it, number 30870439 (but it’s unpublished, and there are no notes about this issue).
The real workaround for this issue is edit the ORACLE_HOMErdbms\admin\catupstr.sql file and change the query from:
WHERE substr(version_full,1,instr(version,'.',1,3)-1) !=
to
WHERE substr(version_full,1,instr(version_full,'.',1,3)-1) !=
As the substr takes the “version_full” column but cuts it based on the value in the “version” column and that leads to the inconsistency.
Hi Liron,
just to confirm: Your workaround is correct, and the fix is not included by default in any 18c or 19c RU so far (even though the issue will come up with 19.10.0 in Jan 2021 for the first time.
Cheers,
Mike
Thank …. so much