Recently, I did an upgrade from Oracle 12cR2 to 19c (RU12). It is an CDB with one PDB. One of the last steps after upgrading the database is to bring the time zone to the latest version.
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 Oracle 19c readme file which is located in the $ORACLE_HOME/oracore/zoneinfo/ directory
$ cat $ORACLE_HOME/oracore/zoneinfo/readme.txt | more Current Structure version: 3 Current Content Version :32 Content Version 31 ------------------ Timezones updated: DSTVERSION TIME_ZONE_NAME FROM_YEAR TO_YEAR 32, Africa/Bissau, 1912, 32, Africa/Windhoek, 1994, 32, Africa/Sao_Tome, 1800, 32, America/Jamaica, 1800, 1912 32, America/Grand_Turk, 1890, 1912 ... ... ------ End of updates in Content version 32 ---- Current Structure version: 3 Current Content Version :31 Content Version 31 ------------------ Timezones updated: DSTVERSION TIME_ZONE_NAME FROM_YEAR TO_YEAR 31, Africa/Windhoek, 1994, 1994 31, Africa/Windhoek, 2017, 31, Africa/Khartoum, 2017, 31, America/Juneau, 1867, 1867 31, America/Yakutat, 1867, 1867 ... ... ------ End of updates in Content version 31 ---- Current Structure version: 3 Current Content Version :30 Content Version 30 ------------------ Timezones updated: DSTVERSION TIME_ZONE_NAME FROM_YEAR TO_YEAR 30, Africa/Monrovia, 1972, 30, Africa/Ceuta, 1901, 1928 30, America/Port-au-Prince, 2017, 30, America/Guayaquil, 1992, 30, America/Guyana, 1966, 30, America/Paramaribo, 1975, 30, Antarctica/Palmer, 2016, ... ...
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. Since this was an upgrade from 12cR2 to 19c, it should be the old time zone version from the 12cR2 database.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
26
The version FROM v$timezone_file should match the DST_PRIMARY_TT_VERSION value found when selecting from DATABASE_PROPERTIES
SQL> set linesize 144 SQL> col PROPERTY_NAME format a44 SQL> col VALUE format a8 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 26 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL>
However, we want to go to time zone version 36. So lets see what we got in $ORACLE_HOME/oracore/zoneinfo/ directory.
$ cd $ORACLE_HOME/oracore/zoneinfo/ $ ls big timezlrg_13.dat timezlrg_1.dat timezlrg_26.dat timezlrg_32.dat timezlrg_9.dat timezone_16.dat timezone_22.dat timezone_29.dat timezone_5.dat little timezlrg_14.dat timezlrg_20.dat timezlrg_27.dat timezlrg_3.dat timezone_10.dat timezone_17.dat timezone_23.dat timezone_2.dat timezone_6.dat readme.txt timezlrg_15.dat timezlrg_21.dat timezlrg_28.dat timezlrg_4.dat timezone_11.dat timezone_18.dat timezone_24.dat timezone_30.dat timezone_7.dat timezdif.csv timezlrg_16.dat timezlrg_22.dat timezlrg_29.dat timezlrg_5.dat timezone_12.dat timezone_19.dat timezone_25.dat timezone_31.dat timezone_8.dat timezlrg_10.dat timezlrg_17.dat timezlrg_23.dat timezlrg_2.dat timezlrg_6.dat timezone_13.dat timezone_1.dat timezone_26.dat timezone_32.dat timezone_9.dat timezlrg_11.dat timezlrg_18.dat timezlrg_24.dat timezlrg_30.dat timezlrg_7.dat timezone_14.dat timezone_20.dat timezone_27.dat timezone_3.dat timezlrg_12.dat timezlrg_19.dat timezlrg_25.dat timezlrg_31.dat timezlrg_8.dat timezone_15.dat timezone_21.dat timezone_28.dat timezone_4.dat
At the moment, we got only the time zone version 32 shipped with the default 19c 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 apply it to the 19c Oracle Home.
Patch 32327201: RDBMS – DSTV36 UPDATE – TZDATA2020E
The patch just includes the following files which are copied to the $ORACLE_HOME/oracore/zoneinfo/ directory.
$ unzip p32327201_190000_Linux-x86-64.zip Archive: p32327201_190000_Linux-x86-64.zip creating: 32327201/ creating: 32327201/files/ creating: 32327201/files/oracore/ creating: 32327201/files/oracore/zoneinfo/ inflating: 32327201/files/oracore/zoneinfo/timezone_36.dat creating: 32327201/files/oracore/zoneinfo/big/ inflating: 32327201/files/oracore/zoneinfo/big/timezone_36.dat inflating: 32327201/files/oracore/zoneinfo/big/timezlrg_36.dat creating: 32327201/files/oracore/zoneinfo/little/ inflating: 32327201/files/oracore/zoneinfo/little/timezlrg_36.dat inflating: 32327201/files/oracore/zoneinfo/little/timezone_36.dat inflating: 32327201/files/oracore/zoneinfo/timezlrg_36.dat inflating: 32327201/files/oracore/zoneinfo/readme_36.txt inflating: 32327201/README.txt creating: 32327201/etc/ creating: 32327201/etc/config/ inflating: 32327201/etc/config/actions.xml inflating: 32327201/etc/config/inventory.xml $ cd 32327201/ $ ls -l total 8 drwxr-x---. 3 oracle dba 20 Feb 2 2021 etc drwxr-x---. 3 oracle dba 21 Feb 2 2021 files -rw-rw-r--. 1 oracle dba 5790 Feb 2 2021 README.txt
HINT: This patch can be applied online without taking the database down.
$ $ORACLE_HOME/OPatch/opatch apply 32327201
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /usr/oraprod/db/19.12.0.0.210720A
Central Inventory : /usr/oraprod/oraInventory
from : /usr/oraprod/db/19.12.0.0.210720A/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.2.0.7.0
Log file location : /usr/oraprod/db/19.12.0.0.210720A/cfgtoollogs/opatch/opatch2021-11-04_08-43-49AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 32327201
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '32327201' to OH '/usr/oraprod/db/19.12.0.0.210720A'
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 32327201 successfully applied.
Log file location: /usr/oraprod/db/19.12.0.0.210720A/cfgtoollogs/opatch/opatch2021-11-04_08-43-49AM_1.log
OPatch succeeded.
$ $ORACLE_HOME/OPatch/opatch lsinv | grep 32327201
Patch 32327201 : applied on Thu Nov 04 08:44:14 CET 2021
32327201
After we successfully applied the patch, we can see a few more files in the $ORACLE_HOME/oracore/zoneinfo/ directory. The important one for me is timezone_36.dat.
$ ls big timezlrg_13.dat timezlrg_20.dat timezlrg_28.dat timezlrg_4.dat timezone_12.dat timezone_1.dat timezone_27.dat timezone_3.dat little timezlrg_14.dat timezlrg_21.dat timezlrg_29.dat timezlrg_5.dat timezone_13.dat timezone_20.dat timezone_28.dat timezone_4.dat readme_36.txt timezlrg_15.dat timezlrg_22.dat timezlrg_2.dat timezlrg_6.dat timezone_14.dat timezone_21.dat timezone_29.dat timezone_5.dat readme.txt timezlrg_16.dat timezlrg_23.dat timezlrg_30.dat timezlrg_7.dat timezone_15.dat timezone_22.dat timezone_2.dat timezone_6.dat timezdif.csv timezlrg_17.dat timezlrg_24.dat timezlrg_31.dat timezlrg_8.dat timezone_16.dat timezone_23.dat timezone_30.dat timezone_7.dat timezlrg_10.dat timezlrg_18.dat timezlrg_25.dat timezlrg_32.dat timezlrg_9.dat timezone_17.dat timezone_24.dat timezone_31.dat timezone_8.dat timezlrg_11.dat timezlrg_19.dat timezlrg_26.dat timezlrg_36.dat timezone_10.dat timezone_18.dat timezone_25.dat timezone_32.dat timezone_9.dat timezlrg_12.dat timezlrg_1.dat timezlrg_27.dat timezlrg_3.dat timezone_11.dat timezone_19.dat timezone_26.dat timezone_36.dat
Starting with 18c (and this applies of course to 19c as well), you will find a few new time zone related scripts in the $ORACLE_HOME/rdbms/admin directory.
$ cd $ORACLE_HOME/rdbms/admin $ ls | grep utltz_ utltz_countstar.sql utltz_countstats.sql utltz_upg_apply.sql utltz_upg_apply_sys.sql utltz_upg_apply_user.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.
$ 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 19c, because they include the latest fixes, especially regarding PDB’s.
$ cat utltz_upg_check.sql | more Rem Rem $Header: rdbms/admin/utltz_upg_check.sql /st_rdbms_19/3 2021/03/07 08:43:28 huagli Exp $ Rem Rem utltz_upg_check.sql Rem Rem Copyright (c) 2017, 2021, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem utltz_upg_check.sql - TIME ZONE Upgrade Check Script Rem (for 11gR2 or higher) Rem Rem DESCRIPTION Rem This script prepares a database to update the database to the highest Rem installed timezone definitions using the utltz_upg_apply.sql script. Rem Rem NOTES Rem * This script must be run using SQL*PLUS from the database home. Rem * This script must be connected AS SYSDBA to run. Rem * The database need to be 11.2.0.1 or higher. Rem * The database will NOT be restarted . Rem * NO downtime is needed for this script. Rem * This script takes no arguments. Rem * This script WILL exit SQL*PLUS when an error is detected Rem * The dba_recyclebin WILL be purged. Rem * This script will check for all known issues at time of last update. Rem * An UPG_TZV table will be created. Rem * TZ_VERSION in Registry$database will be updated with current version. Rem * The utltz_upg_apply.sql script depends on this script. Rem * The script will write a line into the alert.log when ending successfully. Rem Rem BEGIN SQL_FILE_METADATA Rem SQL_SOURCE_FILE: rdbms/admin/utltz_upg_check.sql Rem SQL_SHIPPED_FILE: rdbms/admin/utltz_upg_check.sql Rem SQL_PHASE: UPGRADE Rem SQL_STARTUP_MODE: NORMAL Rem SQL_IGNORABLE_ERRORS: NONE Rem SQL_CALLING_FILE: Rem END SQL_FILE_METADATA Rem Rem MODIFIED (MM/DD/YY) Rem apfwkr 01/04/21 - Backport Rem apfwkr_blr_backport_30681085_19.7.0.0.200414dbru Rem from st_rdbms_19.7.0.0.0dbru Rem apfwkr 11/19/20 - Backport yuanzhou_bug-30681085 from main Rem yuanzhou 12/18/19 - 30681085: use parallel in FIND_AFFECTED_TABLES 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 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 Rem gvermeir 12/23/13 - minor changes on error handling Rem gvermeir 09/20/13 - enhanced error checking and handling Rem gvermeir 06/12/13 - enhanced storing of found result Rem gvermeir 06/07/13 - corrected check for bug 14732853 Rem gvermeir 05/16/13 - Additional check added/typos fixed Rem gvermeir 05/13/13 - Initial internal release Rem gvermeir 04/23/13 - created Rem ... ... ...
The scripts supplied with the DBMS_DST_scriptsV1.9.zip, do not contain the latest fixes from 2017.
$ 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 CDB 19.12.0.0.0 database.
$ sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 4 08:57:23 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> select version from v$timezone_file;
VERSION
----------
26
SQL>
Do the upgrade check and apply the Timezone Patch for Version 36 afterwards. In case the check shows any errors in the alert.log, you should stop here and fix the issue first.
$ ls | grep utltz_ utltz_countstar.sql utltz_countstats.sql utltz_upg_apply.sql utltz_upg_apply_sys.sql utltz_upg_apply_user.sql utltz_upg_check.sql $ sqh SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 4 09:00:26 2021 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.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 19.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 DSTv26 . 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 DSTv36 . 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 entries:
... 2021-11-04T09:01:02.146023+01:00 utltz_upg_check sucessfully found newer RDBMS DSTv36 and took 0 minutes to run. ...
Run the upgrade now … 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 DSTv36 . 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 1.7180E+10 bytes Fixed Size 13881528 bytes Variable Size 7885291520 bytes Database Buffers 9227468800 bytes Redo Buffers 53223424 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 1.7180E+10 bytes Fixed Size 13881528 bytes Variable Size 7885291520 bytes Database Buffers 9227468800 bytes Redo Buffers 53223424 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_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" 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 DSTv36 . 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:
... utltz_upg_apply is ready to update to RDBMS DSTv36 and will now restart the database in UPGRADE mode. Shutting down ORACLE instance (immediate) (OS id: 61433) Shutdown is initiated by sqlplus@host01(TNS V1-V3). 2021-11-04T09:05:19.279170+01:00 Stopping background process SMCO 2021-11-04T09:05:20.425820+01:00 ... ... utltz_upg_apply sucessfully updated this database to RDBMS DSTv36 and took 2 minutes to run. 2021-11-04T09:07:20.709694+01:00 ...
Check the new version.
$ sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 4 09:21:49 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select version from v$timezone_file;
VERSION
----------
36
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
SQL>
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.
$ sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 4 09:23:14 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select version from v$timezone_file;
VERSION
----------
26
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 19.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 DSTv26 .
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 DSTv36 .
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>
Now run the real upgrade on the PDB.
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 DSTv36 .
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_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "SYSMAN_BIPLATFORM"."ESS_FAILOVER_LEASE"
Number of failures: 0
Table list: "SYSMAN_BIPLATFORM"."ESS_FAILOVER_RESOURCE"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_CNTR_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_CNTR_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."EM_FAVORITES_E"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."EM_LOADERJOB_TARGETS_E"
Number of failures: 0
Table list: "SYSMAN"."EM_SCHED_JOB_REGISTRY_E"
Number of failures: 0
Table list: "SYSMAN"."EM_SCHED_JOB_REG_HISTORY_E"
Number of failures: 0
Table list: "SYSMAN"."EM_SERVICECUSTDASHBOARD_E"
Number of failures: 0
Table list: "SYSMAN"."EM_AUC_AGT_JOB_INFO_E"
Number of failures: 0
Table list: "SYSMAN"."EM_AUC_PREREQ_DUMP_E"
Number of failures: 0
Table list: "SYSMAN"."EM_BLACKOUTS_NG_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_CCR_LAST_MAPPER_RUN_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_CCR_REG_CIPHER_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DHCPSERVER_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS_E"
Number of failures: 0
Table list: "SYSMAN"."MGMT_SYSTEM_ERROR_LOG_E"
Number of failures: 0
Table list: "SYSMAN"."EM_PROV_DEPLOYEDIMAGE_E"
Number of failures: 0
Table list: "SYSMAN"."EM_PROV_DEPLOYMENT_PLAN_E"
Number of failures: 0
Table list: "SYSMAN"."EM_PROV_DISKLESSIMAGE_E"
Number of failures: 0
Table list: "SYSMAN"."EM_PROXY_E"
Number of failures: 0
Table list: "SYSMAN"."GEF_ERRORS_E"
Number of failures: 0
Table list: "SYSMAN"."GEF_EXTRACTS_E"
Number of failures: 0
Table list: "SYSMAN"."GEF_TRANSFERS_E"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_EVENT_BUS_TABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_EVENT_BUS_TABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_JOB_STATUS_UPD_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_JOB_STATUS_UPD_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_PC_TP_TABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_EM_PC_TP_TABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."EM_OFFLINE_DC_DETAILS_E"
Number of failures: 0
Table list: "SYSMAN"."EM_OFFLINE_DC_DTYPE_LOG_E"
Number of failures: 0
Table list: "SYSMAN"."EM_GI_AGENT_COMPLIANCE_E"
Number of failures: 0
Table list: "SYSMAN"."EM_GI_AGT_JOB_INFO_E"
Number of failures: 0
Table list: "SYSMAN"."EM_BLACKOUT_OCCURRENCES_E"
Number of failures: 0
Table list: "SYSMAN"."EM_BLACKOUT_SCHEDULE_E"
Number of failures: 0
Table list: "SYSMAN"."EM_BLACKOUT_TARGET_STATE_E"
Number of failures: 0
Table list: "SYSMAN"."EM_BLACKOUT_WINDOWS_E"
Number of failures: 0
Table list: "SYSMAN"."JAM_COLLECTION_TRACE_E"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLI$_STATTAB_TEMP"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_AUTOTASK_CLIENT_HST"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_COL_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_HISTGRM_STATS_VERSN"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_IND_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_OPTSTAT_OPERATIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_SCHEDULER_JOBS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_TAB_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_OPTSTAT_USER_PREFS$"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_SQL_STATEMENT"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_STATTAB"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_STGTAB_DIRECTIVE"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_WRI$_OPTSTAT_AUX_HISTORY"
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 DSTv36 .
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> show con_name
CON_NAME
------------------------------
PDB1
SQL> select version from v$timezone_file;
VERSION
----------
36
1 row selected.
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
1 row selected.
SQL>
In the alter.log you will find messages like the following:
... 2021-11-04T09:27:57.376617+01:00 PDB1(3):utltz_upg_apply is ready to update to RDBMS DSTv36 and will now restart the database in UPGRADE mode. PDB1(3):ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE PDB1(3):Pluggable database PDB1 closing PDB1(3):JIT: pid 35044 requesting stop PDB1(3):Closing sequence subsystem (83968546986). ... ... 2021-11-04T09:32:17.464967+01:00 PDB1(3):utltz_upg_apply sucessfully updated this database to RDBMS DSTv36 and took 4 minutes to run. ...
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 to. 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.