Why do I need time zone updates at all? Because nothing is more constant than the changes. And one of those are the time zone changes. Some countries remove the summer/winter time changes, and some new time zones appear, which have never existed beforehand. In case you want to see all changes, since Oracle has implemented the time zone changes, take a look into the readme file which is located in the $ORACLE_HOME/oracore/zoneinfo/ directory.
cat /u01/app/oracle/product/18.5.0/oracore/zoneinfo/readme.txt ... ... With version 30: Timezones added: America/Punta_Arenas With version 29: Timezones added: Asia/Famagusta Asia/Atyrau Europe/Saratov ... ...
In case you want to know more about time zones, you might want to take a look at the following web sites.
https://www.iana.org/time-zones
https://tools.ietf.org/html/rfc6557
First of all, we need to check the current time zone version that we already have.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
31
The version FROM v$timezone_file should match the DST_PRIMARY_TT_VERSION value found when selecting from DATABASE_PROPERTIES
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 31 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
However, we want to go to version 33. So, lets see what we got in $ORACLE_HOME/oracore/zoneinfo/ directory.
oracle@sun1:/u01/app/oracle/product/18.5.0/oracore/zoneinfo$ ls big timezlrg_14.dat timezlrg_22.dat timezlrg_30.dat timezone_10.dat timezone_19.dat timezone_27.dat timezone_7.dat little timezlrg_15.dat timezlrg_23.dat timezlrg_31.dat timezone_11.dat timezone_2.dat timezone_28.dat timezone_8.dat readme.txt timezlrg_16.dat timezlrg_24.dat timezlrg_4.dat timezone_12.dat timezone_20.dat timezone_29.dat timezone_9.dat timezdif.csv timezlrg_17.dat timezlrg_25.dat timezlrg_5.dat timezone_13.dat timezone_21.dat timezone_3.dat timezlrg_1.dat timezlrg_18.dat timezlrg_26.dat timezlrg_6.dat timezone_14.dat timezone_22.dat timezone_30.dat timezlrg_10.dat timezlrg_19.dat timezlrg_27.dat timezlrg_7.dat timezone_15.dat timezone_23.dat timezone_31.dat timezlrg_11.dat timezlrg_2.dat timezlrg_28.dat timezlrg_8.dat timezone_16.dat timezone_24.dat timezone_4.dat timezlrg_12.dat timezlrg_20.dat timezlrg_29.dat timezlrg_9.dat timezone_17.dat timezone_25.dat timezone_5.dat timezlrg_13.dat timezlrg_21.dat timezlrg_3.dat timezone_1.dat timezone_18.dat timezone_26.dat timezone_6.dat oracle@sun1:/u01/app/oracle/product/18.5.0/oracore/zoneinfo$
At the moment, we got only the time zone version 31 shipped with the default installation.
The general steps for upgrading to a new time zone version are:
- Take a RMAN full backup
- Create a guaranteed restore point
- Shutdown the application
- Apply the time zone patch
- Run the check and upgrade scripts for the CDB
- Run the check and upgrade scripts for the PDB’s
- Start the application
In this blog, I will not go into detail regarding RMAN or Flashback database. I will cover here only step 4,5 and 6.
Ok. Lets start by downloading the following patch, and to apply it.
- Patch 28852325: RDBMS – DSTV33 UPDATE – TZDATA2018G
The patch just includes the following files which are copied to the $ORACLE_HOME/oracore/zoneinfo/ directory.
oracle@sun1:/u01/stage/18c/TimeZone/28852325/files/oracore/zoneinfo$ ls -l
total 2435
drwxr-x--- 2 oracle oinstall 4 Mar 15 13:12 big
drwxr-x--- 2 oracle oinstall 4 Mar 15 13:12 little
-rw-r--r-- 1 oracle oinstall 58353 Mar 6 19:27 readme_33.txt
-rw-r--r-- 1 oracle oinstall 785523 Mar 6 19:27 timezlrg_33.dat
-rw-r--r-- 1 oracle oinstall 341123 Mar 6 19:27 timezone_33.dat
HINT: This patch can be applied online without taking the database down.
oracle@sun1:/u01/stage/18c/TimeZone$ opatch apply 28852325
Oracle Interim Patch Installer version 12.2.0.1.16
Copyright (c) 2019, Oracle Corporation. All rights reserved.
[sourcecode language="plain"]
Oracle Home : /u01/app/oracle/product/18.5.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/18.5.0/oraInst.loc
OPatch version : 12.2.0.1.16
OUI version : 12.2.0.4.0
Log file location : /u01/app/oracle/product/18.5.0/cfgtoollogs/opatch/opatch2019-03-15_13-13-08PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28852325
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '28852325' to OH '/u01/app/oracle/product/18.5.0'
Patching component oracle.oracore.rsf, 18.0.0.0.0...
Patch 28852325 successfully applied.
Log file location: /u01/app/oracle/product/18.5.0/cfgtoollogs/opatch/opatch2019-03-15_13-13-08PM_1.log
OPatch succeeded.
oracle@sun1:/u01/stage/18c/TimeZone$ opatch lsinv | grep 28852325
Patch 28852325 : applied on Fri Mar 15 13:13:25 CET 2019
28852325
After we successfully applied the patch, we can see a few more file in the $ORACLE_HOME/oracore/zoneinfo/ directory. The important one for me is timezone_33.dat.
oracle@sun1:/u01/app/oracle/product/18.5.0/oracore/zoneinfo$ ls big timezlrg_13.dat timezlrg_21.dat timezlrg_3.dat timezlrg_9.dat timezone_17.dat timezone_25.dat timezone_4.dat little timezlrg_14.dat timezlrg_22.dat timezlrg_30.dat timezone_1.dat timezone_18.dat timezone_26.dat timezone_5.dat readme.txt timezlrg_15.dat timezlrg_23.dat timezlrg_31.dat timezone_10.dat timezone_19.dat timezone_27.dat timezone_6.dat readme_33.txt timezlrg_16.dat timezlrg_24.dat timezlrg_33.dat timezone_11.dat timezone_2.dat timezone_28.dat timezone_7.dat timezdif.csv timezlrg_17.dat timezlrg_25.dat timezlrg_4.dat timezone_12.dat timezone_20.dat timezone_29.dat timezone_8.dat timezlrg_1.dat timezlrg_18.dat timezlrg_26.dat timezlrg_5.dat timezone_13.dat timezone_21.dat timezone_3.dat timezone_9.dat timezlrg_10.dat timezlrg_19.dat timezlrg_27.dat timezlrg_6.dat timezone_14.dat timezone_22.dat timezone_30.dat timezlrg_11.dat timezlrg_2.dat timezlrg_28.dat timezlrg_7.dat timezone_15.dat timezone_23.dat timezone_31.dat timezlrg_12.dat timezlrg_20.dat timezlrg_29.dat timezlrg_8.dat timezone_16.dat timezone_24.dat timezone_33.dat oracle@sun1:/u01/app/oracle/product/18.5.0/oracore/zoneinfo$
Starting with 18c, you will find a few new scripts in the $ORACLE_HOME/rdbms/admin directory.
oracle@sun1:/u01/app/oracle/product/18.5.0/rdbms/admin$ ls | grep utltz_ utltz_countstar.sql utltz_countstats.sql utltz_upg_apply.sql utltz_upg_check.sql
However, in case you are still on 12cR2 or 12cR1 you might want to download the scripts from the following MOS Note:
- Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
Which is, DBMS_DST_scriptsV1.9 at the time of writing.
oracle@sun1:/u01/stage/18c/TimeZone$ unzip DBMS_DST_scriptsV1.9.zip Archive: DBMS_DST_scriptsV1.9.zip creating: DBMS_DST_scriptsV1.9/ inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql
I am going to use the supplied scripts which are shipped with 18c, because they include the latest fixes, especially regarding PDB’s.
oracle@sun1:/u01/app/oracle/product/18.5.0/rdbms/admin$ cat utltz_upg_check.sql ... ... Rem MODIFIED (MM/DD/YY) Rem huagli 08/31/17 - 26721930: DB version change Rem huagli 06/09/17 - 25988996: CDB/PDB RAC check and various cleanup Rem huagli 04/07/17 - 25856520: handle PDB name correctly Rem huagli 01/31/17 - renamed to utltz_upg_check.sql and added to shiphome Rem gvermeir 08/22/14 - updated to handle CDB/PDB (Multitenant) DST updates Rem gvermeir 07/10/14 - changed 1882 in DST$ERROR_TABLE from error to warning Rem gvermeir 05/23/14 - changed detection of Bug 14732853 to avoid using DBA_TSTZ_TAB_COLS ... ...
The scripts supplied with the DBMS_DST_scriptsV1.9.zip, do not contain the latest fixes from 2017.
oracle@sun1:/u01/stage/18c/TimeZone/DBMS_DST_scriptsV1.9$ cat upg_tzv_check.sql ... ... Rem MODIFIED (MM/DD/YY) Rem gvermeir 08/22/14 - updated to handle CDB/PDB (Multitenant) DST updates Rem gvermeir 07/10/14 - changed 1882 in DST$ERROR_TABLE from error to warning Rem gvermeir 05/23/14 - changed detection of Bug 14732853 to avoid using DBA_TSTZ_TAB_COLS Rem gvermeir 03/17/14 - logging of time makes more sense in minutes Rem gvermeir 03/04/14 - known bug detection is now faster on some dbs Rem gvermeir 02/20/14 - added logging to alert.log ... ...
Now … be careful. The next steps require a downtime. It is not possible to upgrade a TZ without a downtime. Only the TZ patch itself can be applied online.
Before we run the TZ upgrade scripts, you might want to purge the Scheduler History and the DBMS_STATS history, to speed up the TZ Upgrade. These steps are optional.
SQL> EXEC DBMS_SCHEDULER.PURGE_LOG; PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0); PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.PURGE_STATS(SYSTIMESTAMP); PL/SQL procedure successfully completed. -- -- Do the TZ Upgrade. -- SQL> EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(31); PL/SQL procedure successfully completed.
Now we do the Time Zone Upgrade on the CDB18 18.1.5 Database.
SQL> select version from v$timezone_file;
VERSION
----------
31
Apply Timezone Patch for Version 33 and do the Upgrade Check.
oracle@sun1:/u01/app/oracle/product/18.5.0/rdbms/admin$ ls | grep utltz_ utltz_countstar.sql utltz_countstats.sql utltz_upg_apply.sql utltz_upg_check.sql oracle@sun1:/u01/app/oracle/product/18.5.0/rdbms/admin$ sq SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 15 14:59:54 2019 Version 18.5.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.5.0.0.0 SQL> @utltz_upg_check.sql Session altered. 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 18.0.0.0 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. WARNING: There are 1 open PDBs . WARNING: They will be closed when running utltz_upg_apply.sql . INFO: Database RDBMS DST version is DSTv31 . 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 DSTv33 . 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 utltz_upg_apply.sql to do the actual RDBMS DST update. INFO: Note that the utltz_upg_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. Session altered. SQL>
In the alert log you will see the following entry
... 2019-03-15T15:00:04.719062+01:00 utltz_upg_check sucessfully found newer RDBMS DSTv33 and took 0 minutes to run. ...
Run the upgrade … and be careful, this script will restart the database 2 times.
SQL> @utltz_upg_apply.sql Session altered. INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv33 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. WARNING: There are 1 open PDBs . WARNING: They will be closed when CDB$ROOT is restarted 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 4294965728 bytes Fixed Size 8894944 bytes Variable Size 989855744 bytes Database Buffers 3288334336 bytes Redo Buffers 7880704 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 4294965728 bytes Fixed Size 8894944 bytes Variable Size 989855744 bytes Database Buffers 3288334336 bytes Redo Buffers 7880704 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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY" Number of failures: 0 Table list: "APEX_050100"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2" 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 DSTv33 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this SQL*Plus session. INFO: Do not use it for timezone related selects. Session altered. SQL>
In the alert.log you will see
... 2019-03-15T15:02:27.513093+01:00 utltz_upg_apply is ready to update to RDBMS DSTv33 and will now restart the database in UPGRADE mode. Shutting down instance (immediate) (OS id: 6310) Stopping background process SMCO 2019-03-15T15:02:28.928167+01:00 ... ... ALTER pluggable DATABASE OPEN upgrade ORA-65000 signalled during: ALTER pluggable DATABASE OPEN upgrade... ... ... 2019-03-15T15:08:09.401025+01:00 utltz_upg_apply sucessfully updated this database to RDBMS DSTv33 and took 6 minutes to run. ...
Check the new version
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select version from v$timezone_file;
VERSION
----------
33
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
Keep in mind. Updating the RDBMS DST version of the CDB will not change the RDBMS_DST version of the PDB’s in this CDB and updating the RDBMS DST version of a PDB will not change the RDBMS_DST version of the other PDB’s or the CDB.
SQL> connect system@PDB1
Enter password:
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select version from v$timezone_file;
VERSION
----------
31
SQL> @utltz_upg_check.sql
Session altered.
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 18.0.0.0 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is PDB1 .
INFO: Database RDBMS DST version is DSTv31 .
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 DSTv33 .
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 utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL>
SQL> @utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv33 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is PDB1 .
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.
Pluggable Database closed.
Pluggable 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.
Pluggable Database closed.
Pluggable 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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_050100"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2"
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 DSTv33 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL>
oracle@sun1:/u01/app/oracle/product/18.5.0/rdbms/admin$ sqlplus sys@PDB1 as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 15 15:18:49 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select version from v$timezone_file;
VERSION
----------
33
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
SQL>
In case you want to monitor the progress of the time zone upgrade, you might want to execute the following scripts while the script utltz_upg_apply.sql is running.
SELECT count(*) FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES'; set PAGES 1000 select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'), TIME_REMAINING, SOFAR, TOTALWORK, SID, SERIAL#, OPNAME from V$SESSION_LONGOPS where sid in (select SID from V$SESSION where CLIENT_INFO = 'upg_tzv') and SOFAR < TOTALWORK order by START_TIME; select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID, S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT, S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO, S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program", Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL" from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS where S.SQL_ID = Q1.SQL_ID(+) and S.PREV_SQL_ID = Q2.SQL_ID(+) and S.BLOCKING_SESSION = BS.SID(+) and S.CLIENT_INFO = 'upg_tzv';
You might find the following MOS Notes helpful if you want to know more about time zone upgrades.
- Download Time Zone Patch from MOS Note 412160.1
- Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
- Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
Conclusion
Time zone changes happens every now and then and it is good know how you can upgrade your Oracle database to the latest time zone release, in case you need too. However, do very good testing, before you run these scripts on your production database. And take care, that this is not an online procedure. The CDB is restarted several times, and the PDB’s are not upgraded automatically. You need to take care of the PDB’s yourself.