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

Posted in: Oracle, Technical Track

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.

email

Authors

Interested in working with Rene? Schedule a tech call.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

3 Comments. Leave new

Hi Rene,

did you have an SR for this? If yes, please share the number with me. If “no”, no worries – I verified what you saw. We’ll figure out what’s wrong here. Either the upgrade check is not doing fine, or the TZ patch should have copied it 34 as well.

Mail me if you’d like to follow up.

Cheers,
Mike

Reply

Hi Mike

Hope all is well, just sent you an email

Regards

Reply

Hi Rene,

just fyi – after a lot of discussions we filed:
Bug 32033307 – MISSING TIME ZONE FILES IN TARGET HOME BREAK DATABASE UPGRADE WITH ORA-1722
for it. Let’s see how we can solve this the best way.

Cheers,
Mike

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *