报告概述

本篇文档对ORA-39405数据导入时区不兼容报错问题分析.

问题分析

报错现象

现象：用数据泵在2套Oracle 19c(19.18)导数据，导入时报了个错

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.

Mos资料

参考如下Mos文章
ORA-39405: Oracle Data Pump Does Not Support Importing From A Source Database With TSTZ Version (Doc ID 2793311.1)      



 Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Got below error while running import:
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 34
CHANGES
 Doing import with mismatch DST TZ version.
CAUSE
Oracle Data Pump does not support importing from a source database with TSTZ version 35 or 36 into a target database with TSTZ version 34.
OR
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.
This error is expected because the Oracle Data Pump does not support importing from a source database with higher TSTZ version into a target database with lower TSTZ version.

Applying the DSTv34 update for the Oracle Database (Doc ID 2602555.1)
Applying the DSTv36 update for the Oracle Database(Doc ID 2767770.1)
Applying the DSTv35 update for the Oracle Database(Doc ID 2676922.1)
for any latest DST patches, refer this document section C.1) Oracle RDBMS and OJVM DST-related notes
Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

阅读Mos可以发现这个ORA报错明确指向了Oracle 19+都存在的现象，及高版本的时区导出，导入低版本时区的库会报错不兼容，解决方案也说的很清楚及升级低版本时区的数据库。

19c时区补丁安装升级

安装软件补丁

#以oracle用户进入补丁主目录下后安装补丁

opatch apply

查看已安装补丁信息

cd $ORACLE_HOME/OPatch
opatch lsinventory
#补丁ID为34698179（若需卸载补丁需查明该补丁ID）

#通过查看时区信息文件夹可发现最新时区版本达到40（timezlrg_40.dat)

卸载补丁

#执行补丁回滚
opatch rollback -id 34698179     #34698179为所需卸载补丁ID

更新时区

#查询当前时区版本为32
select * from v$timezone_file;

#执行检测脚本
SQL>@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

报错：开启准备窗口失败，很大可能是共享池无法分配额外的存储用来执行DBMS_DST包，推荐刷新共享池或者重启数据库。
在alter system flush shared_pool或者重启数据库后仍然报错，于是尝试修改数据库内存大小：
alter system set sga_max_size=8192M scope=spfile;
alter system set sga_target=8192M scope=spfile;
测试环境存在这个问题，生产环境大概率内存远大于这个问题不会出现

报错原因：apply脚本修改时区只能在单节点模式下执行
解决：关闭集群模式 alter system set cluster_database=false scope=spfile;
#apply脚本执行成功

#脚本执行完成后查看当前时区版本
#时区版本成功从32更新至40

#修改回集群模式

Oracle 19c安装之后默认时区版本

思考，Oracle 19c安装DBCA图形化安装时区默认版本是多少？有没有可能也有遇到这种报错？如果是DBUA的情况？

升级time zine文件版本
echo "orcl:/u02/app/oracle/product/11.2.0/dbhome_1:N">>/etc/oratab
19c此升级脚本数据库自带
?/rdbms/admin/utltz_upg_check.sql    ---检查当前版本
@?/rdbms/admin/utltz_upg_apply.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 DSTv14 .
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 DSTv32 .
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> @?/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 DSTv32 .
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 1002437904 bytes
Fixed Size                  8903952 bytes
Variable Size             612368384 bytes
Database Buffers          373293056 bytes
Redo Buffers                7872512 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 1002437904 bytes
Fixed Size                  8903952 bytes
Variable Size             612368384 bytes
Database Buffers          373293056 bytes
Redo Buffers                7872512 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: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
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_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_DEBUG_MESSAGES2"
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_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_190200"."WWV_QS_RANDOM_NAMES"
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
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
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> SELECT version FROM v$timezone_file;

   VERSION
----------
        32
Applying the DSTv40 update for the Oracle Database (Doc ID 2904182.1)	
compatible 19.0.0

CDB手工安装了时区40之后,新建的pdb默认的时区是40版本，还是32？

新建pdb test3

PDB如何更新时区补丁

方法一、使用常规安装pdb同步cdb补丁命令 verbose --不好使

[oracle@rac1:/u01/app/oracle/product/19.3.0/dbhome_1/sqlpatch]$ datapatch -pdbs test1 -verbose
SQL Patching tool version 19.3.0.0.0 Production on Tue Mar 28 11:26:28 2023
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_108768_2023_03_28_11_26_28/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.3.0.0.0 Release_Update 190410122720: Installed
  PDB TEST1:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 22-FEB-23 05.51.05.720069 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: TEST1
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

SQL Patching tool complete on Tue Mar 28 11:26:51 2023

方法二、使用和cdb执行的时区补丁sql脚本 -可以成功

#执行apply脚本
SQL>@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

结论及建议

1.建议对于19c的运维场景，compatible ，时区版本，都选择一个统一的标准，所有的19c都可以使用时区40的版本，但是尽量避免一部分使用默认版本32，一部分手工升级之后时区版本使用40，从而导致高版本导出，低版本数据泵导入报错！
2.时区的补丁更新，对于RAC来说最后的应用的动作只能单节点执行，说明是会影像业务的，建议测试后在执行时区补丁的升级！！！