Upgrade Timezone version 10 to 18 in oracle

Upgrading timezone_file version manually during Oracle 12c upgrade

Some of us might have faced a situation to upgrade timezone_file version manually during Oracle 12c upgrade. Here are the steps to follow in Windows.

When you upgrade Oracle to 121020, you will have have an option in DBUA to upgrade the timezone data.

If you don’t check it, then, when you run “post_upgrade_checks.sql”, it will give you the below warning message.

**********************************************************************

Check Tag:     OLD_TIME_ZONES_EXIST

Check Summary: Check for use of older timezone data file

Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.

**********************************************************************

Fixup Returned Information:

INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.

     After the upgrade, it is recommended that DBMS_DST package

     be used to upgrade the 12.1.0.2.0 database time zone version

     to the latest version which comes with the new release.

     Please refer to My Oracle Support note number 977512.1 for details.

**********************************************************************

upgrade it manually

Upgrade Time zone version 10 to 18.

SQL> SELECT version FROM v$timezone_file;

  

  VERSION

----------

        10

SQL> 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                                             10

DST_SECONDARY_TT_VERSION                                        0

DST_UPGRADE_STATE                                                       NONE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  2926472 bytes

Variable Size             889194616 bytes

Database Buffers         1241513984 bytes

Redo Buffers               13848576 bytes

Database mounted.

Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (18);

PL/SQL procedure successfully completed.

SQL> shut immediate

SQL> startup

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        18