I got this error while trying to perform an import from another 19c database but apparently with a higher TZ version:
dbserver:orcl> impdp \'/ as sysdba\' dumpfile=imp_file.dmp EXCLUDE=STATISTICS table_exists_action=replace logfile=imp_file.log Import: Release 19.0.0.0.0 - Production on Mon Apr 19 10:12:04 2021 Version 19.8.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 32 into a target database with TSTZ version 31.
The solution is to perform the TZ upgrade of the database.
Check the current time zone version:
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_31.dat 31 0
Let’s see what’s the latest time zone to which we can upgrade to:
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual; 2 GET_LATEST_TIMEZONE_VERSION --------------------------- 32 SQL>
We can see that the latest version is 32. Let’s start the upgrade:
- Start the database in upgrade mode:
QL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP UPGRADE; ORACLE instance started. Total System Global Area 4294921672 bytes Fixed Size 9063880 bytes Variable Size 2684354560 bytes Database Buffers 1593835520 bytes Redo Buffers 7667712 bytes Database mounted. Database opened. SQL>
2. Prepare for the upgrade:
SQL> DECLARE l_tz_version PLS_INTEGER; BEGIN l_tz_version := DBMS_DST.get_latest_timezone_version; DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version); DBMS_DST.begin_prepare(l_tz_version); END; / PL/SQL procedure successfully completed.
3. Check the upgrade status
SQL> COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A20 SELECT property_name, property_value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------- DST_PRIMARY_TT_VERSION 31 DST_SECONDARY_TT_VERSION 32 DST_UPGRADE_STATE PREPARE
4. Find the affected tables by this upgrade:
SQL> EXEC DBMS_DST.find_affected_tables; PL/SQL procedure successfully completed. SQL> select count(*) from sys.dst$affected_tables; COUNT(*) ---------- 0 SQL> select * from sys.dst$error_table; no rows selected
5. Now we can end the prepare phase:
SQL> EXEC DBMS_DST.end_prepare; PL/SQL procedure successfully completed. SQL>
6. Start the upgrade:
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP UPGRADE; ORACLE instance started. Total System Global Area 4294921672 bytes Fixed Size 9063880 bytes Variable Size 2684354560 bytes Database Buffers 1593835520 bytes Redo Buffers 7667712 bytes Database mounted. Database opened. SQL> SQL> SQL> SET SERVEROUTPUT ON DECLARE l_tz_version PLS_INTEGER; BEGIN SELECT DBMS_DST.get_latest_timezone_version INTO l_tz_version FROM dual; DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version); DBMS_DST.begin_upgrade(l_tz_version); END; /SQL> 2 3 4 5 6 7 8 9 10 11 l_tz_version=32 An upgrade window has been successfully started. PL/SQL procedure successfully completed.
7. Bounce the database
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP; ORACLE instance started. Total System Global Area 4294921672 bytes Fixed Size 9063880 bytes Variable Size 2684354560 bytes Database Buffers 1593835520 bytes Redo Buffers 7667712 bytes Database mounted. Database opened.
8. Finish the upgrade:
SQL> SET SERVEROUTPUT ON DECLARE l_failures PLS_INTEGER; BEGIN DBMS_DST.upgrade_database(l_failures); DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures); DBMS_DST.end_upgrade(l_failures); DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures); END; / 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: "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_EVENT_BUS_TABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_EVENT_BUS_TABLE_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_NOTIFY_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_NOTIFY_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."EM_SCHED_JOB_REGISTRY_E" Number of failures: 0 Table list: "SYSMAN"."EM_SCHED_JOB_REG_HISTORY_E" Number of failures: 0 Table list: "SYSMAN"."EM_AUC_AGT_JOB_INFO_E" Number of failures: 0 Table list: "SYSMAN"."EM_AUC_PREREQ_DUMP_E" Number of failures: 0 Table list: "SYSMAN"."EM_GI_AGT_JOB_INFO_E" Number of failures: 0 Table list: "SYSMAN"."EM_GI_AGENT_COMPLIANCE_E" Number of failures: 0 Table list: "SYSMAN"."EM_BLACKOUTS_NG_E" Number of failures: 0 Table list: "SYSMAN"."EM_BLACKOUT_SCHEDULE_E" Number of failures: 0 Table list: "SYSMAN"."EM_BLACKOUT_WINDOWS_E" Number of failures: 0 Table list: "SYSMAN"."EM_BLACKOUT_TARGET_STATE_E" Number of failures: 0 Table list: "SYSMAN"."EM_BLACKOUT_OCCURRENCES_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_CCR_LAST_MAPPER_RUN_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_CCR_REG_CIPHER_E" Number of failures: 0 Table list: "SYSMAN"."EM_FAVORITES_E" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_JOB_STATUS_UPD_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_JOB_STATUS_UPD_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."EM_LOADERJOB_TARGETS_E" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_L" Number of failures: 0 Table list: "SYSMAN"."MGMT_SYSTEM_ERROR_LOG_E" Number of failures: 0 Table list: "SYSMAN"."EM_PROXY_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_DHCPSERVER_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_RPM_REP_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG_E" Number of failures: 0 Table list: "SYSMAN"."MGMT_PROV_IP_RANGE_E" Number of failures: 0 Table list: "SYSMAN"."EM_PROV_DEPLOYEDIMAGE_E" Number of failures: 0 Table list: "SYSMAN"."EM_PROV_DISKLESSIMAGE_E" Number of failures: 0 Table list: "SYSMAN"."EM_PROV_DEPLOYMENT_PLAN_E" Number of failures: 0 Table list: "SYSMAN"."EM_SERVICECUSTDASHBOARD_E" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_CNTR_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_CNTR_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."JAM_COLLECTION_TRACE_E" Number of failures: 0 Table list: "SYSMAN"."EM_OFFLINE_DC_DETAILS_E" Number of failures: 0 Table list: "SYSMAN"."EM_OFFLINE_DC_DTYPE_LOG_E" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_PC_TP_TABLE_S" Number of failures: 0 Table list: "SYSMAN"."AQ$_EM_PC_TP_TABLE_L" Number of failures: 0 DBMS_DST.upgrade_database : l_failures=0 An upgrade window has been successfully ended. DBMS_DST.end_upgrade : l_failures=0 PL/SQL procedure successfully completed.
9. Check the time zone:
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_32.dat 32 0 SQL> COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A20 SELECT property_name, property_value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------- DST_PRIMARY_TT_VERSION 32 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL> exit
Now I can perform the import. 🙂