Offensive Runaways: Defensive DBA

Reprinted with Permission by Quest Software Nov. 2002

Offensive Runaways: Defensive DBA
Shankar Govindan

Introduction

Oracle Application concurrent programs normally run for long hours and it becomes difficult to identify and monitor periodically which processes are actually running and which are run away processes. If a runaway process is identified as a forms executable then it is easy to check for the runaway and kill the process. Most Oracle sites will have scripts that either checks the dynamic view V$Session for forms programs or sniped processes and kills them. Some sites also check the CPU time of the shadow process and in cases where the shadow process exceeds a threshold set say 30 minutes, then it is a bad process and consumes CPU time more than it is supposed too.

In Oracle Application we could set a threshold and kill the forms executable related processes since they are inherently user processes, but if concurrent programs are long running and turns up as a CPU hog then it is important to check which database the process belongs to and what concurrent program is it executing. What is the status of the concurrent program is it active and running? What time did it start and what is the Request ID of the process so that it could be checked at the Application level.

Have your Oracle Application site got some kind of tracking mechanism in place?

In this paper we would talk about how a runaway brought the database to its knees and derailed our Auto Invoice program run and what we put in place to check and identify that a process is captured periodically and monitored by both the DBA’s and the developers.

Manifested Problem Example:

What prompted to write a script to track long running programs that where consuming CPU was when we had our Auto Invoice batch program that normally runs in the early mornings and completes before 6 Am., did not complete at all. One morning the Auto Invoice never completed, we found a runaway process had brought the database to its knees with excessive latch contention. The immediate solution was to identify the offensive runaway and kill him. But in the process the information we might need to track that this does not happen and proactively monitor any future issues like this affecting other programs was lost. Since we killed it early morning to get the Auto Invoice program run going and it was an OnCall related call, there was no time to get all the information of the offensive runaway. This prompted us to setup a cron that would proactively track any CPU consuming process and keep the operations and DBA’s informed. Known process can be ignored and any unknown ones can be verified with the developers for their abnormal behavior. We can then kill them before they delay any other processing, but most importantly we now have all the information, both dictionary and AOL related information of that process for debugging.

Goal:

Two parts, one is to automatically track all the forms runtime processes and programs that are fired off from Discoverer or impromptu application and kill them if their active processes run more than a threshold time. Two, get all the relevant information for the long running SPID or runaway SPID easily and mail it to concerned DBA’s, Developers and Operations, who will then verify if their concurrent program is long running and incase it is consuming more CPU time than it normally does. Also this would identify the CPU hogging programs easily and provides all the relevant information on the long running process for OnCall personnel to track it effectively.

Implementation:

Since long running programs will have their processes long running too and consuming CPU time, it is better to check the process at the OS level and go upwards. Most sites will have their Unix SysAdmins monitoring these and might try to flag a report. The best info he would be able to provide is the offensive process ID with CPU time consumed. This might not be sufficient to justify and kill the process. So a DBA might need to pull out more information on this process ID and provide the same to Operation or the concerned developers to get a fair idea of the program that is running and if it needs to be terminated or killed.

But to get the information from the database and decipher, communicate with the user or developer, and eventually kill its session and then logon with the right privilege at the OS level and kill the UNIX processes takes a lot of time and energy. If this is an Oracle Application program then the information needs to be pulled out of the AOL Tables too, so you don't kill a long running concurrent program like say a Purge or an Archive or a GL Posting.

Lets write a shell script to get all the Process ID’s first that have consumed CPU time more than a threshold. Here we set the threshold (C_LIMIT) for 45 Minutes. The script will spool all the Process ID’s (SPID) for a given database SID.

------------------------------------x cut script 1x---------------------------------

