I faced with that error during expdp&impdp process between 2 databases.

There was no any issue on expdp process. I copied those export files to new server. While I started to impdp command it fails those errors:

ORA-39097: Data Pump job encountered unexpected error -30094
ORA-39065: unexpected master process exception in DISPATCH
ORA-30094: failed to find the time zone data file for version 28 in $ORACLE_HOME/oracore/zoneinfo

So as you can see we need to upgrade on target database.

Here is the our steps:

  1. Check the latest version of the time zone file available in the Oracle home:

[oracle@hostname]$ cd $ORACLE_HOME/oracore/
[oracle@hostname oracore]$ ls
mesg zoneinfo

  1. Backup current zoneinfo file
    [oracle@hostname oracore]$ cp zoneinfo zoneinfo_old
  2. Check the status of the database:

[oracle@hostname zoneinfo]$ more $ORACLE_HOME/oracore/zoneinfo/timezdif.csv

File version 1.0
Fields: VERSION#, TIMEZONE_NAME, FROM_YEAR, TO_YEAR
Note: A NULL/blank for TO_YEAR means max year representable/allowed by db.
14, Africa/Casablanca, 2010,
14, Africa/Tunis, 2010,
14, America/Argentina/San_Luis, 2010,
14, America/Tijuana, 2010,
14, America/Santiago, 2010, 2010
14, America/Asuncion, 2010,
14, Antarctica/Casey, 2010,

Check also from sqlplus:

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
     WHERE PROPERTY_NAME LIKE 'DST_%'
     ORDER BY PROPERTY_NAME;

PROPERTY_NAME                 |VALUE
------------------------------|------------------------------
DST_PRIMARY_TT_VERSION        |14
DST_SECONDARY_TT_VERSION      |0
DST_UPGRADE_STATE             |NONE

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat              14          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
14
  1. Set related parameter

The recycle bin should be purged, and some session parameters set as per the following MOS notes:

Time Zone upgrade performed through DBUA takes more time in databases that have large amount of data impacted by new TZ files (Doc ID 2259734.1)
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

alter session set “_with_subquery”=materialize;
alter session set “_simple_view_merging”=TRUE;
purge dba_recyclebin;

  1. Start upgrade process
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(26);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.

SQL> shutdown immediate;

SQL> startup upgrade;
ORACLE instance started.

SQL> exec DBMS_DST.BEGIN_UPGRADE(28);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
     FROM DATABASE_PROPERTIES
     WHERE PROPERTY_NAME LIKE 'DST_%'
     ORDER BY PROPERTY_NAME;
  
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         28
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

--Check possibe errors here
SQL>  SELECT * FROM sys.dst$error_table;

no rows selected

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
     DBMS_DST.UPGRADE_DATABASE(:numfail,
     parallel                  => TRUE,
     log_errors                => TRUE,
     log_errors_table          => 'SYS.DST$ERROR_TABLE',
     log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
     error_on_overlap_time     => TRUE,
     error_on_nonexisting_time => TRUE);
     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
     END;
   /
 
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
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$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

--Check error again

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

-- Check timezone 
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_28.dat              28

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
      FROM DATABASE_PROPERTIES
      WHERE PROPERTY_NAME LIKE 'DST_%'
      ORDER BY PROPERTY_NAME;
  
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         28
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE
Reklam