7 Feb 2022

Query to check Installed Languages in Oracle EBS

 Query to check Installed Languages in EBS:

Login to sqlplus as apps user

SELECT Language_code, NLS_language, Installed_flag
FROM fnd_languages
WHERE installed_flag IN ('I', 'B');


The Installed_Flag has 3 values
I- Installed
B- Base
D- Disabled

Concurrent Request failing due to XML publisher directory not available

Issue:
Concurrent Request Failing during OutputPost Processing

Below is the error message from OutputPost Processor Logs:

 [UNEXPECTED]  java.io.FileNotFoundException: /usr/tmp/ERPTEST1/xdokjshyw821_1905677210.fo (No such file or directory)
        at java.io.FileOutputStream.open(Native Method)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:221)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:171)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFileJDK118(Unknown Source)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.getTmpFOFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.createFO(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5984)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3475)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3564)
        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:311)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:185)

Cause 

The Temporary directory for XML Publisher has not been modified after making the clone so it was pointing to non existing location and getting error out.

Solution

1. Create a new Temporary Directory on OS level for the cloned environment.
2. Make sure that the application owner (APPLMGR) has Read and Write permission this directory.

ADOP fs_clone failing with error : "Could not find patch context file from database"

 Issue:

FS_CLONE is failing with error :Could not find patch context file from database
Error details:
$adop phase=fs_clone
==============
Checking for existing adop sessions.
    No pending session exists.
    Starting new adop session.
===========================================================================
ADOP (C.Delta.12)
Session ID: 43
Node: test1.domain1.com
Phase: fs_clone
Log: /test/erp/apps/fs_ne/EBSapps/log/adop/32/20220121_145748/adop.log
===========================================================================
Verifying existence of context files in database.
    [UNEXPECTED] Could not find patch context file from database
    [UNEXPECTED]Patch edition context file not found in database for host test1.domain1.com

Summary report for current adop session:
    Node test1.domain1.com:
       - Fs_clone status:   Not Started
    For more details, run the command: adop -status -detail

[STATEMENT] Please run adopscanlog utility, using the command
"adopscanlog -latest=yes"
to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 2 (Fail)

=========================
Cause:
===
Patch context file not found in fnd_oam_context_files tables.

Solution:
=======
1.Source the patch file system and run autoconfig on patch filesystem to update context file in database.
or
2. Run CtxSynchronizer to upload patch context file to database.

$ $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=<patch context file name and location> logfile=/tmp/patchctxupload.log

Provide apps password when prompted.



"APP-FND-02704: Unable to alter user APPS_NE to change password" while changing APPS password using FNDCPASS

 Issue: 

Changing apps password using FNDCPASS fails with below error

APP-FND-02704: Unable to alter user APPS_NE to change password.
Oracle error 28003:  has been detected in alterpassword2.

Command to change APPS Password.[Ensure to shut down Application services before changing apps password]
FNDCPASS apps/<apps_pwd> 0 Y system/<password> SYSTEM APPLSYS <new_apps_password>
Error Message from Logfile:
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.2
Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.
FNDCPASS:
+---------------------------------------------------------------------------+
Current system time is 10-APR-2020 18:43:36
+---------------------------------------------------------------------------+
Arguments
   FNDCPASS system/***** SYSTEM APPLSYS *****
+----------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS_NE to change password.
Oracle error 28003:  has been detected in alterpassword2.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 10-APR-2020 18:43:39
+---------------------------------------------------------------------------+


Cause:
Issue is due to Password_verify_function enables in dba_profiles for APPS,APPLSYS,APPS_NE users

Solution:

Disable the password verify function for APPS,APPLSYS,APPS_NE users in database profiles.
1. Identify the database profile for APPS,APPLSYS,APPS_NE users using below query.

SELECT USERNAME,PROFILE FROM DBA_USERS where USERNAME in ('APPS','APPLSYS','APPS_NE');
SELECT PROFILE,VALUE FROM DBA_PROFILES WHERE  PROFILE='&profile for user';

2. Disable password verify function

alter profile <profile_name> limit PASSWORD_VERIFY_FUNCTION null;

3. Run the FNDCPASS command to change password.

FNDCPASS apps/<apps_pwd> 0 Y system/<password> SYSTEM APPLSYS <new_apps_password>

4. After changing apps password, run autoconfig on Middle tier and update EBS datasource for R12.2.x

You can follow the below steps to update apps password in EBS Datasource:


5. Startup Application services

4. Revert back the changes done in step 2

Query to check blocking sessions in Oracle database

 Query to check blocking sessions in Oracle database from v$lock:

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess,
  id1,
  id2,
  lmode,
  request,
  type
FROM v$lock
WHERE (id1, id2, type) IN
  (SELECT id1, id2, type FROM v$lock WHERE request > 0
  )
ORDER BY id1,
  request;

How to change weblogic password from Backend in EBS 12.2.x?

On EBS 12.2.X environments , if you are on R12.AD.C.Delta.7 and R12.TXK.C.Delta.7 or later, we can change Weblogic admin server password using the below script.

1. Startup admin server. Do not start any other services

$. ./EBSapps.env RUN
$ cd $ADMIN_SCRIPTS_HOME
$adadminsrvctl.sh start

2. Run the below script  

perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

[The script will prompt for Current and New Weblogic Password and restarts Weblogic admin server using the new password.]

3. Run fs_clone to propagate the changes to the alternate file system (Patch File system).

$adop phase=fs_clone

How to Update APPS password in EBS Datasource (EBS 12.2.x)?

 From EBS 12.2.x versions, whenever we change apps password we need to update the new apps password in EBS Datasource.
Otherwise managed servers wont come up.
Steps to update apps password in Weblogic datasource for EBS 12.2x, post AD-TXK 7:
1. Start Only Admin server,Do not start any other services
$. ./EBSapps.env RUN
$ cd $ADMIN_SCRIPTS_HOME
$adadminsrvctl.sh start

2. Update apps password in Datasource from backend:
$perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
When prompted select the "updateDSPassword" option.
Sample screen output:

perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
Please select from list of valid options
        updateDSPassword - Update WebLogic Datasource Password
        updateDSJdbcUrl  - Update WebLogic Datasource Connection String
Enter Your Choice : updateDSPassword
Enter the full path of Applications Context File [DEFAULT -]:
Enter weblogic admin server password:
Enter the APPS user password:


Note: We can also update the apps password in EBS Datasource from Weblogic console.