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>

ADOP Phase=Preppare ERROR: [ERROR]: Patch file system WebLogic domain edit lock is enabled.

Action:

I run the adop phase=prepare on the run file system.

ERROR:

ERROR:

Verifying existence of context files in database.

Checking if adop can continue with available nodes in the configuration.

    Log: /u02/erptest/fs_ne/EBSapps/log/adop/23/20170227_125158/prepare/erplxtst1

        txkADOPEvalSrvStatus.pl returned SUCCESS

Validating configuration on admin node: [erplxtst1].

    Output: /u02/erptest/fs_ne/EBSapps/log/adop/23/20170227_125158/prepare/validate/remote_execution_result_level1.xml

    [ERROR]     txkADOPValidation failed on Node: "erplxtst1"

    [ERROR]     txkADOPValidation failed or is incomplete on Admin node: erplxtst1

    [ERROR]     Unable to continue processing on other available nodes: erplxtst2

    [UNEXPECTED]Error running "txkADOPValidation" on node(s): erplxtst1.

<?xml version = '1.0' encoding = 'UTF-8'?>

<NODES>

   <Node>

      <Name>erplxtst1</Name>

      <ProcessName>erplxtst1_erplxtst1pool-1-thread-1_erplxtst1_erplxtst1</ProcessName>

      <ResultString>    [WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.

        [ERROR]: Patch file system WebLogic domain edit lock is enabled.

.end std out.

end err out.

</ResultString>

      <Status>failed</Status>

      <TraceFile>NoTraceFile</TraceFile>

   </Node>

</NODES>

Solutions:

1.    On the PATCH file system:

mv /dev7i/applmgr/fs2/FMW_Home/user_projects/domains/EBS_domain_dev7i/edit.lock edit.lockOLD


2. Source the Run file system environment.

3. Re-run prepare:
 
  adop phase=prepare

  

Adop Phase=prepare Failed After Creating the Custom Application in EBS R12.2

ERROR:

he adop phase=prepare failed with error.  The unexpected error occurred while executing txkADOPPreparePhaseSynchronize.pl.

In txkADOPPreparePhaseSynchronize log file
=============================
Inside evalADPATCHStatus()...
=============================
message_status: ERROR
Adsplice action did not go through successfully.
*******FATAL ERROR*******
PROGRAM : (//EBSDEV/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl)
FUNCTION: main::execADSPLICE [ Level 1 ]
ERRORMSG: Adsplice action did not go through successfully.

Solutions:

Please run the following steps.

1. adop phase=abort,cleanup cleanup_mode=full (as env is currently in prepare failed state after adsplice was run on Run FS).
2. adop phase=fs_clone
3. adop phase=prepare

ERROR: After run the EXEC FND_CONC_CLONE.SETUP_CLEAN;

Action: Action: After run the EXEC FND_CONC_CLONE.SETUP_CLEAN;

Running adautoconfig.sh then facing below error.

1. ERROR:

begin

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

ORA-06512: at “APPS.ADX_PRF_PKG”, line 83

ORA-01403: no data found

ORA-06512: at line 7

 

2.Solution:

1. Run adadmin–>Recreate Grants and Synonyms

2. Compile Apps Schema

 

 

Login Page Issue 500 Internal Server Error

ERROR:

I found that error in application log and Database alert log when hitting the login URL.

Error in executing triggers on connect internal
*** 2017-01-21 18:37:11.364
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 5


Solutions:

column TRIGGER_BODY format a1000
set pagesize 1000
spool trg.txt

SQL>select OWNER, TRIGGER_NAME,
TRIGGER_TYPE, TRIGGERING_EVENT, STATUS, TRIGGER_BODY
from DBA_TRIGGERS
where trim(triggering_event) = 'LOGON';

Status: Enabled


SQL>alter trigger LOGON_AUDIT_TRIGGER disable;


After disable the trigger Login page is opening.

How to Disable Login Assistance Link In Oracle ERP R12

Solution:
 =========================================================================

The system profile 'Local Login Mask' has been obsoleted in R12 and cannot be used to disable Login Assistance link.  Please follow the steps below to disable this link:

1. Logon with Functional Administrator responsibility.
2. Click on 'Personalization' Tab.
3. Enter /oracle/apps/fnd/sso/login/webui in document path and click on 'Go' button.
4. Click on 'Pencil' icon under 'Personalize Page' for oracle/apps/fnd/sso/login/webui/MainLoginPG.
5. Make sure that 'Include' for Site is checked and click on 'apply'
6. Look for (LoginRN.MainLoginRN.ForgotPasswordUrlRowLayoutRN1) and click on 'Pencil' icon in
'Personalize' column.
7. Change 'rendered' property value to 'false' at Site level from LOV and click on Apply.
8. This will disable the link and users logging in after the change will not see the link. 

==========================================================================
 

How to check Long running Request in Oracle ERP R12

======================================================================
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC
======================================

How to check tablespace and Datafile free size in oracle

How to check Datafile size for the single tablespace.

-------------------------------------------------------------------------------------------------------------------------

 SELECT B.TABLESPACE_NAME,

         B.FILE_NAME,

         ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",

         ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",

         ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",

         CASE

            WHEN MAXBYTES = 0

            THEN

               ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)

            ELSE

               ROUND (

                    ( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)

         END

            "TOTAL FREE SPACE GB"

    FROM DBA_FREE_SPACE A, DBA_DATA_FILES B

   WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('APPS_TS_MEDIA')

GROUP BY B.TABLESPACE_NAME,

         B.FILE_NAME,

         B.BYTES,

         B.MAXBYTES

ORDER BY 1, 2;

---------------------------------------------------------------------------------------------------------------

2.  How to check tablespace Total size, used and free

---------------------------------------------------

select

   fs.tablespace_name                          "Tablespace",

   (df.totalspace - fs.freespace)              "Used MB",

   fs.freespace                                "Free MB",

   df.totalspace                               "Total MB",

   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"

from

   (select

      tablespace_name,

      round(sum(bytes) / 1048576) TotalSpace

   from

      dba_data_files

   group by

      tablespace_name

   ) df,

   (select

      tablespace_name,

      round(sum(bytes) / 1048576) FreeSpace

   from

      dba_free_space

   group by

      tablespace_name

   ) fs

where

   df.tablespace_name = fs.tablespace_name;

UNIX Useful Commands - FIND

1).Find files modified within specific time

Find files that have been modified less than 3 days ago.

find . -mtime -3

To be more specific and just check for the file change time, use:

find . -ctime -3

To be even more specific you can set a date range by using: Find all files changed, and thus modified, at least one day ago but within three days ago.

find . -ctime +1 -a -ctime -3

2).Find and Replace text in Multiple Files

Use,

                                find . -type f | xargs grep -l 'IBM' | xargs sed -i '' -e 's/IBM/ORACLE/g'

The first part of the command is find, which finds all files and excludes directories. That result is then piped to grep, which lists all files that contain IBM. The results of is then sent to sed, which replaces all occurances of IBM with ORACLE.

3).Find and Move files to a specific location

Use,

                              find . -newer *lmd.trc -exec cp '{}' /tmp/ \ ;

Above command find the file newer than the *lmd.trc and then copies all the files to the tmp directory