Oracle OJVM PSU Error

i am sharing a strange error faced while patching Oracle 12cR1 database on Linux with JAVA component (Please note that i am using OPatch utility version 12.2.0.1.14)

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files…

Applying interim patch ‘28440711’ to OH ‘/app/oracle/product/12.1.0.2’

ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.

ApplySession failed but Oracle Home has not been modified.null

UtilSession failed: ApplySession failed but Oracle Home has not been modified.null

Log file location: /app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-10-26_00-39-02AM_1.log

OPatch failed with error code 73

opening the log file:

cat /app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-10-26_00-39-02AM_1.log

[Oct 26, 2018 12:39:03 AM] [INFO] CUP_LOG: Trying to load HomeOperations object

[Oct 26, 2018 12:39:03 AM] [INFO] CUP_LOG: HomeOperations provider not available

[Oct 26, 2018 12:39:03 AM] [INFO] CUP_LOG: This is unusual state. HomeOperations object is null, but it should not be null if HomeOperations create went thru. CUP is not available

[Oct 26, 2018 12:39:03 AM] [INFO] OPatch invoked as follows: ‘apply -invPtrLoc /app/oracle/product/12.1.0.2/oraInst.loc ‘

[Oct 26, 2018 12:39:03 AM] [INFO] Runtime args: [-Xms150m, -Xmx3072m, -XX:MaxMetaspaceSize=128M, -XX:+HeapDumpOnOutOfMemoryError, -XX:HeapDumpPath=/app/oracle/product/12.1.0.2/cfgtoollogs/opatch, -DOPatch.ORACLE_HOME=/app/oracle/product/12.1.0.2, -DOPatch.DEBUG=false, -DOPatch.MAKE=false, -DOPatch.RUNNING_DIR=//app/oracle/product/12.1.0.2/OPatch, -DOPatch.MW_HOME=, -DOPatch.WL_HOME=, -DOPatch.COMMON_COMPONENTS_HOME=, -DOPatch.OUI_LOCATION=/app/oracle/product/12.1.0.2/oui, -DOPatch.FMW_COMPONENT_HOME=, -DOPatch.OPATCH_CLASSPATH=, -DOPatch.WEBLOGIC_CLASSPATH=, -DOPatch.SKIP_OUI_VERSION_CHECK=, -DOPatch.PARALLEL_ON_FMW_OH=]

[Oct 26, 2018 12:39:03 AM] [INFO] Heap in use : 23 MB

Total memory: 144 MB

Free memory : 120 MB

Max memory : 2731 MB

[Oct 26, 2018 12:39:03 AM] [INFO] Oracle Home : /app/oracle/product/12.1.0.2

Central Inventory : /app/oracle/product/oraInventory

from : /app/oracle/product/12.1.0.2/oraInst.loc

OPatch version : 12.2.0.1.14

OUI version : 12.1.0.2.0

OUI location : /app/oracle/product/12.1.0.2/oui

Log file location : /app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-10-26_00-39-02AM_1.log

[Oct 26, 2018 12:39:03 AM] [INFO] Patch history file: /app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch_history.txt

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-TIME] Loading raw inventory

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-MEMORY] Loaded all components from inventory. Heap memory in use: 22 (MB)

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-MEMORY] Loaded all one offs from inventory. Heap memory in use: 22 (MB)

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-TIME] Raw inventory loaded successfully

[Oct 26, 2018 12:39:05 AM] [INFO] NApply::no CAS enabled, OPatch runs with legacy process.

[Oct 26, 2018 12:39:05 AM] [INFO] Verifying environment and performing prerequisite checks…

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-TIME] Running prerequisite checks

[Oct 26, 2018 12:39:05 AM] [INFO] opatch-external.jar is in /app/oracle/product/12.1.0.2/OPatch/jlib/opatch-external.jar

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-TIME] Loading cooked inventory

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-MEMORY] : Loading cooked one offs. Heap memory used 29 (MB)

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-MEMORY] : Loaded cooked oneoffs. Heap memory used : 25 (MB)

[Oct 26, 2018 12:39:05 AM] [INFO] [OPSR-TIME] Cooked inventory loaded successfully

[Oct 26, 2018 12:39:05 AM] [INFO] Checking conflict among patches…

[Oct 26, 2018 12:39:05 AM] [INFO] Running prereq checkConflictAmongPatchesWithDetail

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches can be applied: 28440711

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches are not required:

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches are auto rollbackable:

[Oct 26, 2018 12:39:05 AM] [INFO] Finished checking prereq checkConflictAmongPatchesWithDetail

[Oct 26, 2018 12:39:05 AM] [INFO] Conflict checking amongst patches PASSED

[Oct 26, 2018 12:39:05 AM] [INFO] Checking if Oracle Home has components required by patches…

