IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 )
