EBS SSO Login Issue Post migration/re-configuration

 ok, So after migrating our EBS R12.1.3 application from on-promise to client cloud we faced multiple issues. One if the imp issue was SSO login.

Issue 1: For new users or the user whose USER_GUID was not present in FND_USER table, users were not able login and user_guid was not getting populated in FND_USER table. 

ERROR at end of URL: 

https://<URL>.<domain>/OA_HTML/jsp/fnd/fnderror.jsp?text=Exception+while+updating+user+session

Error in Access Gate log: 

01ps] SEVERE exception while updating user GUID -->[[
java.sql.SQLException: ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ORA-06512: at "APPS.FND_OID_UTIL", line 2172
ORA-06512: at line 1

Caused by: java.sql.SQLException: ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ORA-06512: at "APPS.FND_OID_UTIL", line 2172
ORA-06512: at line 1

The SSO registration was successful and we were able to extract OID details from EBS database :

SELECT * FROM fnd_user_preferences WHERE user_name='#INTERNAL' AND module_name like '%LDAP%';

Also, users with user_guid present in table were able to login. which meant there was no issue with SSO configuration. So after digging into details we found that the issue was the firewall was not open from EBS DB to OID APP server.

Temporary Workaround:

Get orclguid value from OID for the user impacted and update it manually in fnd_user table.

Permanent Solution:

Open firewall from EBS DB to OID application for ports 389 & 636.

check if ldapbind is working.

Note: if you are using LB for OID in configuration, Open firewall from EBS DB to OID LB. No need to open port to OID servers.

reference: 

ORA-20001 Unable To Call Fnd_ldap_wrapper.update_user (Doc ID 2764993.1)

Issue 2: After resolving first issue, we were facing issue with user provisioning where team was not able to update user details from user define form or from OID.

Error in user provisioning:

Unabled to call fnd_ldap_wrapper.update_user due to the following reason:

ORA-20001: Unabled to call fnd_ldap_wrapper.update_user due to the following reason:

An unexpected error occurred. Please contact your System Administrator. (USER_NAME\***********). (USER_NAME\*********)

Error in Access Gate log:

####<Apr 12, 2022 1:21:31 PM AEST> <Warning> <oracle.apps.fnd.ext.common.server> <svebs2000004pr.nbndc.local> <wls_ag1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <faf0f208cb11b592:ab35fba:17ffe8e5c1e:-8000-0000000000004afd> <1649733691391> <BEA-000000> <FndSsoFilter::doFilter()-- error encountered
oracle.apps.fnd.ext.common.server.FndSsoException: Exception while updating user session.
at oracle.apps.fnd.ext.common.server.AppsHttpServletRequestWrapper.<init>(AppsHttpServletRequestWrapper.java:354)
at oracle.apps.fnd.ext.common.server.FndSsoFilter.doFilter(FndSsoFilter.java:45)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)

Temporary Workaround :

make user_guid null in fnd_user table using below sql:

@/oracle/ebs/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/fndssouu.sql << Username to be unlinked >>

Permanent Solution:

Disable the profile option : Application SSO LDAP Synchronization

Adding and Maintaining NLS Languages

Adding and Maintaining NLS Languages

* This is the new requirement from our client of Installing Arabic language in our EBS R12.1.3 as a part of HRMS implementation.

I will be giving high level steps here along with the issues faced during this installation of new language.

Step 1. Activate the language

* Check the languages that are already installed in system.

select LANGUAGE_CODE,NLS_LANGUAGE,INSTALLED_FLAG from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');

Flag: 
B - Basic
I - Installed

* SO we have only US(American) language installed in our system as basic language, & now need to install Arabic language in addition with below steps.

Login to EBS with System Administrator Responsibility and follow below steps:

Navigation: System Administrator -> License Manager -> Languages-> 

* Check that new languages is in installed status in system.

SQL> select LANGUAGE_CODE,NLS_LANGUAGE,INSTALLED_FLAG from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');

LANG     NLS_LANGUAGE                      I

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

US           AMERICAN                              B

AR          ARABIC                                     I

SQL>

Step 2:Run Maintain Multi-lingual tables (MMT)

* Run adadmin and choose below options

     

 * Issues faced during MMT are mentioned at the end of the document.

Step 3.  Run Maintain snapshot information

* Run adadmin and choose below options

* end message will be like below:

1 Step 4.  Run Translation Synchronization Patch utility & Request Translation Synchronization Patches

* Run the Translation Synchronization Patch utility (adgennls.pl), upload the generated manifest to support and Requested Translation Synchronization Patches as per note 252422.1: 

* Upload this file at https://updates.oracle.com/TransSync and wait for patches from oracle.

Fusion Middleware Health `not ok` Alert

Fusion Middleware - WebLogic Admin or Managed servers health warning Alert

So, We had requirement where our SOA team requested to have alert in place if SOA Admin or any of managed servers health goes into warning. As usual, we tried to set the alert from Web-Logic itself using standard method given in oracle DOC ID: 2478424.1 . However we were having this error "SMTPSenderFailedException: 501 5.1.7 Invalid address" which we were not able to resolve even with help of oracle.

Well, we couldn't go to sleep yet and decided to have our own python customized script for the alert and Bingo !! it worked like charm. 

Coming straight to point, Please find below script that can be used for any version of FMW (11g, 12c ). Just copy paste the script and run it using wlst as below:

Step 1: 

$source setDomainEnv.sh 

Step2: 

$java weblogic.WLST  get_wls_serverstatus.py

you can just extract the result of above wlst in log file and grep "'Server OverAll Health" into your mail:

$cat get_wls_serverstatus.py

############################################################################

# This script is for checking the Health Status of the servers

#  Author: @achaltecharch.blogspot.com

############################################################################

#       Connect to the Admin Server

from java.io import FileInputStream

import java.lang

import os

import string

connect("weblogic","******","t3://<Admin Node host>:<port>")

domainRuntime()

 cd('ServerRuntimes')

 servers=domainRuntimeService.getServerRuntimes()

for server in servers:

     serverName=server.getName();

     print '##############' , serverName,'###############'

     print 'Server OverAll Health :', server.getOverallHealthState()

     print '----------------------------------------------'

License EBS Product

License EBS HRMS/Payroll product

        We are having demand from client to have HRMS & Payroll modules in our EBS (current Version 12.1.3). When I checked we are having the License of this product and is in shared status with our application. 

        So I am  explaining here how to install(register) this product in the application.

Step 1.  Check if product is shared in your system with below query:

         select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level
        FROM FND_APPLICATION_TL FAT, fnd_product_installations FDI
        WHERE FDI.APPLICATION_ID = FAT.APPLICATION_ID
        and fdi.status in ('I', 'S')
        and FDI.APPLICATION_ID in (800,801);

-- where   status : I=installed, S=shared

* SO as it is shared for me, i am installing it with below steps. In case it is already installed(Staus=I) you can directly go with installing HRMS Patches.

 

Step 2. Login to application with sysadmin ( or with user having System Administrator Responsibility).

* Navigate to System Administrator > License Manager

Step 3: 

* Click on Products

 

Step 4:

* In next window, click on “ Show =more options” & In next window click on License Applications Product. Click continue button.

 Step 5:

Search for product to be Licensed and click checkbox against it like below for Payroll & click next.

 

It will show the product to be licensed and ask to submit as below. Submit after verifying that you have selected the correct product.

 

* Confirmation page will come after submission .

Step 6:

Requery to check status of product

          select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level
        FROM FND_APPLICATION_TL FAT, fnd_product_installations FDI
        WHERE FDI.APPLICATION_ID = FAT.APPLICATION_ID
        and fdi.status in ('I', 'S')
        and FDI.APPLICATION_ID in (800,801);

  

This status shows that we have installed i.e. licensed the product(s) successfully.

Check my other relative posts on: 

*Issues faced During HRMS Patch: R12.HR_PF.B.delta.8(20000288)and

*DataInstall and Hrglobal.drv installation

Thanks,

Achal