[Oct 26, 2018 12:39:05 AM] [INFO] CheckMissingComps: Cached file does not exist or is invalid, re-build prereq result.

[Oct 26, 2018 12:39:05 AM] [INFO] Checking conflicts against Oracle Home…

[Oct 26, 2018 12:39:05 AM] [INFO] Running prereq checkConflictAgainstOHWithDetail

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches can be applied: 28440711

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches are not required:

[Oct 26, 2018 12:39:05 AM] [INFO] Following patches are auto rollbackable:

[Oct 26, 2018 12:39:05 AM] [INFO] Finished checking prereq checkConflictAgainstOHWithDetail

[Oct 26, 2018 12:39:06 AM] [INFO] Running prerequisite checks…

[Oct 26, 2018 12:39:06 AM] [INFO] Space Needed : 678.259MB

[Oct 26, 2018 12:39:06 AM] [INFO] Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 28440711

[Oct 26, 2018 12:39:07 AM] [INFO] Start fuser command /sbin/fuser /app/oracle/product/12.1.0.2/bin/oracle at Fri Oct 26 00:39:07 AST 2018

[Oct 26, 2018 12:39:07 AM] [INFO] Finish fuser command /sbin/fuser /app/oracle/product/12.1.0.2/bin/oracle at Fri Oct 26 00:39:07 AST 2018

[Oct 26, 2018 12:39:07 AM] [INFO] OPatch continues with these patches: 28440711

[Oct 26, 2018 12:39:07 AM] [INFO] Do you want to proceed? [y|n]

[Oct 26, 2018 12:39:07 AM] [INFO] Start to wait for user-input at Fri Oct 26 00:39:07 AST 2018

[Oct 26, 2018 12:39:11 AM] [INFO] Finish waiting for user-input at Fri Oct 26 00:39:11 AST 2018

[Oct 26, 2018 12:39:11 AM] [INFO] User Responded with: Y

[Oct 26, 2018 12:39:11 AM] [INFO] [OPSR-TIME] All checks passed.

[Oct 26, 2018 12:39:11 AM] [INFO] Need to look only in the OUI based Oracle Home to configure OCM

[Oct 26, 2018 12:39:11 AM] [INFO] Current working directory is : //app/oracle/product/12.1.0.2/OPatch

[Oct 26, 2018 12:39:11 AM] [INFO] OPatch is generic, it is not bundled with OCM.

[Oct 26, 2018 12:39:11 AM] [INFO] opatch-external.jar is in /app/oracle/product/12.1.0.2/OPatch/jlib/opatch-external.jar

[Oct 26, 2018 12:39:11 AM] [INFO] opatch-external.jar is in /app/oracle/product/12.1.0.2/OPatch/jlib/opatch-external.jar

[Oct 26, 2018 12:39:11 AM] [INFO] You selected -local option, hence OPatch will patch the local system only.

[Oct 26, 2018 12:39:11 AM] [INFO] Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/app/oracle/product/12.1.0.2’)

[Oct 26, 2018 12:39:11 AM] [INFO] Is the local system ready for patching? [y|n]

[Oct 26, 2018 12:39:11 AM] [INFO] Start to wait for user-input at Fri Oct 26 00:39:11 AST 2018

[Oct 26, 2018 12:39:12 AM] [INFO] Finish waiting for user-input at Fri Oct 26 00:39:12 AST 2018

[Oct 26, 2018 12:39:12 AM] [INFO] User Responded with: Y

[Oct 26, 2018 12:39:12 AM] [INFO] Backing up files…

[Oct 26, 2018 12:39:12 AM] [INFO] OUI-67117:Backing up files affected by the patch ‘NApply’ for restore. This might take a while…

[Oct 26, 2018 12:39:15 AM] [INFO] Applying patch 28440711…

[Oct 26, 2018 12:39:15 AM] [INFO] Starting apply session

[Oct 26, 2018 12:39:15 AM] [INFO] opatch-external.jar is in /app/oracle/product/12.1.0.2/OPatch/jlib/opatch-external.jar

[Oct 26, 2018 12:39:15 AM] [INFO] ApplySession applying interim patch ‘28440711’ to OH ‘/app/oracle/product/12.1.0.2’

[Oct 26, 2018 12:39:15 AM] [INFO] Applying interim patch ‘28440711’ to OH ‘/app/oracle/product/12.1.0.2’

[Oct 26, 2018 12:39:15 AM] [INFO] [OPSR-TIME] Starting to apply patch “28440711” to local system

[Oct 26, 2018 12:39:15 AM] [INFO] opatch-external.jar is in /app/oracle/product/12.1.0.2/OPatch/jlib/opatch-external.jar

[Oct 26, 2018 12:39:15 AM] [INFO] OPatch detected ARU_ID/Platform_ID as 226

