Is a time zone upgrade really required?
It depends. When I was a junior, I used to be frustrated by that answer, but now I’ve come to understand and appreciate it. If your database does not store any timestamp data with a time zone (i.e., the data types DATE or TIMESTAMP are without time zone attributes), then it may not be necessary. However, you might think, ‘I don’t have any data that should make me anxious, and I can just stay up-to-date since it’s always recommended to keep up with the latest updates.’
But if your organization operates across different geographic regions with clients in different time zones, and you have data stored with time zone information, then it is important to consider time zones when managing your data.
Which data types are affected by a database time zone upgrade, and what is the impact?
The TIMESTAMP WITH TIME ZONE (TSTZ) and TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) data types depend on time zone information, whereas DATE and TIMESTAMP data types do not.
TSTZ stores both the timestamp and the time zone offset/name at the time of insertion. This makes it ideal for applications dealing with multiple time zones, as it retains the original time zone used. This way, you have the complete offset or named time zone information preserved.
When users input timestamps with a time zone, Oracle Database automatically converts the data to UTC, using the transition rules defined in the time zone file. Upon retrieval, the database performs the reverse conversion from UTC back to the original time zone. For example, the timestamp TIMESTAMP ‘2024-13-11 13:00:00 Europe/Istanbul’ is stored as UTC value 2024-13-11 10:00:00 (along with the original time zone identifier for ‘Europe/Istanbul’). This is because Istanbul was operating at UTC +3 hours at that time.
TSLTZ does not store explicit time zone information. Instead, it normalizes the timestamp to the database’s time zone (DBTIMEZONE value of database) when data is sent to and from clients. Internally, it converts timestamps based on the database time zone at the time of insertion and retrieval. TSLTZ requires 11 bytes of storage.
TSTZ requires 13 bytes of storage (2 more bytes than TIMESTAMP and TSLTZ) because it stores additional time zone information, either as a region name or as a UTC offset.
A common misconception is that the database time zone should match your server’s physical location. This is not necessary. The database time zone has no impact on where the server is hosted. The best practice is to set time_zone (DBTIMEZONE) to +00:00 (or another offset like -09:00 or +08:00) and leave it unchanged if already set to an offset. It is strongly recommended to never use any named time zone that is affected by DST as a database time zone, the preferred value is an offset like +00:00 .
Tables that contain TSTZ columns must be in a state that allows updates while apply script was running during the timezone upgrades. Timestamps affected by time zone changes must be converted. However, DST updates do not directly affect the data stored in TSLTZ columns.
Although it may seem a bit complicated at first glance, I recommend reading Timestamps & time zones – Frequently Asked Questions (Doc ID 340512.1) , Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1) and How to Determine Whether Time Zone Changes Will Affect Your Database (Doc ID 406410.1) to better understand the concepts and the impact of a time zone upgrade on the database.
You may use query provided below to find columns with timezone data. (Both TSTZ and TSLTZ)
SQL> select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
|| c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%WITH%TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by colWhat factors should be considered before performing a database time zone upgrade?
Potential ORA-39405 error in Data Pump operations and ORA-39322 error in transportable tablespace operations.
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32.
ORA-39405
Oracle Data Pump does not support importing from a source database with TSTZ version string into a target database with TSTZ version string.
Cause
The source database was at a different time zone version than the target database and there were tables that contained TIMESTAMP WITH TIME ZONE (TSTZ) data.
Action
Convert the target database to the same time zone version or later as the source database or use Oracle Data Pump with conventional data movement to export then import this data.
Also There is a pitfall to be aware of before updating the database time zone is the possibility of encountering the ORA-39405 error in datapump operations even if you don’t have any timestamp data with time zones in the data you are trying to transfer. Oracle Data Pump does not support importing from a source database with TSTZ version X into a target database with TSTZ version Y. This issue occurs when the time zone version on your source database is newer than the version on the target database. The time zone file is cumulative, meaning it includes information from past versions but doesn’t have any info about future versions. As a result, if you’re migrating to an older time zone release, the target database won’t be able to correctly interpret the time zone from the source database. Impdp to a higher DST version is not a problem. The issue occurs while you want to go backwards only.
Of course there are some workarounds in Mike Dietrich’s blog post ORA-39405 with impdp when you have mixed time zone versions. You can also use the workaround provided below. I came across this idea from Shakil Shaikh in a LinkedIn comment.
A simple solution for ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32.
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32.
1. Use job_name option in the impdp command, the related master table will be created with this job name
JOB_NAME=IMPDP_TZCHANGE
2. Write a code block according to the your Operating system. Here is sample for Linux bourne shell , just continuously updates the master table. You may run it on another terminal.
source /home/oracle/.bashrc
export ORAENV_ASK=NO
export ORACLE_SID=bltdb1
. oraenv bltdb1
while true
do
sqlplus / as sysdba <<EOF
update sys.IMPDP_TZCHANGE set property = 43 where property = 32;
commit;
EOF
done
3. Execute the data pump import job with job_name option.
4. Once the import job has been succesfully completed, kill/terminate the process updating the master table.Actually, Updating the target database time zone to the same time zone version as the source database will fix all these errors.
Updated on 06.02.2025 : : According to Mike Dietrich’s blog post, “Remember ORA-39405 with impdp? There is a fix for it,” a fix (Patch 37041429) will be available soon and is most likely to be shipped with the Data Pump Bundle Patch.
Oracle Engineered Systems, like Exadata, come with its own Operating System Image.
Exadata image version updates do not include the database, JDK/JRE or OJVM time zone upgrades by default. Only Operating system time zone files are updated at the image upgrade (operating system) phase. There should be no problem with updating database time zones. When the database has a more up-to-date time zone file than the storage server (cell), it sends the updated file to the cell.
This transfer happens when a query is executed. If a newer time zone file is found on the DB node, it is sent to the cell. From that point on, the new time zone file is cached on the cellsrv. Considering that each PDB may have a different database time zone, it’s understandable why it works this way. There is also a document you may refer to “Exadata and DST (time zones) (Doc ID 1343576.1)“.
The time zone (DST) upgrade procedures for OJVM and Java in ORACLE_HOMES (like database, OMS, EM Agents) are separate and, in most cases, not necessary.
DST Upgrade for OJVM : There are very few applications who actually use the DST information of the OJVM. OJVM and Database DST version are NOT depending on each other and they can be at different versions.
DST Upgrade for Java : There is no technical need to apply “java DST updates” to the JDK/JRE that is installed in the Oracle Database Home, this information is not used by the database at all.
No action should be taken for Oracle Grid Software.
Note: Much more detailed information about the separate update procedures for each component/product is provided in the Applying document for DSTvx. For version 43, you can refer to Applying the DSTv43 Update for the Oracle Database (Doc ID 3002390.1) for a comprehensive explanation.
Now it is time for upgrading the database time zone version in a sample database step by step.
It’s time to roll up our sleeves and get the job done.
Since Oracle Database 19c Release Update (RU) 19.18 all available time zone files are shipped and deployed to $ORACLE_HOME/oracore/zoneinfo but not applied. That is up to us.
I will provide the steps for a non-CDB 19c database. For a CDB with PDBs, the process is largely the same, you will follow the same routine for each PDB and CDB. The PDB$SEED always receives the time zone during the creation of the CDB. Officially, upgrading PDB$SEED is not supported, but there are possible solutions available.
As I already have DBRU 19.24 installed, I have latest time zone files on my database homes.
Chapter 4 Datetime Data Types and Time Zone Support of Database Globalization Support Guide for Oracle Database 19c is the guide we will follow.
Prepare Window:
We will run utltz_countstats.sql script which shows the optimizer statistics of num_rows of all the tables having TIMESTAMP WITH TIME ZONE (TSTZ) data. Since it relies on database optimizer statistics, statistics should be up to date. If not you should run the utltz_countstar.sql script.
[oracle@blt01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.24/dbhome_1
[oracle@blt01 ~]$ export ORACLE_SID=bltdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 4 09:26:14 2024
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SYS@bltdb1> set timing on
SYS@bltdb1> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Session altered.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
.
Elapsed: 00:00:00.01
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
Elapsed: 00:00:00.00
.
Elapsed: 00:00:00.00
For SYS tables first ...
Elapsed: 00:00:00.00
Note: empty tables are not listed.
Elapsed: 00:00:00.00
Stat date - Owner.TableName.ColumnName - num_rows
Elapsed: 00:00:00.01
30/10/2020 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 34
30/10/2020 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 34
30/10/2020 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 34
01/12/2016 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
01/12/2016 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
01/12/2016 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
01/12/2016 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
01/12/2016 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
01/12/2016 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
01/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
01/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
01/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
01/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
01/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
01/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
01/12/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
01/12/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
01/12/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
01/12/2016 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
01/12/2016 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
01/12/2016 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
01/12/2016 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
01/12/2016 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
01/12/2016 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
04/11/2024 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
04/11/2024 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
04/11/2024 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
26/10/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
26/10/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
26/10/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
26/10/2022 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 3
04/11/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
04/11/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
04/11/2024 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
04/11/2024 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
04/11/2024 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
01/12/2020 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
01/12/2020 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
04/11/2024 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
04/11/2024 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3
04/11/2024 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3
04/11/2024 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
04/11/2024 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
04/11/2024 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 1159792
29/12/2023 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 78
01/12/2020 - SYS.RADM_FPTM$.TSWTZ_COL - 1
22/01/2021 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
22/01/2021 - SYS.REG$.REG_TIME - 2
31/10/2024 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 45517
03/11/2024 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
04/11/2024 - SYS.SCHEDULER$_JOB.END_DATE - 80
04/11/2024 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 80
04/11/2024 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 80
04/11/2024 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 80
04/11/2024 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 80
04/11/2024 - SYS.SCHEDULER$_JOB.START_DATE - 80
03/11/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 20862
03/11/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 20862
03/11/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 20862
01/12/2020 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
01/12/2020 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
04/11/2024 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
04/11/2024 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
04/11/2024 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
04/11/2024 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
04/11/2024 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
04/11/2024 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
03/11/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 30
03/11/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 30
03/11/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 30
03/11/2024 - SYS.STATS_TARGET$.END_TIME - 7779
03/11/2024 - SYS.STATS_TARGET$.START_TIME - 7779
18/09/2024 - SYS.TAB_STATS$.SPARE6 - 1190
31/10/2024 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 87
31/10/2024 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 87
01/11/2024 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 2
01/11/2024 - SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - 2
03/11/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 2776913
03/11/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 2776913
03/11/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 246289
03/11/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 246289
03/11/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 19166
03/11/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 19166
04/11/2024 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 1051
04/11/2024 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1051
04/11/2024 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 1051
03/11/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 40682
03/11/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 40682
03/11/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 40682
03/11/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 15616
03/11/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 15616
04/11/2024 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 8
31/10/2024 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 5
31/10/2024 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 5
04/11/2024 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 470
04/11/2024 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 430
04/11/2024 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 430
01/12/2020 - SYS.XS$PRIN.END_DATE - 15
01/12/2020 - SYS.XS$PRIN.START_DATE - 15
Total numrows of SYS TSTZ columns is : 7528363
There are in total 172 SYS TSTZ columns.
Elapsed: 00:00:00.20
.
Elapsed: 00:00:00.00
For non-SYS tables ...
Elapsed: 00:00:00.00
Note: empty tables are not listed.
Elapsed: 00:00:00.00
Stat date - Owner.Tablename.Columnname - num_rows
Elapsed: 00:00:00.00
01/07/2021 - APEX_190200.WWV_QS_RANDOM_NAMES.TSWTZ - 2001
08/03/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
08/03/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
08/03/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
01/11/2024 - SYSTEM.T_DBA_USERS_LOG.LAST_LOGIN - 4064
01/12/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
01/12/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
01/12/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
01/12/2020 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
01/12/2020 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 6073
There are in total 31 non-SYS TSTZ columns.
Elapsed: 00:00:00.39
Elapsed: 00:00:00.00
Total Minutes elapsed : 0
Elapsed: 00:00:00.00
Session altered.To minimize the downtime, we will purge the scheduler job log records.
SYS@bltdb1> exec dbms_scheduler.purge_log;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.39Whenever statistics in the dictionary are modified, older versions of the statistics are automatically saved for potential future restoration. These versions are stored in the SYS.WRI$_OPSTAT_HIST* tables. Since these tables can hold large amounts of data, and to minimize the overall process duration, we will purge older statistics.
Using DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL) performs a fast TRUNCATE instead of a DELETE, especially on systems version 11.2.0.3 and above. This method is significantly faster for purging millions of rows from SYS.WRI$_OPTSTAT_HIST% tables compared to DBMS_STATS.PURGE_STATS(systimestamp). It only affects historical stats tables without touching current statistics, making it a safe and efficient option.
-- Check number of rows
SYS@bltdb1> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
COUNT(*)
----------
2716564
SYS@bltdb1> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
COUNT(*)
----------
238878
-- Check old stats active retention period
SYS@bltdb1> select systimestamp - dbms_stats.get_stats_history_availability from dual;
SYSTIMESTAMP-DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
+000000031 02:15:20.758545000
-- Change stats retention period
SYS@bltdb1> exec dbms_stats.alter_stats_history_retention(0);
-- Purge old stats
SYS@bltdb1> exec DBMS_STATS.PURGE_STATS(systimestamp);
PL/SQL procedure successfully completed.
Elapsed: 00:01:19.36Check the version of current time zone file.
SYS@bltdb1> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
SYS@bltdb1> 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 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONEUpgrade Window:
You can stop applications here. (Internet Information Services and all other middleware products etc.) At the apply stage database will be restarted twice. No application should query or insert time zone data in the database during the time zone upgrade process.
TIME ZONE_VERSION_UPGRADE_ONLINE parameter which comes with 21c, also gives the chance of near zero downtime time zone upgrade.
Change RAC database to single instance (by setting cluster_database to FALSE). Otherwise you may get the warning provided below when running utltz_upg_check.sql
...
WARNING: This RAC database is not started in single instance mode.
WARNING: Set cluster_database = false and start as single instance
WARNING: BEFORE running utltz_upg_apply.sql!
WARNING: This is REQUIRED!
...You may also take a restore point as a precautionary measure, just to ensure you have a recovery plan in place. I assume that your applications are stopped and no data is being modified at the moment. Daniel Overby Hansen demonstrates how you can revert a time zone file upgrade using Flashback Database.
[oracle@blt01~]$ srvctl stop instance -d bltdb -i bltdb2 -f
[oracle@~]$ ~]$ bltdb1
SYS@bltdb1> alter system set cluster_database=false scope=spfile;
System altered.
SYS@bltdb1> shutdown immediate
SYS@bltdb1> startup
SYS@bltdb1> create restore point BLTDBBEFORETZ guarantee flashback database; We will run check script (utltz_upg_check.sql)
SYS@bltdb1> set timing on
SYS@bltdb1> spool utltz_upg_check.log
SYS@bltdb1> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Session altered.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
INFO: Starting with RDBMS DST update preparation.
Elapsed: 00:00:00.01
INFO: NO actual RDBMS DST update will be done by this script.
Elapsed: 00:00:00.00
INFO: If an ERROR occurs the script will EXIT sqlplus.
Elapsed: 00:00:00.00
INFO: Doing checks for known issues ...
Elapsed: 00:00:00.01
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv32 .
INFO: No known issues detected.
Elapsed: 00:00:00.17
INFO: Now detecting new RDBMS DST version.
Elapsed: 00:00:00.01
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv43 .
Elapsed: 00:00:00.25
INFO: Next step is checking all TSTZ data.
Elapsed: 00:00:00.01
INFO: It might take a while before any further output is seen ...
Elapsed: 00:00:00.00
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.
Elapsed: 00:00:10.05
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00On Standby Databases:
Time zone upgrade patch starts the primary database in upgrade mode, but redo logs that were generated while the database is in upgrade mode cannot be applied to an active standby/data guard database. Otherwise MRP process will get terminated with error ORA-10485 You should stop Real Time Apply in standby databases and apply shoud be started with using archived logfile.
--Standby side
[oracle@gns01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.24/dbhome_1
[oracle@gns01 ~]$ export ORACLE_SID=bltdb1
SYS@gnsdb1> alter database recover managed standby database cancel;
SYS@gnsdb1> startup mount force;
SYS@gnsdb1> alter database recover managed standby database using archived logfile disconnect;On Primary Database:
If the check script (utltz_upg_check.sql) runs successfully, we will complete the database time zone upgrade with the apply (utltz_upg_apply.sql) command. This will restart the database twice. First, it will restart the database in UPGRADE mode to begin the DST upgrade and update all SYS-owned TSTZ data. On the second restart, it will restart the database in NORMAL mode to upgrade non-SYS TSTZ data
SYS@bltdb1> set timing on
SYS@bltdb1> spool utltz_upg_apply.log
SYS@bltdb1> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Session altered.
Elapsed: 00:00:00.01
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
INFO: The database RDBMS DST version will be updated to DSTv43 .
Elapsed: 00:00:00.02
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
Elapsed: 00:00:00.01
Elapsed: 00:00:03.02
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 8576467184 bytes
Fixed Size 13693168 bytes
Variable Size 5167382528 bytes
Database Buffers 3187671040 bytes
Redo Buffers 207720448 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
Elapsed: 00:00:00.01
INFO: Upgrading all SYS owned TSTZ data.
Elapsed: 00:00:00.00
INFO: It might take time before any further output is seen ...
Elapsed: 00:00:00.00
An upgrade window has been successfully started.
Elapsed: 00:00:24.64
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 8576467184 bytes
Fixed Size 13693168 bytes
Variable Size 5167382528 bytes
Database Buffers 3187671040 bytes
Redo Buffers 207720448 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
Elapsed: 00:00:00.00
INFO: It might take time before any further output is seen ...
Elapsed: 00:00:00.01
INFO: Do NOT start any application yet that uses TSTZ data!
Elapsed: 00:00:00.00
INFO: Next is a list of all upgraded tables:
Table list: "SYSTEM"."T_DBA_USERS_LOG"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
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: "APEX_190200"."WWV_QS_RANDOM_NAMES"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ACTIVITY_LOG1$"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ACTIVITY_LOG2$"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ISSUE_NOTIFICATIONS"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_190200"."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 DSTv43 .
Elapsed: 00:00:05.93
INFO: The RDBMS DST update is successfully finished.
Elapsed: 00:00:00.00
INFO: Make sure to exit this SQL*Plus session.
Elapsed: 00:00:00.01
INFO: Do not use it for timezone related selects.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.00The TZ_VERSION column in the Registry$database table now updated with the new time zone version.
SYS@bltdb1> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_43.dat 43 0
SYS@bltdb1> 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 43
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SYS@bltdb1> select tz_version from Registry$database;
TZ_VERSION
----------
43Hope it helps.
Osman’s DBlog 
Leave your comment