After Database upgrade to 12c, now getting errors during datapump import due to different version of time zone datafiles used for exisiting DP jobs.

Error:

Import: Release 12.2.0.1.0 - Production on Wed Jun 28 14:57:07 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-56935: existing datapump jobs are using a different version of time zone data file

Soultion:

Check the value of DST_UPGRADE_STATE by the following query.

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    26
DST_SECONDARY_TT_VERSION  14
DST_UPGRADE_STATE         DATAPUMP(1)

Then try executing the following.

SQL>  ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';
Session altered.

SQL> exec dbms_dst.unload_secondary;
PL/SQL procedure successfully completed.

Now re-execute the query.

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   26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE        NONE

Now run the import job and it’ll work fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s