[Oct 26, 2018 12:39:15 AM] [INFO] ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.

[Oct 26, 2018 12:39:15 AM] [INFO] [OPSR-TIME] Saving patch 28440711 in patch storage

[Oct 26, 2018 12:39:15 AM] [WARNING]OUI-67124:ApplySession failed but Oracle Home has not been modified.null

[Oct 26, 2018 12:39:16 AM] [SEVERE] OUI-67073:UtilSession failed: ApplySession failed but Oracle Home has not been modified.null

[Oct 26, 2018 12:39:16 AM] [INFO] ——————————————————————————–

[Oct 26, 2018 12:39:16 AM] [INFO] The following warnings have occurred during OPatch execution:

[Oct 26, 2018 12:39:16 AM] [INFO] 1) OUI-67124:ApplySession failed but Oracle Home has not been modified.null

[Oct 26, 2018 12:39:16 AM] [INFO] ——————————————————————————–

[Oct 26, 2018 12:39:16 AM] [INFO] Finishing UtilSession at Fri Oct 26 00:39:16 AST 2018

[Oct 26, 2018 12:39:16 AM] [INFO] Log file location: /app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-10-26_00-39-02AM_1.log

[Oct 26, 2018 12:39:16 AM] [INFO] Stack Description: java.lang.RuntimeException: ApplySession failed but Oracle Home has not been modified.null

at oracle.opatch.SystemCall.fastCopy(SystemCall.java:297)

at oracle.opatch.SystemCall.copyFile(SystemCall.java:1131)

at oracle.opatch.SystemCall.copyFile(SystemCall.java:1023)

at oracle.opatch.SystemCall.copyRecurse(SystemCall.java:1571)

at oracle.opatch.SystemCall.copyRecurse(SystemCall.java:1411)

at oracle.opatch.ApplySession.saveOriginalPatch(ApplySession.java:1258)

at oracle.opatch.ApplySession.processLocal(ApplySession.java:3496)

at oracle.opatch.ApplySession.process(ApplySession.java:5044)

at oracle.opatch.ApplySession.process(ApplySession.java:4906)

at oracle.opatch.OPatchACL.processApply(OPatchACL.java:310)

at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:1397)

at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:363)

at oracle.opatch.opatchutil.NApply.process(NApply.java:343)

at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1105)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at oracle.opatch.UtilSession.process(UtilSession.java:359)

at oracle.opatch.OPatchSession.process(OPatchSession.java:2670)

at oracle.opatch.OPatch.process(OPatch.java:811)

at oracle.opatch.OPatch.main(OPatch.java:861)

Caused by: java.lang.RuntimeException: ApplySession failed but Oracle Home has not been modified.null

… 22 more

Caused by: java.lang.RuntimeException

… 22 more

Work Around Solution:

If you are placing the patch outside your Oracle database file systems, copy it to one of your “local database file systems” where the owner of the file system will be your oracle user.

Apply the patch again…and it will work !!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Unified Auditing in Oracle 18c

Oracle database Unified Audit Trail was introduced in Oracle 12cR1 , as a mechanism to unify different oracle database audits (based on different features) under one view. As you may know “mixed mode” auditing is enabled by default starting with 12cR1 release. One of the limitations when switching from “standard auditing” to “unified auditing” in both Oracle 12cR1 and 12cR2 is you can’t push audits to syslog anymore. This has changed in Oracle 18c, you can push audits to SYSLOG in Unix/Linux OS and to windows event log.

A new init parameter has been introduced “unified_audit_systemlog”

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/UNIFIED_AUDIT_SYSTEMLOG.html#GUID-C4E65709-0FE3-4945-8F39-7B0867E2494A

Picture1

In window OS I have set the parameter as TRUE as shown below:

Picture2

For simulation through RMAN I have executed command to take controlfile backup then check the windows event log:

 

Picture3.png

Another new feature in 18c , is the ability to export and import unified audit trail !

Command to export:

expdp system/XXXXXXXX full=y directory=DUMP_DIR logfile=exp_unified18c_log.log dumpfile=exp_unified18c.dmp INCLUDE=AUDIT_TRAILS

Picture4

 

 

 

 

 

 

 

 

 

Oracle 18c new feature: Cloning PDB using DBCA (Database Configuration Assistant)

Here I am cloning a local PDB using DBCA, which is a new feature in oracle 18c:

Go to

cd $ORACLE_HOME/bin

Execute the command:

dbca

make sure X-windows is configured: export DISPLAY=PCXXXX:0.0

Picture1Picture2Picture3Picture4Picture5Picture6Picture7Picture8

You can use silent installation:

In Linux:

./dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

In Windows:

dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

silent_install

Upgrading Oracle database from 12cR2 to 18c in Windows

Download the binary for OTN and extract the ZIP file:

Picture1

Choose “set up Software only” option:

Picture2

