Overcoming ORA-01722 Error While Doing an Upgrade With Different DST Versions

This will be a short entry, but hopefully, it will help if you face the error "ORA-01722: invalid number" while doing an upgrade.

Today I was doing an Oracle RDBMS upgrade from 12.2 with DST 34, to 19.7 with DST 35. The upgrade failed with the error below:

Processing config file ...
 +--------------------------------+
 | Starting AutoUpgrade execution |
 +--------------------------------+
 1 databases will be processed
 
 -------------------------------------------------
 Errors in database [TEST]
 Stage [DBUPGRADE]
 Operation [STOPPED]
 Status [ERROR]
 Info [
 Error: UPG-1400
 UPGRADE FAILED [TEST]
 Cause: This indicates that the database upgrade failed with errors.
 For further details, see the log file located at /u01_diag/working/pythian/upgrade/upg_logs/TEST/102/autoupgrade_20200921_user.log]
 
 -------------------------------------------------
 Logs: [/u01_diag/working/pythian/upgrade/upg_logs/TEST/102/autoupgrade_20200921_user.log]
 -------------------------------------------------Job 102 completed
 ------------------- Final Summary --------------------
 Number of databases [ 1 ]
 
 Jobs finished successfully [0]
 Jobs failed [1]
 Jobs pending [0]
 -------------------- JOBS FAILED ---------------------
 Job 102 for TEST
 
 Exiting
 
 
 -- From the Upgrade Log
 
 
 17:12:53 SQL> Rem Check if time zone file version used by the database exists in new home
 17:12:53 SQL> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
 17:12:53 2 FROM sys.props$
 17:12:53 3 WHERE
 17:12:53 4 (
 17:12:53 5 (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > &C_LTZ_CONTENT_VER)
 17:12:53 6 AND
 17:12:53 7 (0 = (select count(*) from v$timezone_file))
 17:12:53 8 );
 old 5: (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > &C_LTZ_CONTENT_VER)
 new 5: (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > 32)
 SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
  *
 ERROR at line 1:
 ORA-01722: invalid number

While searching for the error, the first thing that came into my mind is that the 19.7 was patched incorrectly, but that wasn't the case. As you can see, the 19.7 was patched with a higher version of the DST patch than the 12.2 OH

([SID:TEST][oracle@hostname:/u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch ] )
 oracle $ ./opatch lspatches
 29997937;RDBMS - DSTV34 UPDATE - TZDATA2019B
 30133625;OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)
 30138470;Database Oct 2019 Release Update : 12.2.0.1.191015 (30138470)
 30122814;OCW OCT 2019 RELEASE UPDATE 12.2.0.1.191015 (30122814)
 
 OPatch succeeded.
 
 ([SID:TEST][oracle@hostname:/u01/app/oracle/product/19.7.0.0/dbhome_1/OPatch ] )
 oracle $ ./opatch lspatches
 30565805;RDBMS 19C REGRESSION ORA-01843 NOT A VALID MONTH ERROR WHEN EXECUTING TO_DATE(20191120,RRMMDD)
 31335037;RDBMS - DSTV35 UPDATE - TZDATA2020A
 30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
 30869156;Database Release Update : 19.7.0.0.200414 (30869156)
 
 OPatch succeeded.

My first move was to search for the following files in the 19.7 OH:

• timezlrg_34.dat
• timezone_34.dat

They weren't there, and because this is a cumulative patch, DST 34 files shouldn't be missing from the 19.7 OH patched with DST 35. Accordingly, I would classify this as a bug in the DST 35 patch.

([SID:TEST][oracle@hostname:home/oracle/working/antunez ] )
 oracle $ cd $ORACLE_HOME/oracore/zoneinfo
 
 ([SID:TEST][oracle@hostname:/u01/app/oracle/product/19.7.0.0/dbhome_1/oracore/zoneinfo ] )
 oracle $ ls -ltr | egrep "timezlrg_34.dat|timezone_34.dat" | wc -l
 0

I fixed this by copying the files from the 12.2 $ORACLE_HOME/oracore/zoneinfo to the 19.7 $ORACLE_HOME/oracore/zoneinfo

([SID:TEST][oracle@hostname:/u01/app/oracle/product/19.7.0.0/dbhome_1/oracore/zoneinfo ] )
 oracle $ cp /u01/app/oracle/product/12.2.0.1/dbhome_1/oracore/zoneinfo/timezone_34.dat ./
 
 ([SID:TEST][oracle@hostname:/u01/app/oracle/product/19.7.0.0/dbhome_1/oracore/zoneinfo ] )
 oracle $ cp /u01/app/oracle/product/12.2.0.1/dbhome_1/oracore/zoneinfo/timezlrg_34.dat ./
 
 ([SID:TEST][oracle@hostname:/u01/app/oracle/product/19.7.0.0/dbhome_1/oracore/zoneinfo ] )
 oracle $ ls -ltr | egrep "timezlrg_34.dat|timezone_34.dat" | wc -l
 2

Once I did this, I relaunched my upgrade. This time, it finished successfully

([SID:TEST][oracle@hostname:/home/oracle/working/antunez/TEST ] )
 oracle $ $ORACLE_HOME19c/jdk/bin/java -jar $ORACLE_HOME19c/rdbms/admin/autoupgrade.jar -config TEST_config.cfg -mode DEPLOY -noconsole
 AutoUpgrade tool launched with default options
 Processing config file ...
 +--------------------------------+
 | Starting AutoUpgrade execution |
 +--------------------------------+
 1 databases will be processed
 
 Job 102 completed
 ------------------- Final Summary --------------------
 Number of databases [ 1 ]
 
 Jobs finished successfully [1]
 Jobs failed [0]
 Jobs pending [0]
 ------------- JOBS FINISHED SUCCESSFULLY -------------
 Job 102 for TEST
 
 ---- Drop GRP at your convenience once you consider it is no longer needed ----
 Drop GRP from TEST: drop restore point AUTOUPGRADE_221145114461854_TEST

Hopefully, this small post will help you if you are faced with this error, as it seems the DST 35 patch is not including the necessary DST 34 files.

Note: This was originally posted on rene-ace.com.