ORA-56935: existing datapump jobs are using a different version of time zone data file

I got ” ORA-56935: existing datapump jobs are using a different version of time zone data file ” error in Oracle database.

 

ORA-56935: existing datapump jobs are using a different version of time zone data file

Details of error are as follows.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 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
ORA-06512: at "SYS.DBMS_DST", line 1849
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1812
ORA-06512: at "SYS.KUPM$MCP", line 20452
ORA-06512: at "SYS.KUPM$MCP", line 13059
ORA-06512: at "SYS.KUPM$MCP", line 12435
ORA-06512: at "SYS.KUPM$MCP", line 15079
ORA-06512: at "SYS.KUPM$MCP", line 10315

 

 

This ORA-56935 error is related with the existing datapump jobs are using a different version of time zone data file.

 

Query the DST_UPGRADE_STATE as follows.

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

PROPERTY_NAME VALUE
---------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 18
DST_UPGRADE_STATE DATAPUMP(7)

 

 

 

Run the following 30090 EVENTS and then run exec dbms_dst.unload_secondary command 7 times until it to 0 as follows.

 

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.

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

PROPERTY_NAME VALUE
----------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 18
DST_UPGRADE_STATE DATAPUMP(6)




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.

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

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

PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 18
DST_UPGRADE_STATE DATAPUMP(2)

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

SQL> exec dbms_dst.unload_secondary;

PL/SQL procedure successfully completed.

SQL> r
1 SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4* ORDER BY PROPERTY_NAME

PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE



SQL> exec dbms_dst.unload_secondary;
BEGIN dbms_dst.unload_secondary; END;

*
ERROR at line 1:
ORA-56938: no secondary time zone data file being loaded by on-demand or a datapump job
ORA-06512: at "SYS.DBMS_DST", line 1969
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1906
ORA-06512: at line 1


SQL> r
1 SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4* ORDER BY PROPERTY_NAME

PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

 

 

 

Once DST_UPGRADE_STATE is NONE, you can run the import again as follows. it will work fine.

 

Import: Release 11.2.0.4.0 - Production on Wed Dec 15 21:22:06 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MSDBA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "MSDBA"."SYS_IMPORT_TABLE_01": MSDBA/******** directory=PUMP_DIR dumpfile=TEST_TABLE%U.dmp logfile=TEST_TABLE.log parallel=196 cluster=n exclude=statistics TABLES=ISIS.TEST_TABLE 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 25,914 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published.