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.

Import&Export, , , ,

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:

  1. 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. 🙂

Leave Comment

Your email address will not be published. Required fields are marked *