Month: July 2019

Upgrading TimeZone error ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading

arcsdegeoLeave a comment

when you upgrade your database, there are post-upgrade actions….among them is upgrading the “time zone“, the following errors were thrown when i successfully upgraded database from 12cR1 to 18c:

ERROR at line 1:

ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1390

ORA-06512: at line 1

Solution:

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

 

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

arcsdegeoLeave a comment

while performing an import datapump (impdp) to an Oracle 18c database from 12cR2 database dump…..the following errors were thrown:

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 1855

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1818

ORA-06512: at “SYS.KUPM$MCP”, line 19693

ORA-06512: at “SYS.KUPM$MCP”, line 12495

ORA-06512: at “SYS.KUPM$MCP”, line 11882

ORA-06512: at “SYS.KUPM$MCP”, line 14507

ORA-06512: at “SYS.KUPM$MCP”, line 9794

SOLUTION:

sqlplus / as sysdba

SQL> SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value

FROM   DATABASE_PROPERTIES

WHERE  PROPERTY_NAME LIKE ‘DST_%’

ORDER  BY PROPERTY_NAME;

 

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

**** Important Remark: make sure DST_UPGRADE_STATE is set to “NONE“….you may need to execute the following multiple times: SQL> exec dbms_dst.unload_secondary;

 

Patching Oracle 18c database in Windows

arcsdegeo

Download the database patch from Oracle support

Also, download the latest OPatch utility for Windows OS as shown below:

Picture1

Check your environment variables:

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

set ORACLE_HOME=D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home

set PERL5LIB=

Shutdown Oracle database services:

Picture2

Stop Distributed Transaction Coordinator

net stop msdtc

Go to the directory where the patch is downloaded

cd D:\ORACLE_WINDOWS_PATCHING\29589622

D:\OPATCH_DOWNLOAD\OPatch\opatch apply

When you try to apply the patch the following  error will occur if the OPatch utility is NOT placed within the Oracle database home binary

Picture3

Error in Text: The Oracle Home D:\OPATCH is not OUI based home. Please give proper Oracle Home

after placing the OPatch utility under Oracle Home, apply the patch:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch apply

Picture4

To resolve this error I will rollback this patch first:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch rollback -id 28267731

Picture5

And attempting to re-apply the patch will succeed

Picture6

Startup the Oracle services again:

Picture7

Post-Patch activity:

sqlplus / as sysdba

SQL> startup

SQL> alter pluggable database all open;

exit;

cd D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch

datapatch -verbose

sqlplus / as sysdba

SQL> @?/rdbms/admin/utlrp.sql

SQL> select * from dba_registry_sqlpatch;

Picture8