Set session timeout in R12

Profile option "ICX:Session Timeout" indicates when an EBS Forms session expires after inactivity. See Doc ID 307149.1 (E-Business Suite Applications Technology Stack Information Regarding How AutoConfig Sets Profile Option 'ICX: Session Timeout')

The "ICX:Session Timeout" is set by Autoconfig script adwebprf.sql using the s_sesstimeout variable, and so manual change on it will be reset by Autoconfig. Ensure that variable s_sesstimeout and variable s_oc4j_sesstimeout" (controls OC4J session timeout) in context file match (Doc ID 1067115.1).

Steps to set timeout to 60 (or 120) minutes:
1. Context variable in $INST_TOP/appl/admin on all mid-tiers: s_oc4j_sesstimeout to 60 (or 45)
2. Context variable on all mid-tiers: s_sesstimeout to 3600000 (or 7200000)
Note: 360000/(1000*60) = 60
3. Run autoconfig.

Autoconfig uses s_oc4j_sesstimeout to propagate following files:
- $INST_TOP/ora/10.1.3/j2ee/forms-c4ws/application-deployments/forms-c4ws/formsclient/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/forms/application-deployments/forms/formsweb/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/ascontrol/ascontrol/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/mapviewer/web/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/webservices/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml

After a Forms session times out, will its database session (or lock if any) stay intact in the database? Hope its database session(s) will be terminated together, however seems they are not.

NOTES in January 2022:  

s_oc4j_sesstimeout is not used in R12.2.  R12.2 still uses s_sesstimeout and has a setting in WebLogic.  See Doc ID 2142867.1 (Troubleshooting Session Timeouts in EBS Applications):

Login to WebLogic Admin Console

Under 'Change Center', click on 'Lock & Edit'

Under 'Domain Structure', click on 'Deployments'

Go to the next page until you see the 'oacore' deployment

Click on the plus sign next to the word 'oacore'

Click on the module '/OA_HTML'

Click on the 'Configuration' tab

Set 'Session Timeout (in seconds)' to 1800 (= 30 minutes)

Click 'Save'.

Under 'Domain Structure', click on 'Deployments'

Go to the next page until you get to the 'oacore' deployment and click on it

Click on the 'Configuration' tab

Set 'Session Timeout (in seconds)' to 1800

Click 'Save'.

Click 'Release Configuration'

Set the 'ICX Session Timeout' profile option value at the site level to 30 ( s_sesstimeout => 1800000)

Restart services via adstpall.sh / adstrtal.sh

Find the R12 Froms session that may lock data row

User received message "Could not reserve record[2 tries]. Keep trying? " on R12 Receipts Summary form (ARXRWMAI). I used below script (modified from Doc ID 1020007.6) to find the process IDs on Apps server that may place a lock on table rows.

SQL> select  nvl(S.USERNAME,'Internal') username, s.process EBS_PID, l.ctime, s.machine,
    L.SID||','||S.SERIAL# Kill,
    s.module,
    U1.NAME||'.'||substr(T1.NAME,1,20) table_name,
    decode(L.LMODE,1,'No Lock',
          2,'Row Share',
          3,'Row Exclusive',
          4,'Share',
          5,'Share Row Exclusive',
          6,'Exclusive',null) lmode,
    s.program,
    decode(L.REQUEST,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive',null) request
from   V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1

where    L.SID = S.SID 
and    T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
and    U1.USER# = T1.OWNER#
and    S.TYPE != 'BACKGROUND' and U1.name != 'SYS'
order by l.ctime desc,2,6 ;

EBS_PID is the process ID on Apps node. The query may return a long list of rows and so becomes not very useful.

OS command "top" can be used to see if any process runs for too long or uses high CPU/memory. Before kill it on OS level to release the block, Linux line can be used to verify its start time:

$ ps -eo pid,lstart,cmd | grep EBS_PID

For example, a Form session has run for days:

$ ps -eo pid,lstart,cmd | grep 16303

   9557 Thu Oct 15 09:40:59 201x grep --color=auto 16303

16303 Mon Oct 12 09:57:02 201x frmweb server webfile=HTTP-0,0,1,default

A simple query can be used to find R12 Forms' user name (in database 12c and 11g).  If the query returns nothing, it does not connect to database any more and it is safe to kill the run-away process on OS level. 

SQL> select sid, SERIAL#, process EBS_PID, machine, action, module, CLIENT_IDENTIFIER

FROM v$session where process = '16303' ;     -- 16303 is the EBS_PID

Below query can be used to verify if there is a true blocking session or not:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, 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;

Oracle Application Manager (OAM) also lists forms sessions with user name (OAM -> Dashboard -> Site Map -> Monitoring -> Forms Sessions).  But if the list is very long, it is very difficult to sort it out. Note if Forms sessions are not populated in OAM, set the profile option "Sign-On:Audit Level" to "FORM" (see Doc ID 1270277.1).

NOTES: 

Doc ID 1610624.1 gives a SQL to find Forms user name in 11i :
SELECT (SELECT substr(fu.user_name ,1 ,20)
         FROM   apps.fnd_user fu
         WHERE  fu.user_id = fl.user_id)
         user_name, to_char(fl.start_time,'DD-MON-YYYY HH24:MI') login_start_time,
        substr(fl.process_spid ,1 ,6) spid,
        to_char(fl.pid) pid, vs.machine host,
        substr(vs.process, 1 ,8) host_pid,
        substr(to_char(rf.audsid) ,1 ,6) audsid,
        to_char(vs.sid) sid,
        substr(to_char(vs.serial#) ,1 ,8) serial#,
        substr(vs.module || ' - ' ||
               (SELECT substr(ft.user_form_name, 1 ,40)
                FROM   apps.fnd_form_tl ft
                WHERE  ft.application_id = rf.form_appl_id
                AND    ft.form_id = rf.form_id
                AND    ft.LANGUAGE = 'US')
              ,1 ,40) form
FROM   apps.fnd_logins  fl,   gv$process vp,
       apps.fnd_login_resp_forms rf,   gv$session vs
WHERE  fl.end_time IS NULL
AND    fl.start_time > SYSDATE - 31
-- AND    fl.login_type = 'FORM'
AND    fl.process_spid = vp.spid
AND    fl.pid = vp.pid
AND    fl.login_id = rf.login_id
AND    rf.end_time IS NULL
AND    rf.audsid = vs.audsid
AND    vs.process = '5629'  -- enter process ID (EBS_PID) from above
ORDER  BY user_name, login_start_time, sid;