# !/bin/ksh
#|------------------------------------------------------------------------------------|
#| Filename: long_run_spid                                                            |
#| Author : Shankar Govindan.                                                         |
#| Created : 02/12/2002                                                               |
#|------------------------------------------------------------------------------------|
#| Description: This script is used to get Apps long running processes at the         |
#| OS level. It spools all the process Id's that have consumed cpu                    |
#| time set by the variable C_LIMIT to a file called gotem_spid                       |
#| which will be called by the script getem_badspool                                  |
#|                                                                                    |
#|------------------------------------------------------------------------------------|
if ( [ "$1" -eq "" ] )
then
echo "Usage: $0 ORACLESID"
echo " Eg.: $0 PROD"
exit
fi
ORACLESID=$1
PWD=`pwd`
mv $PWD/gotem_spid $PWD/gotem_spid-01
#_____________________________________________________________________________
#
# Variable that decides how much time a process is allowed to use the CPU
#_____________________________________________________________________________
C_LIMIT=45
#_____________________________________________________________________________
#
# spool all the processes that belongs to a SID
#_____________________________________________________________________________
ps -elcf -o user,pid,time,args | sort -r -k 3 | grep oracle${ORACLESID} | grep LOCAL | grep -v gre
p > $PWD/getem_spid
eval "grep -v ^# $PWD/getem_spid" | while read LINE
do
GOTEM=`echo $LINE | awk '{print $2}'`
#_____________________________________________________________________________
#
# Testing the position of TIME in the output and manupilate accordingly
#_____________________________________________________________________________
ps -fp $GOTEM | grep oracle${ORACLESID} | grep LOCAL | awk '{print $8}' > $PWD/testem
cat $PWD/testem | grep oracle >/dev/null 2>&1
if [ $? -eq 0 ];
then
CPUTIME=`ps -fp $GOTEM | grep oracle | awk '{print $7}' | awk -F":" '{print $1}' | grep -v gr
ep` ; export CPUTIME
else
CPUTIME=`ps -fp $GOTEM | grep oracle | awk '{print $8}' | awk -F":" '{print $1}' | grep -v gr
ep` ; export CPUTIME
fi
if [ "$CPUTIME" -ge "$C_LIMIT" ]; then
echo $GOTEM >> $PWD/gotem_spid
fi
done
return 0
rm $PWD/testem
rm $PWD/getem_spid
--------------------------------------------x end script 1 x-----------------------------------

Now, we have to get all the relevant information for this SPID with another script that will spool and send out a mail. To get these manually we have the process at the OS level, then check the status of the process from the dynamic view v$session and v$process. We have to then map the same process at the AOL level and get all the Application specific information from the FND tables.

Here is a script that would just take a SPID and send a mail with all the information that is required. The script connects to the right database and if the database does not have any information related to Oracle Application AOL tables then it does not output the same, but only what is available from the dictionary views.

We are not calling the earlier spooled file gotem_spid to mail all the SPID related information, instead the script is designed as an independent one. You could call the below script in the earlier gotem_spid script, like:

Cat $PWD/gotem_spid | while read CMD
do
$PWD/getem_badspool $CMD shankargovindan@yahoo.com
done

Change the MailList and the default Mail ID to your setup.

-------------------------------------------------------x CUT script 2 x-------------------------------------------------------
#!/bin/ksh
#|------------------------------------------------------------------------------------------|
#|Filename: getem_badspool                                                                  |
#|Author : Shankar Govindan.                                                                |
#| Created : 03/22/2002                                                                     |
#|------------------------------------------------------------------------------------------|
#| Description : This script is used to get Apps related info for an OS Process ID.         |
#| Pass an OS PID and this script outputs relevant database related information             |
#| for that OS PID to a file and the script mails the file to a given Mail ID.              |
#|                                                                                          |
#| 1st Argument: SPID                                                                       |
#| 2nd Argument: MAIL ID                                                                    |
#|------------------------------------------------------------------------------------------|
#| History : 03/24/2002 Shankar Added NVL function to spool non-Apps SPID's.                |
#| 03/29/2002 Shankar Added code to handle SPID more than a day old.                        |
#| 04/22/2002 Shankar Setup ENV in the script itself to run as any user.                    |
#| 05/10/2002 Shankar Removed LOCAL=NO for Release 11 compatibility.                        |
#|------------------------------------------------------------------------------------------|
if ( [ "$1" -eq "" ] )
then
echo "Usage: $0 SPID MAILID"
echo " Eg.: $0 12345 shankargovindan@yahoo.com else default_Mail_Id@domain.com"
exit
fi

