Login Issue

[satish]

Dear Erman,

We are on R12.2.5 and database version 19.17. We need your expertise on below issue. Our configuration is as below:

We have 2 application servers and 2 node RAC database servers.

On app node 1 : we have 2 oacores and 2 forms
On app node 2 : we have 2 oacores and 2 forms

Using shared appl top

We have 64GB ram on each app nodes and CPU's are as below on each node.

CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    2
Core(s) per socket:    4
Socket(s):             1

Recently number of concurrent users increased from 250 to 390. We faced below error in logs.


<4 Nov, 2023 3:53:27 PM IST> <Error> <ServletContext-/OA_HTML> <BEA-000000> <oracle.apps.fnd.login.LoginHelper.getConnection
weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool EBSDataSource to allocate to applications, please increase the size of the pool and retry..
        at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:252)
        at weblogic.jdbc.common.internal.RmiDataSource.getPoolConnection(RmiDataSource.java:471)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:553)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:513)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:506)
        Truncated. see log file for complete stacktrace

Thanks,
SG

[ErmanArslansOracleBlog]

1)
Go through -> Oracle E-Business Suite 12.2 Data Source Connection Pool Diagnostics (Doc ID 1940996.1)
It seem connection pool is fully loaded.. Consider setting ICT..

To set the Inactive Connection Timeout parameter, follow these steps.

Login to the WLS console,
Navigate to Services-> Data Sources -> EBSDataSource -> Connection Pool -> Advanced (Expand Arrow)
Click on the Lock and Edit Button
Update the Inactive Connection Timeout to a desired value in seconds..

Also check your active connection using the following query ->

select a.node_name, 'Number of user sessions : ' || count(
  distinct b.session_id) How_many_user_sessions from apps.fnd_nodes a,apps.icx_sessions b
  where disabled_flag != 'Y' and PSEUDO_FLAG = 'N'
  and (last_connect + decode(apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
  NULL,limit_time, 0,limit_time,apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
  and a.node_id=b.node_id and counter < limit_connects group by a.node_name
  /

2)
You may consider adding 1 more oacore server to each of your apps node..
1 oacore JVM can handle up to 150 users approx.

Reference MOS Note 1905593.1 Step 4.4.1 Adding a new managed server.

3)Check your env with FND login analyzer.. In case, patches might be needed.
E-Business Suite Applications Login Analyzer (Doc Id 2319360.1)

4)You may increase the capacity of your EBSDatasource in case this load is expected and additional connection pool resources are really required.

5)Also see -> https://ermanarslan.blogspot.com/2018/05/ebs-122-things-that-can-be-done-for.html

[satish]

Dear erman,

Thanks for the suggestions.

2)
You may consider adding 1 more oacore server to each of your apps node..
1 oacore JVM can handle up to 150 users approx.

We have 4cores and 2 threads per each core. Does adding oacore has any dependency with cpus? Pls advice.


Thanks,
Satish

[ErmanArslansOracleBlog]

Yes, of course having more oacore server requires more cpu resources.. Especially if you have the workload to utilize them well. A detailed analyze should be done at OS level and it must be ensured that you have room to grow in terms of oacore processes.

[ErmanArslansOracleBlog]

But before taking any action you should go through the MOS document "Oracle E-Business Suite 12.2 Data Source Connection Pool Diagnostics (Doc ID 1940996.1)" and identify your issue a little more. For instance, you may have a leaked connection problem, or you may have some other problem.. Maybe your app is really utilizing these connections, I mean it may actually need them..

Note: A connection is said to be leaked if it is not returned, no longer associated with the connection pool, or otherwise recoverable.