在oracle 19c数据库中使用expdp，impdp时，提示
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 42. 。查询oracle mos文档 发现这个ORA报错明确指向了Oracle 19+都存在的现象，及高版本的时区导出，导入低版本时区的库会报错不兼容，解决方案也说的很清楚及升级低版本时区的数据库。
[oracle@db-1 ~]$ impdp '"/ as sysdba"' directory=DATA_PUMP_DIR dumpfile=20251106001.dump,20251106002.dump,20251106003.dump logfile=imp20251106.log Import: Release 19.0.0.0.0 - Production on Fri Nov 7 13:55:22 2025 Version 19.25.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 43 into a target database with TSTZ version 42.
19c时区补丁安装升级
安装软件补丁
#以oracle用户进入补丁主目录下后安装补丁，升级到19.25版本
[oracle@db-1 36912597]$ ../opatch apply Oracle Interim Patch Installer version 12.2.0.1.48 Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /app/oracle/product/19.0.0/dbhome_1 Central Inventory : /app/oraInventory from : /app/oracle/product/19.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.48 OUI version : 12.2.0.7.0 Log file location : /app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-11-06_21-03-34PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 36912597 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/app/oracle/product/19.0.0/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '36912597' to OH '/app/oracle/product/19.0.0/dbhome_1' ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ] not present in the Oracle Home or a higher version is found. Patching component oracle.rdbms, 19.0.0.0.0... Patching component oracle.rdbms.util, 19.0.0.0.0... Patching component oracle.rdbms.rsf, 19.0.0.0.0... Patching component oracle.assistants.acf, 19.0.0.0.0... Patching component oracle.assistants.deconfig, 19.0.0.0.0... Patching component oracle.assistants.server, 19.0.0.0.0... Patching component oracle.blaslapack, 19.0.0.0.0... Patching component oracle.buildtools.rsf, 19.0.0.0.0... Patching component oracle.ctx, 19.0.0.0.0... Patching component oracle.dbdev, 19.0.0.0.0... Patching component oracle.dbjava.ic, 19.0.0.0.0... Patching component oracle.dbjava.jdbc, 19.0.0.0.0... Patching component oracle.dbjava.ucp, 19.0.0.0.0... Patching component oracle.duma, 19.0.0.0.0... Patching component oracle.javavm.client, 19.0.0.0.0... Patching component oracle.ldap.owm, 19.0.0.0.0... Patching component oracle.ldap.rsf, 19.0.0.0.0... Patching component oracle.ldap.security.osdt, 19.0.0.0.0... Patching component oracle.marvel, 19.0.0.0.0... Patching component oracle.network.rsf, 19.0.0.0.0... Patching component oracle.odbc.ic, 19.0.0.0.0... Patching component oracle.ons, 19.0.0.0.0... Patching component oracle.ons.ic, 19.0.0.0.0... Patching component oracle.oracore.rsf, 19.0.0.0.0... Patching component oracle.perlint, 5.28.1.0.0... Patching component oracle.precomp.common.core, 19.0.0.0.0... Patching component oracle.precomp.rsf, 19.0.0.0.0... Patching component oracle.rdbms.crs, 19.0.0.0.0... Patching component oracle.rdbms.dbscripts, 19.0.0.0.0... Patching component oracle.rdbms.deconfig, 19.0.0.0.0... Patching component oracle.rdbms.oci, 19.0.0.0.0... Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0... Patching component oracle.rdbms.scheduler, 19.0.0.0.0... Patching component oracle.rhp.db, 19.0.0.0.0... Patching component oracle.rsf, 19.0.0.0.0... Patching component oracle.sdo, 19.0.0.0.0... Patching component oracle.sdo.locator.jrf, 19.0.0.0.0... Patching component oracle.sqlplus, 19.0.0.0.0... Patching component oracle.sqlplus.ic, 19.0.0.0.0... Patching component oracle.wwg.plsql, 19.0.0.0.0... Patching component oracle.xdk.rsf, 19.0.0.0.0... Patching component oracle.javavm.server, 19.0.0.0.0... Patching component oracle.xdk.xquery, 19.0.0.0.0... Patching component oracle.ctx.rsf, 19.0.0.0.0... Patching component oracle.ovm, 19.0.0.0.0... Patching component oracle.oraolap, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf.lbuilder, 19.0.0.0.0... Patching component oracle.rdbms.rat, 19.0.0.0.0... Patching component oracle.ldap.rsf.ic, 19.0.0.0.0... Patching component oracle.rdbms.dv, 19.0.0.0.0... Patching component oracle.xdk, 19.0.0.0.0... Patching component oracle.mgw.common, 19.0.0.0.0... Patching component oracle.ldap.client, 19.0.0.0.0... Patching component oracle.install.deinstalltool, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.oraolap.api, 19.0.0.0.0... Patching component oracle.dbtoolslistener, 19.0.0.0.0... Patching component oracle.rdbms.drdaas, 19.0.0.0.0... Patching component oracle.rdbms.hs_common, 19.0.0.0.0... Patching component oracle.rdbms.lbac, 19.0.0.0.0... Patching component oracle.sdo.locator, 19.0.0.0.0... Patching component oracle.rdbms.dm, 19.0.0.0.0... Patching component oracle.ldap.ssl, 19.0.0.0.0... Patching component oracle.xdk.parser.java, 19.0.0.0.0... Patching component oracle.odbc, 19.0.0.0.0... Patching component oracle.network.listener, 19.0.0.0.0... Patching component oracle.ctx.atg, 19.0.0.0.0... Patching component oracle.rdbms.install.common, 19.0.0.0.0... Patching component oracle.rdbms.hsodbc, 19.0.0.0.0... Patching component oracle.network.aso, 19.0.0.0.0... Patching component oracle.rdbms.locator, 19.0.0.0.0... Patching component oracle.rdbms.install.plugins, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0... Patching component oracle.oraolap.dbscripts, 19.0.0.0.0... Patching component oracle.network.client, 19.0.0.0.0... Patching component oracle.precomp.common, 19.0.0.0.0... Patching component oracle.precomp.lang, 19.0.0.0.0... Patching component oracle.jdk, 1.8.0.201.0... Patch 36912597 successfully applied. Sub-set patch [35943157] has become inactive due to the application of a super-set patch [36912597]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-11-06_21-03-34PM_1.log OPatch succeeded.
补丁检查./datapatch -sanity_checks
[oracle@db-1 OPatch]$ ./datapatch -sanity_checks SQL Patching sanity checks version 19.25.0.0.0 on Thu 06 Nov 2025 09:08:36 PM CST Copyright (c) 2021, 2025, Oracle. All rights reserved. Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20251106_210836_22419/sanity_checks_20251106_210836_22419.log Running checks JSON report generated in /app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20251106_210836_22419/sqlpatch_sanity_checks_summary.json file Checks completed. Printing report: Check: Database component status - OK Check: PDB Violations - OK Check: Invalid System Objects - OK Check: Tablespace Status - OK Check: Backup jobs - OK Check: Temp file exists - OK Check: Temp file online - OK Check: Data Pump running - OK Check: Container status - OK Check: Oracle Database Keystore - OK Check: Dictionary statistics gathering - OK Check: Scheduled Jobs - WARNING Execution of scheduler jobs while database patching is running may lead to failures and/or performance issues. There are jobs currently running or scheduled to be executed during next hour. Consider patching the database when jobs are not running and will not be scheduled to run during patching. To check for jobs that are running or scheduled to run: SELECT owner as schema_name, job_name, state, next_run_date FROM sys.all_scheduler_jobs WHERE state = 'RUNNING' UNION SELECT owner as schema_name, job_name, state, next_run_date FROM sys.all_scheduler_jobs WHERE state = 'SCHEDULED' and cast(next_run_date as date) > sysdate and cast(next_run_date as date) < sysdate + 1/24; hwlisogg: | JOB_NAME | NEXT_RUN_DATE | SCHEMA_NAME | STATE | |--------------------------+-------------------------------------+-------------+-----------| | CLEANUP_ONLINE_IND_BUILD | 06-NOV-25 09.49.08.716029 PM +08:00 | SYS | SCHEDULED | |--------------------------+-------------------------------------+-------------+-----------| | CLEANUP_ONLINE_PMO | 06-NOV-25 09.49.48.739658 PM +08:00 | SYS | SCHEDULED | |--------------------------+-------------------------------------+-------------+-----------| | CLEANUP_TAB_IOT_PMO | 06-NOV-25 09.49.18.721360 PM +08:00 | SYS | SCHEDULED | |--------------------------+-------------------------------------+-------------+-----------| | CLEANUP_TRANSIENT_PKG | 06-NOV-25 09.49.38.000000 PM +08:00 | SYS | SCHEDULED | |--------------------------+-------------------------------------+-------------+-----------| Check: GoldenGate triggers - OK Check: Logminer DDL triggers - OK Check: Check sys public grants - OK Check: Statistics gathering running - OK Check: Optim dictionary upgrade parameter - OK Check: Symlinks on oracle home path - OK Check: Central Inventory - OK Check: Queryable Inventory dba directories - OK Check: Queryable Inventory locks - OK Check: Queryable Inventory package - OK Check: Queryable Inventory external table - OK Check: Imperva processes - OK Check: Guardium processes - OK Check: Locale - OK Refer to MOS Note 2680521.1 and debug log /app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20251106_210836_22419/sanity_checks_debug_20251106_210836_22419.log SQL Patching sanity checks completed on Thu 06 Nov 2025 09:09:01 PM CST [oracle@zht-lis-sync-db-1 OPatch]$
补丁信息输出
[oracle@db-1 OPatch]$ ./datapatch -verbose SQL Patching tool version 19.25.0.0.0 Production on Thu Nov 6 21:10:23 2025 Copyright (c) 2012, 2024, Oracle. All rights reserved. Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sqlpatch_23735_2025_11_06_21_10_23/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done 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.25.0.0.0 Release_Update 241010184253: Installed SQL registry: Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 05-MAR-24 05.54.29.719252 PM Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back Patch 36912597 (Database Release Update : 19.25.0.0.241015 (36912597)): Apply from 19.22.0.0.0 Release_Update 240104023954 to 19.25.0.0.0 Release_Update 241010184253 No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 1 Validating logfiles...done Patch 36912597 apply: SUCCESS logfile: /app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_HWLISOGG_2025Nov06_21_10_53.log (no errors) SQL Patching tool complete on Thu Nov 6 21:11:53 2025 [oracle@zht-lis-sync-db-1 OPatch]$
此时，虽然补丁打成功了。导入还是报错，原因是因为时区没有升级上去。需要下面的步骤升级时区。 查看目前时区版本
[oracle@db-1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 7 13:50:32 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_42.dat 42 0
执行时区检查脚本 SQL>@$ORACLE_HOME/rdbms/admin/utltz_upg_check.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 DSTv42 . 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>
执行时区升级脚本@?/rdbms/admin/utltz_upg_apply.sql 注意：执行改脚本时，会重启数据库实例，因此生产环境要根据自己的业务 时间来进行协调执行。
[oracle@db-1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 7 17:16:09 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 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 DSTv43 . 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 4.2950E+10 bytes Fixed Size 32615960 bytes Variable Size 1.1274E+10 bytes Database Buffers 3.1541E+10 bytes Redo Buffers 101601280 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 4.2950E+10 bytes Fixed Size 32615960 bytes Variable Size 1.1274E+10 bytes Database Buffers 3.1541E+10 bytes Redo Buffers 101601280 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_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GGADMIN"."AQ$_QT$_OGG$RTEST_1_L" Number of failures: 0 Table list: "GGADMIN"."AQ$_QT$_OGG$RTEST_1_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 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> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_43.dat 43 0 1 row selected.
这样时区版本就升级成功了。再次进行impdp导入就不会有问题了。
impdp '"/ as sysdba"' directory=DATA_PUMP_DIR dumpfile=20251107001.dump,20251107002.dump,20251107003.dump logfile=imp20251107.log ;;; Import: Release 19.0.0.0.0 - Production on Fri Nov 7 18:06:39 2025 Version 19.25.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 Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=20251107001.dump,20251107002.dump,20251107003.dump logfile=imp20251107.log Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."LIANSRESULT" 34.17 GB 1083947474 rows . . imported "TEST"."LP_NTERFACE" 21.01 GB 569511489 rows . . imported "TEST"."LIILLINFO" 8.421 GB 463098861 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" completed with 0 error(s) at Sat Nov 8 00:46:28 2025 elapsed 0 06:39:49
