Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)

upgrade DST 18 to 24

Download p20875898_112040_Linux-x86-64.zip

Download DBMS_DST_scriptsV1.9.zip
$ORACLE_HOME/oracore/zoneinfo/timezlrg_18.dat

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        18

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

SQL> exit

$/scratch/DBMS_DST_scriptsV1.9
  upg_tzv_check.sql
  upg_tzv_apply.sql
  countstarTSTZ.sql
  countstatsTSTZ.sql

$sqlplus '/as sysdba'

SQL> sta upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 11.2.0.4 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv24 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> sta upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv24 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             436208520 bytes
Database Buffers          616562688 bytes
Redo Buffers               13905920 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             436208520 bytes
Database Buffers          616562688 bytes
Redo Buffers               13905920 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_L"
Number of failures: 0
Table list: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_S"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_S"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_S"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_L"
Number of failures: 0
Table list: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_L"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_L"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_L"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_L"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv24 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
property@didbs2b$pwd
/scratch/applications/12c/DBMS_DST_scriptsV1.9
property@didbs2b$sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 6 13:09:42 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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