CMD=$1
SPID=$CMD
MAILLIST=$2
#------------------------------------------------------------------------------------------
# Global Variables start here
#------------------------------------------------------------------------------------------
HOST=${HOST:=`/bin/hostname`}
ORATAB=/var/opt/oracle/oratab ; export ORATAB
ORACLE_BASE=/Ora_base/oracle ; export ORACLE_BASE
SETDIR=/dba/bin
MAILLOGDIR=/dba/log/${HOST}
CFGDIR=/dba/etc
GROUP=`groups | cut -d" " -f1`

function _badspidcmd
{
#-------------------------------------------------------------------------------------------
# Get the ORACLE_SID for the SPID and remember processes can be a day old.
#-------------------------------------------------------------------------------------------
ps -fp $CMD | grep oracle | awk '{print $8}' > $MAILLOGDIR/testem
cat $MAILLOGDIR/testem | grep oracle >/dev/null 2>&1
if [ $? -eq 0 ]
then
ORACLESID=`ps -fp $CMD | grep oracle | awk '{print $8}' | sed 's/oracle//g'` ;export ORACLE_SID
else
ORACLESID=`ps -fp $CMD | grep oracle | awk '{print $9}' | sed 's/oracle//g'` ;export ORACLE_SID
fi
PWD_FILE=$CFGDIR/.${GROUP}_${ORACLESID}.pwd #file like .dba_prod.pwd
SYSTEM_PWD=`grep -i "^system/" $PWD_FILE` #inside the file sys/manager
if [ "$SYSTEM_PWD" = "" ] ; then
echo "\nERROR: userid=system does not exist in ${PWD_FILE}"
return 1
fi
#-------------------------------------------------------------------------------------------
# Set up ENV variables so the script runs as any user
#-------------------------------------------------------------------------------------------
#$SETDIR/setdb ${ORACLESID} # If you have a env script, call it here.
ORACLE_HOME=`cat $ORATAB | grep ${ORACLESID} | awk -F":" '{print $2}'` ;export ORACLE_HOME
ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/ccs/lib:/usr/ucblib:/usr/dt/lib ; export LD_LIBRARY_PATH
#--------------------------------------------------------------------------------------------
#Main SQL script starts here, specify ORACLE_HOME/bin location for SQLPLUS
#--------------------------------------------------------------------------------------------
ps -fp$CMD > $MAILLOGDIR/badspid_${ORACLESID}
echo "_______________________________________________________________________________" >> $MAILLOGDIR/badspid_${ORACLE_SID}
echo "" >> $MAILLOGDIR/badspid_${ORACLESID}
echo "" >> $MAILLOGDIR/badspid_${ORACLESID}
(echo ${SYSTEM_PWD}@${ORACLESID}; echo set linesize 120 pagesize 100 pause off verify off feedb
ack off termout off; echo "select substr(s.SID,1,3) SID,substr(s.SERIAL#,1,7) SERIAL,substr(p.S
PID,1,7) UNIXPID,substr(s.MACHINE,1,8) Machine,substr(s.STATUS,1,8) Status,substr(s.osuser,1,8)
PCUSER,substr(s.username,1,8) ORAUSER,substr(s.program,1,30) Program_Name,to_char(s.logon_time
,'dd-mon hh24:mi') started_at,nvl(substr(fsav.user_name,1,20),0) Form_User_Name,nvl(substr(fsav
.RESPONSIBILITY_NAME,1,20),0) Responsibility,nvl(substr(fsav.user_form_name,1,15),0) Form_Name,
nvl(substr(fcp.user_concurrent_program_name,1,30),0) User_Program_Name,nvl(substr(fcr.status_co
de,1,2),0) STAT,nvl(substr(fcr.REQUEST_ID,1,7),0) REQUEST,nvl(to_char(fcr.actual_start_date, 'd
d-mon-yyyy hh24:mi:ss'),0) ACTUAL_START_DATE from v\$session s,v\$process p,apps.fnd_concurrent_programs fcp,apps.fnd_concurrent_requests fcr, apps.fnd_signon_audit_view fsav where p.spid=$CMD and s.paddr=p.addr and p.pid=fsav.pid(+) and s.AUDSID = fcr.ORACLE_SESSION_ID(+) and fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID(+) and fcr.PROGRAM_APPLICATION_ID = fcp.APPLICAT
ION_ID(+) ;") |$ORACLE_HOME/bin/sqlplus -S >> $MAILLOGDIR/badspid_${ORACLESID}
#---------------------------------------------------------------------------------------------
# Mail command starts here, modify MAILLIST as needed
#---------------------------------------------------------------------------------------------
if [ "$MAILLIST" != "" ] ; then
mailx -s "Long Running Apps Processes ${ORACLESID} ${HOST}" $MAILLIST < $MAILLOGDIR/badspid_${ORACLESID} >/dev/null
else
MAILLIST=shankargovindan@yahoo.com
mailx -s "Long Running Apps Processes ${ORACLESID} ${HOST}" $MAILLIST < $MAILLOGDIR/badspid_${ORACLESID} >/dev/null
return 1
fi
rm $MAILLOGDIR/testem
}
#----------------------------------------------------------------------------------------------
# Main Script starts here
#----------------------------------------------------------------------------------------------
if [ "$CMD" = "${1}" ];
then
_badspidcmd $CMD
return 0
else
echo " Database is down "
wait
return 1
fi
-------------------------------------------x END Script 2 x---------------------------------------

Output of the script:

Output I: The NVL function was added to the SQL scripts to output the information, irrespective of whether the process is executed by the concurrent managers or just an SQL program running for a long time. One such output is as shown below where there is no AOL related information for the SPID.

UID PID PPID C STIME TTY TIME CMD
oracle 21886 1 3 02:08:00 ? 626:00 oracleprd1 (LOCAL=NO)

SID SERIAL UNIXPID MACHINE STATUS PCUSER ORAUSER PROGRAM_NAME STARTED_AT FORM_USER_NAME
--- ------- ------- -------- -------- -------- -------- ------------------------------ ------------ --------------------
RESPONSIBILITY FORM_NAME USER_PROGRAM_NAME S REQUEST ACTUAL_START_DATE
-------------------- --------------- ------------------------------ - ------- --------------------
486 3108 21886 ljcqs010 ACTIVE applmgr APPS sqlplus@ljcqs010 (TNS V1-V2) 30-jul 02:08 0
0 0 0 0 0 0

From the above, it is clear that the program is a sqlplus program and not related to form or concurrent process.

Output II: This output below shows that the SPID has AOL related information too and helps in identifying the user concurrent program and the request Id’s status.

The above program is fired of as a SQLPLUS program by the concurrent process. This can be a concurrent program like a shell script or a C- program designed to spawn of multiple SQLPLUS process to purge the data. That is why it shows up as SQLPLUS program in the Program field of the v$session and as a purge program in the FND concurrent programs view.

Output III: Here is another output which was actually a runaway and we passed on the info to the user and got his permission before the processes was killed. This is a form runtime process.

SID SERIAL UNIXPID MACHINE STATUS PCUSER ORAUSER PROGRAM_NAME STARTED_AT FORM_USER_NAME
--- ------- ------- -------- -------- -------- -------- ------------------------------ ------------ --------------------
RESPONSIBILITY FORM_NAME USER_PROGRAM_NAME S REQUEST ACTUAL_START_DATE
-------------------- --------------- ------------------------------ - ------- --------------------
177 14818 14818 ljcqs026 INACTIVE oracle APPS f45runm@ljcqs026 (TNS V1-V2) 12-aug 08:02 AOGRAFF
MLG AR SUPERUSER Sign-On 0 0 0 0

177 14818 14818 ljcqs026 INACTIVE oracle APPS f45runm@ljcqs026 (TNS V1-V2) 12-aug 08:02 AOGRAFF
MLG AR SUPERUSER Transactions 0 0 0 0

177 14818 14818 ljcqs026 INACTIVE oracle APPS f45runm@ljcqs026 (TNS V1-V2) 12-aug 08:02 AOGRAFF
MLG AR SUPERUSER Receipts 0

From the above script we are able to identify the flow of sign on, transactions and receipts access of the user and the responsibility he is using.

The user might have shutdown his system without closing the forms or just disconnected from the Application without a clean exit.

The above scripts are reactive and track only long running programs and then the developers decide if it needs to be killed. The form related runaways are covered below separately as a two prong attack and they are pro-active, so that form runtime related processes never turn up here as a long running program.

Notes on the script:

Most of the time the Oracle Application concurrent programs would run for more than a day and hence I have included the option to check that too.

If a process is running and is not a day old then you would find that we can awk $8 from the output.

            $ps -fp 4288
            UID PID PPID C STIME TTY TIME CMD
            oracle 4288 1 3 03:42:41 ? 350:40 oracleprd1 (LOCAL=NO)
            $ps -fp 4288 | grep oracle | awk '{print $8}'
            oracleprd1

But if the process is more than a day old then you need to factor that when you awk the correct column, here we find that the process is a day old and hence we awk $9.

           $ps -fp 15723
            UID PID PPID C STIME TTY TIME CMD
            oracle 15723 1 0 Apr 28 ? 0:01 oracleprd1 (LOCAL=NO)
            $ps -fp 15723 | grep oracle | awk '{print $9}'
            oracleprd1

Once we get the process output we have to figure out on how to isolate the ORACLE SID from the command output. We use sed command of UNIX here to cut the oracle off the command line and capture just the ORACLE SID. Like,

sed 's/oracle//g'`

The basic Oracle ENV like NLS and LD_LIBRARY_PATH has been setup in the script itself so that the shell script can be executed as any user. This is a requirement of the SQLPLUS utility, which would error out otherwise, with so many versions of Oracle Sever software installed in the same machine.

I have removed LOCAL=NO from ps –fp <SPID> | grep oracle | grep LOCAL=NO, which we are used to in releases prior to version 8.x so that the script can run for Oracle Apps release 11 and above where the SqlNet connection uses Beq mode and will have LOCAL=YES. Also some connections which are spawned of locally like Database tools will also spawn of in the server itself and will have their process command line with LOCAL=YES and PROTOCOL=BEQ.

Definition of a bad process is presumed to be one that is a runaway without a Parent Process Id or one that is consuming more CPU time than normal which we find out using the UNIX command ps -ef | grep SPID | grep -v grep | awk {'print $8'} for process that are more than a day old and awk {'print $7’} for the current date ones.

If you are using the Script 2 as a standalone script and if there is a request to exclude known long running programs from the script, then just exclude it from the where clause of the script 2, like adding the following exclusion to the end of the script2.

Nvl(fcp.user_concurrent_program_name,’ ‘) not in (‘Purge Spawn’,’Customer Merge’);

Please note that I am using a password file here and you can use whatever you want that is appropriate for your setup and security.

PWD_FILE=$CFGDIR/.${GROUP}_${ORACLESID}.pwd
SYSTEM_PWD=`grep -i "^system/" $PWD_FILE`

The password file will be hidden with a dot and prefixed with the UNIX Group (.dba_prod.pwd) and will have an entry like,

            System/manager
            Sys/manager
            Shankar/dba

F45RUNM: Track and Kill them automatically

User programs like the Forms related, or Discoverer can also be a runaway. Although these processes might not be a major threat or might not bring the database to its knees, it is nevertheless best practice to track them and kill them if they run for longer durations than they should. We can check the program from v$session and then see what the value is for last_call_et of that session program. According to oracle,

The LAST_CALL_ET is a Number. This number is the amount of time in seconds since the user's last statement execution. Each time a user executes a new statement the LAST_CALL_ET gets reset back to 0 and begins accumulating again.

We can set a threshold time that we presume these programs if active should run. Let’s say we set this value to 45 minutes. Then any f45run programs or programs that have a ‘C:’ (run from windows) that show up in the program filed of v$session will become a candidate to be periodically sampled for the last_call_et value.

Check Metalink Note: 1022663.6 and Note: 1017759.102 for a description of how the program column information of v$session is populated.

If this value exceeds the threshold time of 45 minutes, then the SPID, SID and serial number of that program is spooled to a file. The script then verifies that the program is active and is running more than the threshold time. It then spools a SQL file with the command to kill the sessions.

______________________________________x cut script 3 x______________________________
#!/bin/ksh
#|---------------------------------------------------------------------------------------|
#| Filename: f45run_killem                                                               |
#| Author: Shankar Govindan.                                                             |
#| Created: 06/10/2002                                                                   |
#|---------------------------------------------------------------------------------------|
#| Description: This script is used to track the f45run and C: programs from the         |
#| v$session view for a given database, the script spools all the database               |
#| related info and compares the last_call_et to a set time.                             |
#| If the time is more than a set time then those SID’s are killed.                      |
#|                                                                                       |
#|---------------------------------------------------------------------------------------|
#| 1st Argument: ORACLE_SID                                                              |
#| 2nd Argument: MAILID -- optional                                                      |
#|                                                                                       |
#| History: 06/12/2002 Shankar Modified the Mailing portion to include default.          |
#|---------------------------------------------------------------------------------------|
# set -x
if ( [ "$1" -eq "" ] )
then
echo "Usage: $0 ORACLE_SID "
echo " Eg.: $0 DEV1 "
exit
fi

ORACLESID=$1
#------------------------------------------------------------------------------------
# Global Variables start here
#------------------------------------------------------------------------------------
HOST=${HOST:=`/bin/hostname`}
ORATAB=`/dba/bin/default ORATAB` ; export ORATAB
ORACLE_BASE=`/dba/bin/default ORACLE_BASE` ; export ORACLE_BASE
SETDIR=/dba/bin
MAILLOGDIR=/dba/log/${HOST}
CFGDIR=/dba/etc
MAILLIST=$2
GROUP=`groups | cut -d" " -f1`
SETTIME=2700 # 45 Minutes
#---------------------------------------------------------------------------------------
# Main Script starts here
# Get the System password
#---------------------------------------------------------------------------------------
PWD_FILE=$CFGDIR/.${GROUP}_${ORACLESID}.pwd ; export PWD_FILE
SYSTEM_PWD=`grep -i "^system/" $PWD_FILE`
if [ "$SYSTEM_PWD" = "" ] ; then
echo "\nERROR: userid=system does not exist in ${PWD_FILE}"
return 1
fi
#---------------------------------------------------------------------------------------
# Set up Env variables so the script runs as any user
#---------------------------------------------------------------------------------------
ORACLE_HOME=`cat $ORATAB | grep ${ORACLESID} | awk -F":" '{print $2}'`
ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data LD_LIBRARY_PATH=/usr/lib:/usr/ccs/lib:/usr/ucblib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/lib
/libXm_sol2.4:$ORACLE_HOME/lib:.
#----------------------------------------------------------------------------------------
#Main Sql script starts here, specify the ORACLE_HOME/bin location for sqlplus
#----------------------------------------------------------------------------------------
(echo ${SYSTEM_PWD}@${ORACLESID}; echo set linesize 120 pagesize 0 pause off head off verify off feedback off termout off; echo "select substr(p.spid,1,10)||':'||substr(s.sid,1,10)||':'||substr(s.seri
al#,1,10)||':'||substr(s.last_call_et,1,10) from v\$session s, v\$process p where (s.program like 'f
45%' or s.program like 'C:%') and s.type != 'BACKGROUND' and s.audsid != userenv('SESSIONID') and p.
addr=s.paddr;") |$ORACLE_HOME/bin/sqlplus -S > $PWD/f45info_${ORACLESID}
if [ -f $PWD/f45info_${ORACLESID} ] ; then
#---------------------------------------------------------------------------------------
# Loop and Check for every user link spooled by above sql script
#---------------------------------------------------------------------------------------
eval "grep -v ^# $PWD/f45info_${ORACLESID}" | while read LINE
do
FSPID=`echo $LINE | awk -F":" '{print $1}'`
FSID=`echo $LINE | awk -F":" '{print $2}'`
FSERIAL=`echo $LINE | awk -F":" '{print $3}'`
FTIME=`echo $LINE | awk -F":" '{print $4}'`
if [ $FTIME -ge $SETTIME ] ; then
echo "ALTER SYSTEM KILL SESSION '$FSID,$FSERIAL';" >> $PWD/f45kill_${ORACLESID}.sql
fi
wait
done
else
echo "ERROR"
fi
sqlplus -s << EOF
system/${SYSTEM_PWD}
@f45kill_${ORACLESID}
exit
EOF
rm $PWD/f45info_${ORACLESID}
______________________________________x cut script 3 x_______________________________

Most of the time the F45RUN processes will not be running for a long time unless it is a runaway process, in cases like that, if you want to get more information on the User who executed the request and the form name and responsibility used, you can modify the above f45run_killem shell script by calling the getem_badspool script for every SPID. Change the “Long Running Apps Programs” to “Programs that are being killed” and change the MAIL ID to a mail address where you can save them for a few days. Or the getem_badspool can be called and the information logged to a log directory for looking up later. Either way, you will have the entire information of the killed programs incase of any management issues.

Another scenario would be some times during book close or any special events, the forms process needs to run for a longer time than the threshold of 45 minutes set by us. This can be specific to certain responsibility. You can,

Exclude these responsibilities in the above f45run_killem script. Just add the view apps.fnd_signon_audit_views to the script and join the fnd_signon_audit_view.pid with the v$process.pid.

In the Output III above we see a forms runtime as a runaway. This user has a responsibility of MLG AR Super User and he was specifically removed from being clubbed in the kill section using the where clause of the f45run_killem script. His responsibility was excluded,

from apps.fnd_signon_audit_views fsav, v$process p
where nvl(fsav.responsibility_name,’ ‘) not in ('GL_SuperUser','MLG AR SUPERUSER')
and fsav.pid = p.pid

This sometimes becomes necessary in some sites as I said before during book close or other extended activities and you should setup the script by excluding whatever responsibility that you presume will be affected by the kill process.

Dead Connection Detection:

Apart from the above there is SQLNET.EXPIRE_TIME. This should not be confused with what we are doing here. This is setting up Dead Connection Detection (DCD) wherein we setup the sqlnet.ora file to say ping or probe to see if any connections have been abnormally disconnected. The server process then releases the server resources associated to that connection. In an Oracle Financial setup where there are spawned processes which can get into inactive periods periodically and this sqlnet.expire_time should be setup correctly. The sqlnet.expire_time is in minutes and 30 to 45 minutes would be comfortable to start with. For more info on DCD, lookup the Metalink Note: 1013364.6

Note: As usual test the scripts for your environment thoroughly on a test database and feel free to correct/customize the scripts for your setup.

Shankar Govindan works as a Sr. Oracle DBA at CNF , Portland, Oregon. Shankar Govindan is Oracle Certified 7, 8 and 8I, you can contact him at shankargovindan@yahoo.com.

Note: The above info as usual is of my individual tests and opinions and has nothing to do with the company I work for or represent.