Script for killing Inactive forms Sessions

Ref : http://practicalappsdba.wordpress.com/

Lots of time, due to user training issues, users leave their forms sessions open and do not end thier sessions. This can cause lots of issues as sometime even querying in a forms sessions places a lock (I know of Order Entry forms which used to behave like that) and as DBAs you will end up clearing blocking sessions in the database.

Oracle apps has timeout profiles (e.g. ICX Session Timeout) options and confirguration options (Apache timeouts etc) , but most of them do not end the database session. Following script checks the forms sessions in the database and kill the database and their corresponding forms (f60webmx) sessions, which were “INACTIVE” and idle (LAST_CALL_ET) for more than 1 hour.

This works for all types of configurations, whether single node or multi-node. For multi-node the requirement is to enable remsh (remote shell), otherwise change the script to use ssh (for more secure option). It also create log and appends to it and send e-mail if there are any sessions that needs to be killed.

#!/bin/ksh

# ============================================================================

# Program kill_forms_db_long.ksh - Kill long running/inactive forms processes 

# History:

# Date Who Description

# -------- ------------- -----------------------------------

# 04/09/07 Navdeep Saini Initial Creation

# 04/20/07 Navdeep Saini Hard coded the forms: INVTTMTX, INVTVQOH, INVTOTRX

# Usage:

# kill_forms_db_long.ksh [SID] 

# ============================================================================

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

# userror: echos the usage error line and exits with an exit code of 1

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

# Set environmental variables

#

command=$(print $0 | awk -F/ '{print $NF }')

typeset -xu ORACLE_SID

LOG=${HOME}/local/log/kill_forms_db_long.log

PW=/usr/local/bin/GetPass.ksh

typeset -x HOSTNAME=$(hostname)

typeset -x DATE=$DATE

function userror

{

 print "Usage: $command [<sid>]"

 exit 2

}

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

# check that applmgr is running this script

#

if [[ $(/usr/bin/whoami) != applmgr ]]; then

 echo "You must be applmgr to run this script."

 exit 1

fi

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

# if there are no command line parameters the script is in error

#

if [[ $# = 0 ]]; then

 print "Please enter the proper number of command line parameters"

 userror

fi

ORAENV_ASK=NO

typeset -x ORACLE_SID=$1

. /usr/local/bin/db

. ${PW} system

# Check to see if the SYSTEM password is available

if [[ -z ${SYSTEM_PASS} ]]; then

 print "${PW} does not have the SYSTEM password for $ORACLE_SID"

 userror

 fi

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

#Main

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

sqlplus -s <<EOF

system/${SYSTEM_PASS}@${ORACLE_SID}

set lines 200

set head off

column machine format a10

column logon_time format a19

column spid format a10

column process format a10

column module format a18

spool /tmp/kill_forms_db.lst

select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;

select 'Following user forms session, inactive for more than 60 min, are killed' from dual;

set head on

set pagesize 1000

SELECT

 p.spid,

 s.process,

 s.status,

 s.machine,

 to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,

 s.last_call_et/3600 Last_Call_ET,

 s.action,

 s.module,

 s.sid,

 s.serial#

 FROM

 V\$SESSION s

 , V\$PROCESS p

 WHERE

 s.paddr = p.addr

 AND

 s.username IS NOT NULL

 AND

 s.username = 'APPS'

 AND

 s.osuser = 'applmgr'

 AND

 s.last_call_et/3600 > 1

 and

 s.action like 'FRM%' 

 -- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX') 

 and 

 s.status='INACTIVE' order by logon_time;

spool off

set head off

set feedback off

spool /tmp/db_sessions.lst

Select

'alter system kill session '''||s.sid||','||s.serial#||''';'

 FROM

 V\$SESSION s

 , V\$PROCESS p

 WHERE

 s.paddr = p.addr

 AND

 s.username IS NOT NULL

 AND

 s.username = 'APPS'

 AND

 s.osuser = 'applmgr'

 AND

 s.last_call_et/3600 > 1

 and

 s.action like 'FRM%'

 -- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')

 and

 s.status='INACTIVE';

spool off

spool /tmp/forms_session.sh

SELECT

 'remsh '||s.machine||' kill -7 '||s.process

 FROM

 V\$SESSION s

 , V\$PROCESS p

 WHERE

 s.paddr = p.addr

 AND

 s.username IS NOT NULL

 AND

 s.username = 'APPS'

 AND

 s.osuser = 'applmgr'

 AND

 s.last_call_et/3600 > 1

 and

 s.action like 'FRM%'

 -- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')

 and

 s.status='INACTIVE';

spool off

EOF

#cat /tmp/kill_forms_db.lst |awk '{print "alter system kill session ('" $7 "\,'" $8}'

#mail -s "Long running forms sessions in "$HOSTNAME navdeep.saini@echostar.com < /tmp/kill_forms_db.lst

cat /tmp/kill_forms_db.lst >> $LOG

file:///C|/blog/kill_forms_db_long.txt (3 of 4) [5/22/2007 2:34:54 PM]file:///C|/blog/kill_forms_db_long.txt

cat /tmp/db_sessions.lst >> $LOG

cat /tmp/forms_session.sh >> $LOG

#Check to see if any rows returned and run the kill db and forms

NUMROWS=`cat /tmp/kill_forms_db.lst|grep "no rows selected"`

#echo $NUMROWS

if [[ $NUMROWS != "no rows selected" ]] then

sqlplus -s <<EOF

system/${SYSTEM_PASS}@${ORACLE_SID}

@/tmp/db_sessions.lst

EOF

#echo "inside if condition"

sh /tmp/forms_session.sh

cat /tmp/kill_forms_db.lst | mailx -s "Ran kill long running forms in "$ORACLE_SID DBATeam@yourdomain.com

fi