Upgrade DST timezone version from 4 to 14 after upgrading the db from 10.2.0.4 to 11.2.0.3

After upgrading the db from 10.2.0.4 to 11.2.0.3 find the timezone DST TZ version using the command,

SQL> SELECT version FROM v$timezone_file;


   VERSION
----------
         4

this value is less than 14, So we have to upgrade to 14.

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         4

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

check for the timezone .dat files 

/opt/oracle/product/11.2.0.3/dbs > ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*

-rw-r--r--   1 oracle   dba         336K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezone_14.dat

-rw-r--r--   1 oracle   dba         773K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezlrg_14.dat

/opt/oracle/product/11.2.0.3/dbs >

Connect as SYS user and prepare for the upgrade.

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
PL/SQL procedure successfully completed


SQL> 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       14
DST_UPGRADE_STATE              PREPARE

Now you will see 14 as secondary timezone.

Truncate all the DST affected tables:

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.



Run the below PL/SQL code to log errors for DST affected tables.

SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Following run should return no rows which confirms that no DST tables affected during the operation.

SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

End the prepare.

SQL>  EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.

Shut the database and startup in UPGRADE mode.

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3424985088 bytes
Fixed Size                  2163120 bytes
Variable Size            2315263568 bytes
Database Buffers         1090519040 bytes
Redo Buffers               17039360 bytes
Database mounted.
Database opened.
SQL>

Execute the DST upgrade.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

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         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE

SQL>

Shut the database and Startup the database in NORMAL mode.

SQL> SELECT version FROM v$timezone_file;
VERSION
———-
14


============