Introduction
In Oracle, the timezone file is the data file the database uses to store and manage time zone information, including the rules for daylight saving time and standard time across different regions. Governments periodically change daylight saving rules for various countries, so Oracle periodically releases updated timezone file versions to keep up. Running on an outdated version can cause incorrect time calculations for regions whose DST rules changed after your current file was released. This guide walks through upgrading the timezone file, step by step.
Time Zone File Locations
- Oracle stores time zone files in the
$ORACLE_HOME/oracore/zoneinfo/directory. - Files are named
timezlrg_XX.dat(the large time zone file) andtimezone_XX.dat(the small time zone file), whereXXis the version number.
Prerequisites
- Plan for roughly 45 minutes of database downtime, since the upgrade process restarts the database multiple times.
Environment Used in This Guide
- Hostname: test
- IP: 192.168.101.15
ORACLE_SID: orcl
Step 1: Check the Current and Available Timezone File Versions
Before upgrading, check the version currently in use, at both the CDB and PDB level, since they can be on different versions independently. Then check the zoneinfo directory to see the highest version file actually available on disk, that's the version you can upgrade to.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB2 READ WRITE NO
SQL>
SQL> alter session set container=ORCLPDB2;
Session altered.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
SQL>
SQL> exit
[oracle@test binaries]$ cd $ORACLE_HOME/oracore/zoneinfo/
[oracle@test zoneinfo]$ ls -lrth
total 49M
-rw-r--r--. 1 oracle oinstall 337K Dec 18 2012 timezone_13.dat
-rw-r--r--. 1 oracle oinstall 773K Dec 18 2012 timezlrg_14.dat
-rw-r--r--. 1 oracle oinstall 280K Dec 18 2012 timezone_4.dat
-rw-r--r--. 1 oracle oinstall 269K Dec 18 2012 timezone_2.dat
-rw-r--r--. 1 oracle oinstall 775K Jun 17 2014 timezlrg_10.dat
-rw-r--r--. 1 oracle oinstall 296K Dec 9 2015 timezone_8.dat
-rw-r--r--. 1 oracle oinstall 334K Dec 9 2015 timezone_17.dat
-rw-r--r--. 1 oracle oinstall 337K Dec 9 2015 timezone_14.dat
-rw-r--r--. 1 oracle oinstall 338K Dec 9 2015 timezone_11.dat
-rw-r--r--. 1 oracle oinstall 603K Dec 9 2015 timezlrg_8.dat
-rw-r--r--. 1 oracle oinstall 336K Jun 22 2016 timezone_22.dat
-rw-r--r--. 1 oracle oinstall 338K Jun 22 2016 timezone_10.dat
-rw-r--r--. 1 oracle oinstall 763K Jun 22 2016 timezlrg_25.dat
-rw-r--r--. 1 oracle oinstall 764K Jun 22 2016 timezlrg_18.dat
-rw-r--r--. 1 oracle oinstall 767K Jul 25 2016 timezlrg_27.dat
-rw-r--r--. 1 oracle oinstall 335K Jul 25 2016 timezone_27.dat
-rw-r--r--. 1 oracle oinstall 280K Aug 10 2016 timezone_3.dat
-rw-r--r--. 1 oracle oinstall 335K Aug 10 2016 timezone_26.dat
-rw-r--r--. 1 oracle oinstall 333K Aug 10 2016 timezone_25.dat
-rw-r--r--. 1 oracle oinstall 333K Aug 10 2016 timezone_24.dat
-rw-r--r--. 1 oracle oinstall 336K Aug 10 2016 timezone_23.dat
-rw-r--r--. 1 oracle oinstall 336K Aug 10 2016 timezone_21.dat
-rw-r--r--. 1 oracle oinstall 336K Aug 10 2016 timezone_20.dat
-rw-r--r--. 1 oracle oinstall 268K Aug 10 2016 timezone_1.dat
-rw-r--r--. 1 oracle oinstall 336K Aug 10 2016 timezone_19.dat
-rw-r--r--. 1 oracle oinstall 334K Aug 10 2016 timezone_18.dat
-rw-r--r--. 1 oracle oinstall 336K Aug 10 2016 timezone_16.dat
-rw-r--r--. 1 oracle oinstall 337K Aug 10 2016 timezone_15.dat
-rw-r--r--. 1 oracle oinstall 337K Aug 10 2016 timezone_12.dat
-rw-r--r--. 1 oracle oinstall 783K Aug 10 2016 timezlrg_9.dat
-rw-r--r--. 1 oracle oinstall 588K Aug 10 2016 timezlrg_7.dat
-rw-r--r--. 1 oracle oinstall 573K Aug 10 2016 timezlrg_6.dat
-rw-r--r--. 1 oracle oinstall 574K Aug 10 2016 timezlrg_5.dat
-rw-r--r--. 1 oracle oinstall 519K Aug 10 2016 timezlrg_4.dat
-rw-r--r--. 1 oracle oinstall 516K Aug 10 2016 timezlrg_3.dat
-rw-r--r--. 1 oracle oinstall 497K Aug 10 2016 timezlrg_2.dat
-rw-r--r--. 1 oracle oinstall 768K Aug 10 2016 timezlrg_26.dat
-rw-r--r--. 1 oracle oinstall 759K Aug 10 2016 timezlrg_24.dat
-rw-r--r--. 1 oracle oinstall 771K Aug 10 2016 timezlrg_23.dat
-rw-r--r--. 1 oracle oinstall 767K Aug 10 2016 timezlrg_22.dat
-rw-r--r--. 1 oracle oinstall 765K Aug 10 2016 timezlrg_21.dat
-rw-r--r--. 1 oracle oinstall 772K Aug 10 2016 timezlrg_20.dat
-rw-r--r--. 1 oracle oinstall 483K Aug 10 2016 timezlrg_1.dat
-rw-r--r--. 1 oracle oinstall 768K Aug 10 2016 timezlrg_19.dat
-rw-r--r--. 1 oracle oinstall 761K Aug 10 2016 timezlrg_17.dat
-rw-r--r--. 1 oracle oinstall 783K Aug 10 2016 timezlrg_16.dat
-rw-r--r--. 1 oracle oinstall 773K Aug 10 2016 timezlrg_15.dat
-rw-r--r--. 1 oracle oinstall 765K Aug 10 2016 timezlrg_13.dat
-rw-r--r--. 1 oracle oinstall 768K Aug 10 2016 timezlrg_12.dat
-rw-r--r--. 1 oracle oinstall 769K Aug 10 2016 timezlrg_11.dat
-rw-r--r--. 1 oracle oinstall 344K Aug 10 2016 timezone_9.dat
-rw-r--r--. 1 oracle oinstall 281K Aug 10 2016 timezone_7.dat
-rw-r--r--. 1 oracle oinstall 280K Aug 10 2016 timezone_6.dat
-rw-r--r--. 1 oracle oinstall 280K Aug 10 2016 timezone_5.dat
-rw-r--r--. 1 oracle oinstall 765K Sep 28 2016 timezlrg_28.dat
-rw-r--r--. 1 oracle oinstall 334K Sep 28 2016 timezone_28.dat
-rw-r--r--. 1 oracle oinstall 770K Dec 5 2016 timezlrg_29.dat
-rw-r--r--. 1 oracle oinstall 334K Dec 5 2016 timezone_29.dat
-rw-r--r--. 1 oracle oinstall 333K May 3 2017 timezone_30.dat
-rw-r--r--. 1 oracle oinstall 768K May 3 2017 timezlrg_30.dat
-rw-r--r--. 1 oracle oinstall 333K Nov 6 2017 timezone_31.dat
-rw-r--r--. 1 oracle oinstall 769K Nov 6 2017 timezlrg_31.dat
-rw-r--r--. 1 oracle oinstall 52K Jun 14 2018 timezdif.csv
-rw-r--r--. 1 oracle oinstall 59K Jun 14 2018 readme.txt
-rw-r--r--. 1 oracle oinstall 333K Jun 20 2018 timezone_32.dat
-rw-r--r--. 1 oracle oinstall 769K Jun 20 2018 timezlrg_32.dat
-rw-r--r--. 1 oracle oinstall 400K Jul 14 2024 timezone_43.dat
-rw-r--r--. 1 oracle oinstall 400K Jul 14 2024 timezone_42.dat
-rw-r--r--. 1 oracle oinstall 397K Jul 14 2024 timezone_41.dat
-rw-r--r--. 1 oracle oinstall 407K Jul 14 2024 timezone_40.dat
-rw-r--r--. 1 oracle oinstall 407K Jul 14 2024 timezone_39.dat
-rw-r--r--. 1 oracle oinstall 411K Jul 14 2024 timezone_38.dat
-rw-r--r--. 1 oracle oinstall 411K Jul 14 2024 timezone_37.dat
-rw-r--r--. 1 oracle oinstall 411K Jul 14 2024 timezone_36.dat
-rw-r--r--. 1 oracle oinstall 411K Jul 14 2024 timezone_35.dat
-rw-r--r--. 1 oracle oinstall 414K Jul 14 2024 timezone_34.dat
-rw-r--r--. 1 oracle oinstall 416K Jul 14 2024 timezone_33.dat
-rw-r--r--. 1 oracle oinstall 923K Jul 14 2024 timezlrg_43.dat
-rw-r--r--. 1 oracle oinstall 923K Jul 14 2024 timezlrg_42.dat
-rw-r--r--. 1 oracle oinstall 921K Jul 14 2024 timezlrg_41.dat
-rw-r--r--. 1 oracle oinstall 955K Jul 14 2024 timezlrg_40.dat
-rw-r--r--. 1 oracle oinstall 970K Jul 14 2024 timezlrg_39.dat
-rw-r--r--. 1 oracle oinstall 1009K Jul 14 2024 timezlrg_38.dat
-rw-r--r--. 1 oracle oinstall 1009K Jul 14 2024 timezlrg_37.dat
-rw-r--r--. 1 oracle oinstall 1019K Jul 14 2024 timezlrg_36.dat
-rw-r--r--. 1 oracle oinstall 1018K Jul 14 2024 timezlrg_35.dat
-rw-r--r--. 1 oracle oinstall 1021K Jul 14 2024 timezlrg_34.dat
-rw-r--r--. 1 oracle oinstall 1021K Jul 14 2024 timezlrg_33.dat
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_43.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_42.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_41.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_40.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_39.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_38.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_37.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_36.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_35.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_34.txt
-rw-r--r--. 1 oracle oinstall 74K Jul 14 2024 readme_33.txt
drwxr-xr-x. 2 oracle oinstall 4.0K Oct 14 02:31 big
drwxr-xr-x. 2 oracle oinstall 4.0K Oct 14 02:31 little
[oracle@test zoneinfo]$
The current timezone file in use is version 32. The highest version available on disk is 43 (timezone_43.dat and timezlrg_43.dat), so that's the target version for this upgrade.
Step 2: Run the utltz_* Scripts to Upgrade the Timezone File
Oracle provides a set of scripts in $ORACLE_HOME/rdbms/admin/ to perform the upgrade safely, checking your data first before making any changes. These need to be run separately for the CDB and for each PDB, since timezone file versions are tracked independently at each level.
At the CDB:
First, utltz_countstats.sql and utltz_countstar.sql estimate how much timestamp-with-timezone (TSTZ) data exists in the database. This matters because the upgrade process has to rewrite every TSTZ value to the new rules, so more TSTZ data means a longer upgrade window.
[oracle@test admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 6 04:10:47 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Session altered.
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date - Owner.TableName.ColumnName - num_rows
17/04/2019 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
17/04/2019 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
17/04/2019 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
17/04/2019 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
17/04/2019 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
17/04/2019 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
17/04/2019 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
17/04/2019 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
17/04/2019 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
17/04/2019 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
17/04/2019 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
17/04/2019 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
17/04/2019 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
17/04/2019 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
17/04/2019 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
15/10/2024 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
15/10/2024 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
15/10/2024 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 84
17/04/2019 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
17/04/2019 - SYS.RADM_FPTM$.TSWTZ_COL - 1
17/04/2019 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
17/04/2019 - SYS.REG$.REG_TIME - 2
17/04/2019 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 29
17/04/2019 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
17/04/2019 - SYS.SCHEDULER$_JOB.END_DATE - 21
17/04/2019 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 21
17/04/2019 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 21
17/04/2019 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 21
17/04/2019 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 21
17/04/2019 - SYS.SCHEDULER$_JOB.START_DATE - 21
17/04/2019 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
17/04/2019 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
17/04/2019 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
17/04/2019 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
17/04/2019 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
17/04/2019 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
17/04/2019 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
17/04/2019 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
Total numrows of SYS TSTZ columns is : 70713
There are in total 169 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
17/04/2019 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
17/04/2019 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Session altered.
.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
.
For SYS tables first ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 1015
SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
SYS.RADM_FPTM$.TSWTZ_COL - 1
SYS.REG$.NTFN_GROUPING_START_TIME - 2
SYS.REG$.REG_TIME - 2
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 18
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
SYS.SCHEDULER$_JOB.END_DATE - 22
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 22
SYS.SCHEDULER$_JOB.LAST_END_DATE - 22
SYS.SCHEDULER$_JOB.LAST_START_DATE - 22
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 22
SYS.SCHEDULER$_JOB.START_DATE - 22
SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 18
SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 18
SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 18
SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
SYS.SCHEDULER$_WINDOW.END_DATE - 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
SYS.SCHEDULER$_WINDOW.START_DATE - 9
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 46
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 46
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 3213
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 3213
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 248
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 248
SYS.WRI$_OPTSTAT_OPR.END_TIME - 27
SYS.WRI$_OPTSTAT_OPR.SPARE6 - 27
SYS.WRI$_OPTSTAT_OPR.START_TIME - 27
SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 602
SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 602
SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 602
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 297
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 297
SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 6
SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 2
SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 2
SYS.XS$PRIN.END_DATE - 15
SYS.XS$PRIN.START_DATE - 15
Total count * of SYS TSTZ columns is : 11074
There are in total 169 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total count * of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/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 DSTv32 .
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 DSTv43 .
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> @$ORACLE_HOME/rdbms/admin/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 DSTv43 .
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 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 855638016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7598080 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 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 855638016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7598080 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
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
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 .
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>
SQL> exit
Note the workflow here: utltz_upg_check.sql only detects and prepares, it makes no actual changes. The real upgrade happens in utltz_upg_apply.sql, which restarts the database twice without asking for confirmation, once in upgrade mode to update SYS-owned data, and once in normal mode to update non-SYS data. Because of this, running utltz_upg_apply.sql is the point of no return in this process, make sure your backup is in place before running it.
At the PDB: repeat the same sequence of scripts inside the pluggable database. Note the CDB and PDB timezone versions are tracked independently, upgrading the CDB doesn't automatically upgrade its PDBs.
[oracle@test admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 6 04:15:33 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
43
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB2 READ WRITE NO
SQL> alter session set container=ORCLPDB2;
Session altered.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Session altered.
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date - Owner.TableName.ColumnName - num_rows
17/04/2019 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
17/04/2019 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
17/04/2019 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
17/04/2019 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
17/04/2019 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
...
...
Total numrows of SYS TSTZ columns is : 82193
There are in total 169 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
17/04/2019 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
17/04/2019 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
17/04/2019 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
17/04/2019 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Session altered.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
For SYS tables first ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 740
SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
SYS.RADM_FPTM$.TSWTZ_COL - 1
SYS.REG$.NTFN_GROUPING_START_TIME - 2
SYS.REG$.REG_TIME - 2
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 10
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
SYS.SCHEDULER$_JOB.END_DATE - 20
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 20
SYS.SCHEDULER$_JOB.LAST_END_DATE - 20
SYS.SCHEDULER$_JOB.LAST_START_DATE - 20
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 20
SYS.SCHEDULER$_JOB.START_DATE - 20
SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 10
SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 10
SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 10
SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
SYS.SCHEDULER$_WINDOW.END_DATE - 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
SYS.SCHEDULER$_WINDOW.START_DATE - 9
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 3319
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 3319
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 259
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 259
SYS.WRI$_OPTSTAT_OPR.END_TIME - 21
SYS.WRI$_OPTSTAT_OPR.SPARE6 - 21
SYS.WRI$_OPTSTAT_OPR.START_TIME - 21
SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 611
SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 611
SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 611
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 295
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 295
SYS.XS$PRIN.END_DATE - 15
SYS.XS$PRIN.START_DATE - 15
Total count * of SYS TSTZ columns is : 10889
There are in total 169 SYS TSTZ columns.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total count * of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/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 ORCLPDB2 .
INFO: Database RDBMS DST version is DSTv32 .
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 DSTv43 .
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> @$ORACLE_HOME/rdbms/admin/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 DSTv43 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is ORCLPDB2 .
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: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
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 .
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>
SQL> exit
Step 3: Bounce the Database and Confirm Both Levels Are Upgraded
Restart the database one final time, then confirm the timezone file version at both the CDB and PDB level shows the new version.
[oracle@test admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 6 04:28:19 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 855638016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7598080 bytes
Database mounted.
Database opened.
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
43
SQL> alter session set container=ORCLPDB2;
Session altered.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
43
SQL> exit
Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@test admin]$
Both the CDB and PDB now report version 43, confirming the timezone file upgrade completed successfully across the entire database.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Very nice Explanation in simple word
ReplyDeleteThanks
Deletenice
ReplyDelete