ORA-04091: table APPLSYS.FND_PROFILE_OPTION_VALUES is mutating, trigger/function may not see it

ERROR:

Autoconfig failed with following error , checking on this

ORA-04091: table APPLSYS.FND_PROFILE_OPTION_VALUES is mutating,
trigger/function may not see it
ORA-06512: at "APPS.FND_CORE_LOG", line 45
ORA-06512: at "APPS.FND_CORE_LOG", line 456
ORA-06512: at "APPS.FND_PROFILE", line 122
ORA-06512: at "APPS.ALR_FND_PROFILE_OPTIO_0_84_UAR", line 2
ORA-04088: error during execution of trigger
'APPS.ALR_FND_PROFILE_OPTIO_0_84_UAR'
ORA-06512: at line 43
ORA-06512: at line 123


Action Plan:

SQL> show user
USER is "APPS"


SQL> Alter TRIGGER APPS.ALR_FND_PROFILE_OPTIO_0_84_UAR disable;

Trigger altered.


>> adautoconfig completed successfully.

SQL> Alter TRIGGER APPS.ALR_FND_PROFILE_OPTIO_0_84_UAR enable;

Trigger altered.

[ERROR]: The value of s_appltmp is not set correctly in at least one of the context files. validateServerSSLConfig

We faced this issue in Multi-Node Application R12.2 environment.

ADOP PREPARE PHASE ERROR:

Validating configuration on admin node: [appltst1].
  Output: /u01/test/fs_ne/EBSapps/log/adop/46/20180127_013644/prepare/validate/remote_execution_result_level1.xml
  [ERROR]   txkADOPValidation failed on Node: "apptst1"
  [ERROR]   txkADOPValidation failed or is incomplete on Admin node: appltst1
  [ERROR]   Unable to continue processing on other available nodes: appltst2
  [UNEXPECTED]Error running "txkADOPValidation" on node(s): appltst1.



Cause:  [ERROR]: The value of s_appltmp is not set correctly in at least one of the context files.
validateServerSSLConfig:

ADOP do not use the shared directory for APPLTMP.

Action Plan: We have implemented below action plan in appltst1 and appltst2 in TEST for both FS.

Appltst1:

>>>FS RUN:

bash-3.2$ diff test_appltst1.xml-org test_appltst1.xml
549c549
<     <APPLTMP oa_var="s_appltmp">/prd/share/test/appltmp</APPLTMP>
---
>     <APPLTMP oa_var="s_appltmp">//u01/appltst/fs1/inst/apps/test_appltst1/appltmp</APPLTMP>    ->>> This parameter needs to update.

>> Soft Link Creation:
/u01/appltst/fs1/inst/apps/test_appltst1
bash-3.2$ mv appltmp appltmp-org

bash-3.2$ ln -s /prd/share/test/appltmp appltmp

bash-3.2$ ls -ld /prd/test/fs1/inst/apps/test_appltst1/appltmp
lrwxrwxrwx  1 appltst fin      22 Jan 27 03:17 /prd/test/fs1/inst/apps/test_appltst1/appltmp -> /prd/share/test/appltmp

>>>FS Patch:

bash-3.2$ diff test_appltst1.xml-org test_appltst1.xml
549c549
<     <APPLTMP oa_var="s_appltmp">/prd/share/test/appltmp</APPLTMP>
---
>     <APPLTMP oa_var="s_appltmp">/prd/test/fs2/inst/apps/test_appltst1/appltmp</APPLTMP>    ->>> This parameter needs to update.

>> Soft Link Creation:
/prd/test/fs2/inst/apps/test_appltst1
bash-3.2$ mv appltmp appltmp-org
bash-3.2$ ls
admin    appl     appltmp-org logs     oam     ora     out     pids     portal    rgf     soa     temp
bash-3.2$ ln -s /prd/share/test/appltmp appltmp
bash-3.2$ ls -ld /prd/test/fs2/inst/apps/test_appltst1/appltmp
lrwxrwxrwx  1 appltst fin      22 Jan 27 03:58 /prd/test/fs2/inst/apps/test_appltst1/appltmp -> /prd/share/test/appltmp

