はじめに
Oracleデータインポートする際、ソースとターゲットのデータベースのTSTZ version 不一致の場合（ターゲット＜ソース）、ORA-39405エラー出ます。
例：
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32.
この問題を解決するため、ターゲットのデータベースのTSTZ versionをアップグレードする必要があります。
下記の環境でTSTZ version 32から34にアップグレードしてみました。
OS: Window
Oracle Database 19c Standard Edition 19.3.0.0.0
手順
１、patch適用する前のTZ_VERSIONバージョン確認
SQL> select tz_version from registry$database;
TZ_VERSION
----------
32
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
32
LATEST TIMEZONE VERSIONは32になっている。
２．PATCH ダウンロード、適用
今回はPatch 29997937を適用
※適用するpatch 番号につきまして、OracleのバージョンとTZ_VERSION合わせてOracle公式資料を確認してください。
Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
C:\p29997937_190000_MSWIN-x86-64\29997937>C:\oracle\WINDOWS.X64_193000_db_home\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : C:\oracle\WINDOW~1.X64
Central Inventory : C:\Program Files\Oracle\Inventory
from :
OPatch version : 12.2.0.1.15
OUI version : 12.2.0.7.0
Log file location : C:\oracle\WINDOW~1.X64\cfgtoollogs\opatch\opatch2021-10-20_05-34-04AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 29997937
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '29997937' to OH 'C:\oracle\WINDOW~1.X64'
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 29997937 successfully applied.
Log file location: C:\oracle\WINDOW~1.X64\cfgtoollogs\opatch\opatch2021-10-20_05-34-04AM_1.log
OPatch succeeded.
TZ_VERSIONバージョン確認
SQL> select tz_version from registry$database;
TZ_VERSION
----------
32
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
34
LATEST TIMEZONE VERSIONは32から34になった。
3．アップグレードtime zone data
utltz_* Scripts とDBMS_DST Package 二つの方法があります、今回utltz_* Scriptsを使います。
ORACLE_HOME/rdbms/adminディレクトリにある次のスクリプトを実行すれば、データベースのタイムゾーンデータをアップグレードできます。
utltz_upg_check.sql - 事前チェック用
utltz_upg_apply.sql - アップグレード用
C:>cd C:\oracle\WINDOWS.X64_193000_db_home\rdbms\admin
C:\oracle\WINDOWS.X64_193000_db_home\rdbms\admin>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 20 14:20:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.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.
INFO: There are no open PDBs .
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 DSTv34 .
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> @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 DSTv34 .
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.
INFO: There are no open PDBs .
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 9663675416 bytes
Fixed Size 9276440 bytes
Variable Size 1476395008 bytes
Database Buffers 8153726976 bytes
Redo Buffers 24276992 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 9663675416 bytes
Fixed Size 9276440 bytes
Variable Size 1476395008 bytes
Database Buffers 8153726976 bytes
Redo Buffers 24276992 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 DSTv34 .
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.
TZ_VERSIONバージョン確認
SQL> SELECT tz_version FROM registry$database;
TZ_VERSION
----------
34
無事にTZ_VERSIONを34にアップグレードできました。