DbaKeeda: September 2018

Recently we encountered this issue while upgrading from 11.2.0.3 to 11.2.0.4.

ISSUE

catupgrd.sql script was failing with the below error:

DOC> run in the old oracle home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original ORACLE_HOME and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
--
The following error is generated if (1) the old release uses a time
DOC> zone file version newer than the one shipped with the new oracle
DOC> release and (2) the new oracle home has not been patched yet:
DOC>
DOC> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Patch new ORACLE_HOME to the same time zone file version as used
DOC> in the old ORACLE_HOME.

SOLUTION

Get the output of the below queries from 11.2.0.3 and 11.2.0.4 homes:

1) SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

2) SELECT version FROM v$timezone_file;

3) ls -ltr $ORACLE_HOME/ORACORE/ZONEINFO

OUTPUT:

on 11.2.0.3
========

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

2. SELECT version FROM v$timezone_file;

SYS@host1> SELECT version FROM v$timezone_file;

VERSION
----------
18

3. @host1> cd /u01/sq/ora_1/db/11.2.0.3/oracore/zoneinfo/
@host1> ls -tlr
total 14568
-rw-r--r-- 1 oracle oinstall 6068 Jun 7 2010 readme.txt
-rw-r--r-- 1 oracle oinstall 31525 Jun 7 2010 timezdif.csv
-rw-r--r-- 1 oracle oinstall 351525 Aug 18 2011 timezone_9.dat
-rw-r--r-- 1 oracle oinstall 302100 Aug 18 2011 timezone_8.dat
-rw-r--r-- 1 oracle oinstall 286815 Aug 18 2011 timezone_7.dat
-rw-r--r-- 1 oracle oinstall 286217 Aug 18 2011 timezone_6.dat
-rw-r--r-- 1 oracle oinstall 286310 Aug 18 2011 timezone_5.dat
-rw-r--r-- 1 oracle oinstall 286264 Aug 18 2011 timezone_4.dat
-rw-r--r-- 1 oracle oinstall 286651 Aug 18 2011 timezone_3.dat
-rw-r--r-- 1 oracle oinstall 274900 Aug 18 2011 timezone_2.dat
-rw-r--r-- 1 oracle oinstall 274427 Aug 18 2011 timezone_1.dat
-rw-r--r-- 1 oracle oinstall 344448 Aug 18 2011 timezone_14.dat
-rw-r--r-- 1 oracle oinstall 344425 Aug 18 2011 timezone_13.dat
-rw-r--r-- 1 oracle oinstall 345024 Aug 18 2011 timezone_12.dat
-rw-r--r-- 1 oracle oinstall 345356 Aug 18 2011 timezone_11.dat
-rw-r--r-- 1 oracle oinstall 345637 Aug 18 2011 timezone_10.dat
-rw-r--r-- 1 oracle oinstall 801410 Aug 18 2011 timezlrg_9.dat
-rw-r--r-- 1 oracle oinstall 616723 Aug 18 2011 timezlrg_8.dat
-rw-r--r-- 1 oracle oinstall 601242 Aug 18 2011 timezlrg_7.dat
-rw-r--r-- 1 oracle oinstall 586750 Aug 18 2011 timezlrg_6.dat
-rw-r--r-- 1 oracle oinstall 587487 Aug 18 2011 timezlrg_5.dat
-rw-r--r-- 1 oracle oinstall 531137 Aug 18 2011 timezlrg_4.dat
-rw-r--r-- 1 oracle oinstall 527717 Aug 18 2011 timezlrg_3.dat
-rw-r--r-- 1 oracle oinstall 507957 Aug 18 2011 timezlrg_2.dat
-rw-r--r-- 1 oracle oinstall 493675 Aug 18 2011 timezlrg_1.dat
-rw-r--r-- 1 oracle oinstall 791430 Aug 18 2011 timezlrg_14.dat
-rw-r--r-- 1 oracle oinstall 782475 Aug 18 2011 timezlrg_13.dat
-rw-r--r-- 1 oracle oinstall 785621 Aug 18 2011 timezlrg_12.dat
-rw-r--r-- 1 oracle oinstall 787272 Aug 18 2011 timezlrg_11.dat
-rw-r--r-- 1 oracle oinstall 792894 Aug 18 2011 timezlrg_10.dat
-rwxr-xr-x 1 oracle oinstall 781669 Jun 11 2012 timezlrg_18.dat >>>>>>>>>>>>>>>>>
-rwxr-xr-x 1 oracle oinstall 341718 Jun 11 2012 timezone_18.dat >>>>>>>>>>>>>
-rwxr-xr-x 1 oracle oinstall 7150 Jun 11 2012 readme_18.txt
drwxr-xr-x 2 oracle oinstall 4096 May 6 2013 big
drwxr-xr-x 2 oracle oinstall 4096 May 6 2013 little