Renebus2:

>>>FS RUN:

bash-3.2$ diff test_appltst2.xml-org test_appltst2.xml
549c549
<     <APPLTMP oa_var="s_appltmp">/prd/share/test/appltmp</APPLTMP>
---
>     <APPLTMP oa_var="s_appltmp">/prd/test/fs1/inst/apps/test_appltst2/appltmp</APPLTMP>   ->>> This parameter needs to update.


Soft Link Creation:

bash-3.2$ cd $INST_TOP
/prd/test/fs1/inst/apps/test_appltst2
bash-3.2$ mv appltmp appltmp-org
bash-3.2$ ln -s /prd/share/test/appltmp appltmp
bash-3.2$ ls -ld /prd/test/fs1/inst/apps/test_appltst2/appltmp
lrwxrwxrwx  1 appltst fin      22 Jan 27 04:18 /prd/test/fs1/inst/apps/test_appltst2/appltmp -> /prd/share/test/appltmp

>>>FS Patch:

bash-3.2$ diff test_appltst2.xml-org test_appltst2.xml
549c549
<     <APPLTMP oa_var="s_appltmp">/prd/share/test/appltmp</APPLTMP>
---
>     <APPLTMP oa_var="s_appltmp">/prd/test/fs2/inst/apps/test_appltst2/appltmp</APPLTMP>     ->>> This parameter needs to update.

Soft Link Creation:

bash-3.2$ cd $INST_TOP
bash-3.2$ mv appltmp appltmp-org
/prd/test/fs2/inst/apps/test_appltst2
bash-3.2$ ln -s /prd/share/test/appltmp appltmp
bash-3.2$ ls -ld /prd/test/fs2/inst/apps/test_appltst2/appltmp
lrwxrwxrwx  1 appltst fin      22 Jan 27 04:20 /prd/test/fs2/inst/apps/test_appltst2/appltmp -> /prd/share/test/appltmp

>> Executed adautoconfig.sh (1-2-1) on both Run FS and Patch.

OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:

If  below error reported during opatch in ERPTST then perform below.

ERROR:
In Release 12 in Production:
When attempting to apply the CPUJAN08 to the 10.1.2 or 10.1.3 Oracle Homes
the following error occurs.


OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null

+++++++++++++++++++++++++++++++++++++++

Solution:

Or you can verify the following entry in /etc/oraInst.loc

inventory_loc=/u01/app/apptst/TEST/apps/tech_st/10.1.3
inventory_loc=/u01/app/apptst/TEST/apps/tech_st/10.1.2

1. Run opatch lsinventory using the command

opatch lsinventory -invPtrLoc <PATH to oraInst.loc file>
eg:
opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

To implement the solution, please execute the following steps:

2. Run OPatch pointing to the local inventory either 10.1.2 or 10.1.3

opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc

Opatch Prerequisite check "CheckActiveFilesAndExecutables" failed. The details are: Following executables are active : /u01/app/11.2.0/grid/lib/libclntsh.so.11.1

Opatch auto failed with error:

Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following executables are active :
/u01/app/11.2.0/grid/lib/libclntsh.so.11.1

OPatch failed with error code 73

Cause

This library is still being referenced by an active process:
/u01/app/11.2.0/grid/lib/libclntsh.so.11.1

This error during apply is indicative of the cause of the problem:

Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following executables are active :
/u01/app/11.2.0/grid/lib/libclntsh.so.11.1

Solution

As per opatch*.log, there are still processes using one of the files that is being patched:
Following executables are active :
/u01/app/11.2.0/grid/lib/libclntsh.so.11.1

ACTION PLAN
~~~~~~~~~~~
1. As 'root' user, identify process using this file/library:
  # fuser /u01/app/11.2.0/grid/lib/libclntsh.so.11.1
  # lsof /u01/app/11.2.0/grid/lib/libclntsh.so.11.1

2. You may shutdown application or kill process using above file/library before trying opatch again.
  # crsctl stop crs -f
  # fuser /u01/app/11.2.0/grid/lib/libclntsh.so.11.1
  # kill -9 <ospid of process identified above>