Upgrading existing Oracle database from 12cR2 to 18c

In the unzipped binaries go to “bin” folder then search for dbua.bat file

 

UP1UP2

UP3

You will see a list of pre-requisite checks that is being listed, in my case I will hit “Fix & Check Again” option….this will successfully eliminate the check with severity “Error” regarding recycle bin not being purged as shown below:

UP4UP5I accepted the warning message, press “Yes”

UP6

DBUA (Database Upgrade Assistant) is providing a rollback (recovery strategy) in case the upgrade fails, you have to choose on of three options:

  • Flashback to guaranteed restore point
  • Full RMAN backup – restore script is also provided as shown below
  • You will be responcible for the recovery strategy

UP7UP8UP9UP10

Wait until the upgrade finishes successfully !

After successfully upgrading your Oracle database, you can enable a new feature in 18c called “Read-Only” Oracle Homes .

This feature enables you take a software image, and separating configuration files. Also, it will improve your patching process as the patched (binaries) image can be distributed to other servers.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle PSU July 2018 error – ORA-20001: Latest xml inventory is not loaded into table

while applying latest Oracle PSU released in July 2018 for 12.1.0.2 release i have faced the following when i reached the “data patch” level:

myhostt1:orax1> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Thu Jul 19 10:53:37 2018

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /oracle/orax1/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

Connecting to database…OK

Bootstrapping registry and package to current versions…done

Queryable inventory could not determine the current opatch status.

Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

and/or check the invocation log

/oracle/orax1/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/oraclex/oradbt14/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Thu Jul 19 10:53:41 2018

if you try to execute the following query:

SQL> select * from OPATCH_XML_INV ;

ERROR:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04004: error while reading file

/oracle/orax1/product/12.1.0.2/QOpatch/qopiprep.bat

 

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
——————————————————————————–
ORA-20001: Latest xml inventory is not loaded into table

 

SOLUTION:

first, you need to download the latest OPatch utility 12.2.0.1.14

second, the error i have faced due to that i am invoking the opatch utility outside ORACLE_HOME…… now you need copy it in your oracle home directory ( its a must ! )

cd $ORACLE_HOME
mv $ORACLE_HOME/OPatch.orig
mkdir OPatch
cd OPatch
scp -r /newopatch/OPatch/* .

a good reference is Doc ID 1602089.1

impdp oracle error ORA-01000: maximum open cursors exceeded

my current environment were the problem was faced in:

Oracle 12cR1 (12.1.0.2.180417)

Linux Red Hat 7.4

The following errors were thrown in the import (impdp) log file :

ORA-39014: One or more workers have prematurely exited.

ORA-39029: worker 1 with process name “DW00” prematurely terminated

ORA-31671: Worker process DW00 had an unhandled exception.

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPC$QUEUE_INT”, line 254

ORA-06512: at “SYS.KUPW$WORKER”, line 2157

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPW$WORKER”, line 4700

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPW$WORKER”, line 23341

To resolve this problem …you have three options:

Apply ORACLE DATABASE Patch for Bug# 23278750 for Linux-x86-64 Platforms 

OR

Upgrade to 12.2.0.1 🙂

OR

 Set the OPEN_CURSORS parameter to a value equal to or greater than the total number of indexes being imported

 

 

ALTER USER system privilege in Oracle 12cR2

In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

1

2

 

3

 

4

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

11

As a SYSTEM user I can’t alter the SYS user password as shown below:

12

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

33

in this example The SYSTEM account will execute the procedure with “definer’s” right.

 

 

 

 

 

 

 

 

 

 

Applying April 2018 PSU Patch ERROR :Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

while applying latest Oracle database PSU (April 2018) on Oracle 12cR1 (12.1.0.2) on IBM AIX 7.1 Operating System the following errors were thrown:

Verifying the update…

There are 1 copy files under ORACLE_HOME that are not patched.
Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Restoring “/orcl/oratest/product/12.1.0.2” to the state prior to running NApply…

Checking if OPatch needs to invoke ‘make’ to restore some binaries…

OPatch was able to restore your system. Look at log file and timestamp of each file to make sure your system is in the state prior to applying the patch.

NApply restored the home. Please check your ORACLE_HOME to make sure:

– files are restored properly.

– binaries are re-linked correctly.

(use restore.[sh,bat] and make.txt (Unix only) as a reference. They are located under

“/orcl/oratest/product/12.1.0.2/.patch_storage/NApply/2018-04-19_12-37-29PM”

UtilSession failed: ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Log file location: /orcl/oratest/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-04-19_12-37-29PM_1.log

Solution:

use the latest Oracle OPatch utility you can download it from here:

https://updates.oracle.com/download/6880880.html

latest Opatch utility version (at the time of writing this article 🙂 ) is 12.2.0.1.13

Latest OPatch Utility