An EBS R12.1.1 instance had a lot of database sessions kept in INACTIVE for a long time (15+ days)
and never got closed. So, the number of db sessions increased daily and easily
exceeded database parameter PROCESSES (which is set to 3000). I had to recycle
Apps services each two weeks to kill the idle DB sessions. Most idle sessions were from
"e::bes:oracle.apps.icx.security.session.created" by JDBC Thin Client. Modules AR and GL are used most in this
instance.
After the instance was upgraded to R12.1.3 and its database was
upgraded to 12c, the idle session problem went away surprisingly. Before that, I tried below setting parameters and did NOT get much luck.
Two documents were used to tune CONTEXT variables:
- How To Prevent Inactive JDBC Connections In Oracle Applications 11i and R12 ( Doc ID 427759.1 )
- JVM: Guidelines to setup the Java Virtual Machine in Apps Ebusiness Suite 11i and R12 ( Doc ID 362851.1 )
The number of jvms (oc4j in R12) is configured by the autoconfig
variables s_oacore_nprocs, s_forms_nprocs, s_disco_nprocs, and
s_xmlsvcs_nprocs. They can be updated in the $CONTEXT_FILE (using the autoconfig editor from OAM).
The document suggests use 1 JVM per CPU for 100 active connected users to OACoreGroup. Use the script to determine "active users" for OACoreGroup :
REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select 'Number of user sessions : ' || count( distinct session_id)
How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y' and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
NULL,limit_time,
0,limit_time, FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) >
sysdate and counter < limit_connects;
REM
REM END OF SQL
REM
I got "Number of user sessions : 372" in my instance. So, I set s_oacore_nprocs to 4 to handle the volume ( assuming that 372 is peak level), and set s_forms_nprocs to 4 as well.
s_oacore_nprocs will decide how the number of sub-folders under $LOG_HOME/ora/10.1.3/j2ee/oacore
I set Heap configuration (4 parameters forms_jvm_start_options, oacore_jvm_start_options, forms_jvm_stop_options, oacore_jvm_stop_options in $CONTEXT_FILE) to
-Xmx1024M -Xms512M -XX:MaxPermSize=256M -XX:NewRatio=2 -XX:+PrintGCTimeStamps
I also added the following parameter to the DBC file:
JDBC\:oracle.jdbc.maxCachedBufferSize=262144
Changed the DBC file settings for dbc file under $FND_SECURE directory:
FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_MAX_JDBC_CONNECTIONS=256
FND_JDBC_USABLE_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5
But all above changes did not help much :(
UPDATE in November 2016:
After we moved EBS R12.1.3 instance to a new Linux 6 server (from Linux 5), adstrtal.sh got error when starting services.
Executing service control script:
$ADMIN_SCRIPTS_HOME/adoacorectl.sh start
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adoacorectl.sh version 120.13
Starting OPMN managed OACORE OC4J instance ...
****************************************************
Executing service control script:$ADMIN_SCRIPTS_HOME/adformsctl.sh start
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adformsctl.sh version 120.16.12010000.3
Starting OPMN managed FORMS OC4J instance ...
But I had no problem in log onto the EBS site and launching Forms.
The error (or warning) could be fixed by increasing CONTEXT FILE parameters for "Timeout specified in context file: 100 second(s)". Since parameters s_oacore_nprocs=4 and s_forms_nprocs=4 did not help, I just re-set them back to 2 (and fnd_jdbc_usable_check to false) in $CONTEXT_FILE and ran adautocfg.sh. After that, the adstrtal.sh Timed-out error did not show up.
