Upgrade timezone version to 14 after upgrading to 11.2.0.2

An out-of-place upgrade was performed to create 11.2.0.2 into separate home.

After upgrading to 11.2.0.2 from 11.2.0.1, find the timezone version using the following query:

SQL> SELECT version FROM v$timezone_file;

VERSION
———-
11

As the result is lower than 14, we need to upgrade the timezone version to 14.

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

PROPERTY_NAME
——————————
VALUE
——————————————————————————–
DST_PRIMARY_TT_VERSION
11

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE

Check for timezone .dat files as shown below:

SQL> !ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*
total 30942
-rw-r–r–   1 oracle   oinstall  791430 Aug 12  2010 timezlrg_14.dat
-rw-r–r–   1 oracle   oinstall  344448 Aug 12  2010 timezone_14.dat

Connect as SYS user and prepare for the upgrade.

SQL> exec DBMS_DST.BEGIN_PREPARE(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;  2    3    4

PROPERTY_NAME
——————————
VALUE
——————————————————————————–
DST_PRIMARY_TT_VERSION
11

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
PREPARE

You will see the following error message if you try executing the PREPARE command once again. This can be ignored safely.

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;

*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading
of a secondary time zone data file is in an active state
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DST”, line 1340
ORA-06512: at line 1

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> SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

End the prepare.

SQL> SQL> EXEC DBMS_DST.END_PREPARE;

Shut the database and startup in UPGRADE mode.

PL/SQL procedure successfully completed.
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.

Check the primary DST TZ version using the SQL below, should show up 14.

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

PROPERTY_NAME
——————————
VALUE
——————————————————————————–
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
11

DST_UPGRADE_STATE
UPGRADE

Startup the database in NORMAL mode.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
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>
SQL> SELECT version FROM v$timezone_file;

VERSION
———-
14

All steps completed.