Using DBMS_DST package to upgrade the timezone file version during or pre Oracle 11gR2 Upgrade

If you see a warning stating that your current timezone file version is lower then 11, you need to upgrade it to version 11.

Oracle 11g provides a built-in package DBMS_DST that can be used to evaluate the current timezone data before the timezone file version upgrade and does the upgrade of timezone file version as well.

The DBMS_DST package helps performing these tasks by creating a prepare window and upgrade window.

DBMS_DST prepare window

Use this window to validate the current timezone data that you have.

Show the primary and secondary timezone file version and the upgrade state of the timezone file:

$ sqlplus / as sysdba
SQL> startup
SQL> set serveroutput on
SQL> EXEC DBMS_DST.BEGIN_PREPARE(11);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.

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         4
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

Open upgrade mode window

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.

DBMS_DST upgrade

Now is time to upgrade the database timezone file version to 11. 

SQL> startup upgrade
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
PL/SQL procedure successfully completed.

SQL> shutdown immediate
SQL> startup

SQL> set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
   parallel                  => TRUE,
   log_errors                => TRUE,
   log_errors_table          => 'SYS.DST$ERROR_TABLE',
   log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
   error_on_overlap_time     => TRUE,
   error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
Failures: 0
PL/SQL procedure successfully completed.

SQL> BEGIN
 DBMS_DST.END_UPGRADE(:numfail);
END;
/
PL/SQL procedure successfully completed.

Check for success

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         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

The timezone file version is upgraded.