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.
Share this
You May Also Like
These Related Stories
No Comments Yet
Let us know what you think