Oracle：ORA-39405——时区升级

异常

正常情况下，数据泵导入是可以跨版本的，但是此处却出现了异常

[oracle@prdeamdb OPatch]$ impdp \'/ as sysdba\' directory=ORACLE_BASE DUMPFILE=eam20240715.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 15 17:07:11 2024
Version 19.17.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition 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 40 into a target database with TSTZ version 32.


分析

19.17 的时区版本是 32，19.18 的时区版本是 40，所以此处应该升级

解决

升级 PSU

升级此处就不做赘述了，就写一下大体步骤

1. 升级 OPatch
2. 升级 PSU 至 19.18 版本

升级时区版本

1. 检查时区版本

   SQL> select * from v$timezone_file;

   FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0


2. 运行升级 check 脚本

   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 DSTv40 .
   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.


3. 执行时区的升级
   注意：此处会对数据库做重启操作，生产数据库需要根据情况而选择时段进行升级

   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 DSTv40 .
   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 6442448960 bytes
   Fixed Size 8939584 bytes
   Variable Size 989855744 bytes
   Database Buffers 5435817984 bytes
   Redo Buffers 7835648 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 6442448960 bytes
   Fixed Size 8939584 bytes
   Variable Size 989855744 bytes
   Database Buffers 5435817984 bytes
   Redo Buffers 7835648 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
   INFO: Total failures during update of TSTZ data: 0 .
   An upgrade window has been successfully ended.
   INFO: Your new Server RDBMS DST version is DSTv40 .
   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.


4. 验证升级结果

   SQL> select * from v$timezone_file;

   FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_40.dat 40 0


此时已经解决，再导入数据进行验证，已无报错，成功导入

[oracle@prdeamdb OPatch]$ impdp \'/ as sysdba\' directory=ORACLE_BASE DUMPFILE=eam20240715.dmp

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=ORACLE_BASE DUMPFILE=eam20240715.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
... ...