on 11.2.0.4
========

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

2. SQL> SELECT version FROM v$timezone_file;

no rows selected

3. @host1> cd /u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/
@host1> ls -tlr
total 14568
-rw-r--r-- 1 oracle oinstall 6068 Jun 7 2010 readme.txt
-rw-r--r-- 1 oracle oinstall 31525 Jun 7 2010 timezdif.csv
-rw-r--r-- 1 oracle oinstall 351525 Jul 16 2013 timezone_9.dat
-rw-r--r-- 1 oracle oinstall 302100 Jul 16 2013 timezone_8.dat
-rw-r--r-- 1 oracle oinstall 286815 Jul 16 2013 timezone_7.dat
-rw-r--r-- 1 oracle oinstall 286217 Jul 16 2013 timezone_6.dat
-rw-r--r-- 1 oracle oinstall 286310 Jul 16 2013 timezone_5.dat
-rw-r--r-- 1 oracle oinstall 286264 Jul 16 2013 timezone_4.dat
-rw-r--r-- 1 oracle oinstall 286651 Jul 16 2013 timezone_3.dat
-rw-r--r-- 1 oracle oinstall 274900 Jul 16 2013 timezone_2.dat
-rw-r--r-- 1 oracle oinstall 274427 Jul 16 2013 timezone_1.dat
-rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 timezone_14.dat
-rw-r--r-- 1 oracle oinstall 344425 Jul 16 2013 timezone_13.dat
-rw-r--r-- 1 oracle oinstall 345024 Jul 16 2013 timezone_12.dat
-rw-r--r-- 1 oracle oinstall 345356 Jul 16 2013 timezone_11.dat
-rw-r--r-- 1 oracle oinstall 345637 Jul 16 2013 timezone_10.dat
-rw-r--r-- 1 oracle oinstall 801410 Jul 16 2013 timezlrg_9.dat
-rw-r--r-- 1 oracle oinstall 616723 Jul 16 2013 timezlrg_8.dat
-rw-r--r-- 1 oracle oinstall 601242 Jul 16 2013 timezlrg_7.dat
-rw-r--r-- 1 oracle oinstall 586750 Jul 16 2013 timezlrg_6.dat
-rw-r--r-- 1 oracle oinstall 587487 Jul 16 2013 timezlrg_5.dat
-rw-r--r-- 1 oracle oinstall 531137 Jul 16 2013 timezlrg_4.dat
-rw-r--r-- 1 oracle oinstall 527717 Jul 16 2013 timezlrg_3.dat
-rw-r--r-- 1 oracle oinstall 507957 Jul 16 2013 timezlrg_2.dat
-rw-r--r-- 1 oracle oinstall 493675 Jul 16 2013 timezlrg_1.dat
-rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 timezlrg_14.dat
-rw-r--r-- 1 oracle oinstall 782475 Jul 16 2013 timezlrg_13.dat
-rw-r--r-- 1 oracle oinstall 785621 Jul 16 2013 timezlrg_12.dat
-rw-r--r-- 1 oracle oinstall 787272 Jul 16 2013 timezlrg_11.dat
-rw-r--r-- 1 oracle oinstall 792894 Jul 16 2013 timezlrg_10.dat
-rw-r--r-- 1 oracle oinstall 341718 Sep 18 2013 timezone_17.dat
-rw-r--r-- 1 oracle oinstall 779003 Sep 18 2013 timezlrg_17.dat
-rw-r--r-- 1 oracle oinstall 6905 Aug 31 22:38 readme_17.txt
drwxr-xr-x 2 oracle oinstall 4096 Aug 31 23:04 big
drwxr-xr-x 2 oracle oinstall 4096 Aug 31 23:04 little
@host1>

from the above output we can see that the result for 2nd query on 11.2.0.4 is empty(NO ROW SELECED) because of 18 version TZ files does not exist at new 11.2.0.4 home.

Hence to resolve this issue we copied the TZ files from 11.2.0.3 home to new 11.2.0.4 home.
Make sure to copy the 18 version of files from 11.2.0.3 to 11.2.0.4 homes on below locations:

/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/
/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/big
/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/little

And we ran the catupgrd.sql script again and it was successful.

==========================*****========================================

DbaKeeda

Translate into your own language

Saturday, September 15, 2018

ORA-01722: invalid number while running catupgrd.sql during database upgrade