LATEST TOPICS

catupgrd.sql and ORA-01722: invalid number

Recently while upgrading one of the database from Oracle version 11.2.0.3 to 11.2.0.4, the catupgrd.sql script failed with following errors

DOC>#######################################################################
DOC>#######################################################################
DOC>   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.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
                  *
ERROR at line 1:
ORA-01722: invalid number

As per the error report, it seems to be an issue with time zone file version mismatch between 11.2.0.3 (Old ORACLE_HOME) and 11.2.0.4 (new ORACLE_HOME) and Oracle is suggesting to patch the new ORACLE_HOME (11.2.0.4) to the same time zone file version that is used by old ORACLE_HOME (11.2.0.3). I knew that the old ORACLE_HOME was using a time zone file version 17, so I decided to look in to the time zone file version for the new ORACLE_HOME.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

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

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

Even though, we have the same time zone version (17) in new and old ORACLE_HOME. Oracle is still complaining about the time zone mismatch during the upgrade process. Somehow Oracle is not able to determine the time zone version from the new ORACLE_HOME home, which is resulting in to the mismatch.

Let’s query the V$TIMEZONE_FILE view in new ORACLE_HOME to check if we can find out the

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select * from V_$TIMEZONE_FILE;

no rows selected

Here the problem is. Even though we have the same time zone file version in new and old ORACLE_HOME, Oracle is not able to locate the time zone file for that version under the new ORACLE_HOME. Let’s check if the time zone file is present under the new ORACLE_HOME.

oracle@labserver1:~> cd $ORACLE_HOME/oracore/zoneinfo
oracle@labserver1:/app/oracle/product/11.2.0.4/oracore/zoneinfo> ls -lrt *_17.dat
ls: cannot access *_17.dat: No such file or directory

The time zone files for version 17 is missing from the new ORACLE_HOME (it is not shipped by default with Oracle 11.2.0.4. The latest version shipped with Oracle 11.2 is version 14 of time zone file), which is why a query against V$TIMEZONE_FILE is not returning any information. We need to make sure that the time zone files are present under new ORACLE_HOME for the upgrade to be successful. We can simply copy the relevant time zone file from old ORACLE_HOME to new ORACLE_HOME as shown below

oracle@labserver1:~> echo $ORACLE_HOME
/app/oracle/product/11.2.0.4/

oracle@labserver1:~> cp /app/oracle/product/11.2.0.3/oracore/zoneinfo/*_17.dat $ORACLE_HOME/oracore/zoneinfo

oracle@labserver1:~> find $ORACLE_HOME -name *_17.dat -print 2>/dev/null
/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezone_17.dat
/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezlrg_17.dat

Let’s check if Oracle is able to locate the time zone file.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select * from V_$TIMEZONE_FILE;

FILENAME                VERSION
-------------------- ----------
timezlrg_17.dat              17

Yes, Oracle is now able to determine the time zone information from new ORACLE_HOME. Now, we can re-initiate the catupgrd.sql script to complete database upgrade.

Footnote: To avoid timezone issues during database upgrade, make sure that the time zone files relevant to the current time zone version are present under the new ORACLE_HOME. If the time zone files are missing (not shipped with the specific Oracle binaries) under new ORACLE_HOME, you can simply copy them over from the existing (old) ORACLE_HOME. Don’t miss to take a note of the time zone version (from old ORACLE_HOME) before starting the database upgrade and make sure the time zone version matches in the new ORACLE_HOME before you initiate the catupgrd.sql script from the new home. If the time zone version is different in the new ORACLE_HOME, follow the Oracle documented process to upgrade the time zone version before initiating catupgrd.sql

2 Comments
  1. Khalid Rahim
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed