==============
Issue
==============
During import from oracle 19.22 to oracle 19.10, following error came during import.
Apparently, even if Oracle servers are of same major version, an export file from higher TZ VERSION database cannot be imported into lower TZ_VERSION database.
Once updated, the import was successfull.
Import: Release 19.0.0.0.0 - Production on Wed Dec 4 17:03:27 2024
Version 19.10.2.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 42 into a target database with TSTZ version 32.
==============
Check Current Time Zone Version
The V$TIMEZONE_FILE view displays the zone file version being used by the database.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
32
SQL> SELECT * FROM V$VERSION;
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.10.2.0.0
==============
Solution
==============
Step A - apply patch 35220732, to upgrade TZ VERSION to version 42.
Step A - apply patch 35220732, to upgrade to version 42
mkdir patches
mv p35220732_190000_Linux-x86-64.zip patches
unzip p35220732_190000_Linux-x86-64.zip
cd 35220732
$ORACLE_HOME/OPatch/opatch apply - report
$ORACLE_HOME/OPatch/opatch apply
oracle@myhost:~/patches/35220732>% $ORACLE_HOME/OPatch/opatch apply -report
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2024, Oracle Corporation. All rights reserved.
Oracle Home : /software/oracle/1910
Central Inventory : /software/oracle/oraInventory
from : /software/oracle/1910/oraInst.loc
OPatch version : 12.2.0.1.25
OUI version : 12.2.0.7.0
Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-12-05_09-33-33AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 35220732
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
You are calling OPatch with -ocmrf option while this OPatch is generic, not beine calling OPatch.
Backing up files...
Applying interim patch '35220732' to OH '/software/oracle/1910'
Users request no RAC file generation. Do not create MP files.
Skip patching component oracle.oracore.rsf, 19.0.0.0.0 and its actions.
The actions are reported here, but are not performed.
ApplySession skipping inventory update.
Patch 35220732 successfully applied.
Log file location: /software/oracle/1910/cfgtoollogs/opatch/opatch2024-12-05_09-33-33AM_1.log
OPatch succeeded.
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
42
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
Even after applying the patch, the v$timezone_file is still not updated.
ls -ltr $ORACLE_HOME/oracore/zoneinfo/
-rw-r--r-- 1 oracle dba 779003 Aug 10 2016 timezlrg_17.dat
-rw-r--r-- 1 oracle dba 800913 Aug 10 2016 timezlrg_16.dat
-rw-r--r-- 1 oracle dba 791476 Aug 10 2016 timezlrg_15.dat
-rw-r--r-- 1 oracle dba 782475 Aug 10 2016 timezlrg_13.dat
-rw-r--r-- 1 oracle dba 785621 Aug 10 2016 timezlrg_12.dat
-rw-r--r-- 1 oracle dba 787272 Aug 10 2016 timezlrg_11.dat
-rw-r--r-- 1 oracle dba 351525 Aug 10 2016 timezone_9.dat
-rw-r--r-- 1 oracle dba 286815 Aug 10 2016 timezone_7.dat
-rw-r--r-- 1 oracle dba 286217 Aug 10 2016 timezone_6.dat
-rw-r--r-- 1 oracle dba 286310 Aug 10 2016 timezone_5.dat
-rw-r--r-- 1 oracle dba 782585 Sep 28 2016 timezlrg_28.dat
-rw-r--r-- 1 oracle dba 341401 Sep 28 2016 timezone_28.dat
-rw-r--r-- 1 oracle dba 788462 Dec 5 2016 timezlrg_29.dat
-rw-r--r-- 1 oracle dba 341401 Dec 5 2016 timezone_29.dat
-rw-r--r-- 1 oracle dba 340884 May 2 2017 timezone_30.dat
-rw-r--r-- 1 oracle dba 785841 May 2 2017 timezlrg_30.dat
-rw-r--r-- 1 oracle dba 340892 Nov 6 2017 timezone_31.dat
-rw-r--r-- 1 oracle dba 786708 Nov 6 2017 timezlrg_31.dat
-rw-r--r-- 1 oracle dba 52931 Jun 14 2018 timezdif.csv
-rw-r--r-- 1 oracle dba 59574 Jun 14 2018 readme.txt
-rw-r--r-- 1 oracle dba 340869 Jun 19 2018 timezone_32.dat
-rw-r--r-- 1 oracle dba 786909 Jun 19 2018 timezlrg_32.dat
-rw-r--r-- 1 oracle dba 408795 Apr 14 2023 timezone_42.dat
-rw-r--r-- 1 oracle dba 944613 Apr 14 2023 timezlrg_42.dat
-rw-r--r-- 1 oracle dba 74717 Apr 14 2023 readme_42.txt
Step B - Implement steps described in Datetime Data Types and Time Zone Support, 4.7.2 Upgrading the Time Zone Data Using the utltz_* Scripts section
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
04/12/2024 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
04/12/2024 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
04/12/2024 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.CREATION_TIME - 1
04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.DELETION_TIME - 1
04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.MODIFICATION_TIME - 1
20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
20/10/2021 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 2
04/12/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
04/12/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
04/12/2024 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
04/12/2024 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
04/12/2024 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
04/12/2024 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
04/12/2024 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
04/12/2024 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 1
04/12/2024 - SYS.KET$_CLIENT_TASKS.LG_DATE - 1
04/12/2024 - SYS.KET$_CLIENT_TASKS.LT_DATE - 1
04/12/2024 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
04/12/2024 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
04/12/2024 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 163860
04/12/2024 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
20/10/2021 - SYS.RADM_FPTM$.TSWTZ_COL - 1
04/12/2024 - SYS.REG$.NTFN_GROUPING_START_TIME - 4
04/12/2024 - SYS.REG$.REG_TIME - 4
04/12/2024 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 2668
04/12/2024 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
04/12/2024 - SYS.SCHEDULER$_JOB.END_DATE - 29
04/12/2024 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 29
04/12/2024 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 29
04/12/2024 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 29
04/12/2024 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 29
04/12/2024 - SYS.SCHEDULER$_JOB.START_DATE - 29
02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 1372
02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 1372
02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 1372
21/10/2021 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
21/10/2021 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
04/12/2024 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
04/12/2024 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
04/12/2024 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
04/12/2024 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
04/12/2024 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
04/12/2024 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 30
03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 30
03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 30
04/12/2024 - SYS.STATS_TARGET$.END_TIME - 2839
04/12/2024 - SYS.STATS_TARGET$.START_TIME - 2839
04/12/2024 - SYS.TAB_STATS$.SPARE6 - 1179
04/12/2024 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 3
04/12/2024 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 3
04/12/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 277020
04/12/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 277020
04/12/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 25069
04/12/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 25069
04/12/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 9589
04/12/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 9589
04/12/2024 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 1005
04/12/2024 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1005
04/12/2024 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 1005
04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 38636
04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 38636
04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 38636
04/12/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 16324
04/12/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 16324
04/12/2024 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 2
04/12/2024 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 213
04/12/2024 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 213
27/05/2024 - SYS.XS$PRIN.END_DATE - 15
27/05/2024 - SYS.XS$PRIN.START_DATE - 15
Total numrows of SYS TSTZ columns is : 953487
There are in total 166 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
1
20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
20/10/2021 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
20/10/2021 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 8
There are in total 17 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL> @./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: 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 DSTv42 .
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>
The utltz_upg_apply.sql script automatically restarts the database multiple times during its execution.
SQL> @./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 DSTv42 .
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 8589930592 bytes
Fixed Size 8917088 bytes
Variable Size 6174015488 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7856128 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.
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 DSTv42 .
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>
The TZ_VERSION column in the REGISTRY$DATABASE and v$timezone_file now gets updated with the new time zone version.
SQL> SELECT * FROM REGISTRY$DATABASE;
PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION
----------- -------------------- ------------- ----------
13 Linux x86 64-bit SE2 42
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
42
