Script Required

[Deepak Seth_1]

We have lots of application server and one database server - oracle. User make connection using sqlnet . How do i track a user on application server have a corresponding connection to a database server . They might just be a ghost or runaway process . I have developed a script which use the lsof and find the inet and then match the inet with the inet on the database sever.

Example :

Application server

lsof | grep inet

api6.1 3369 agriffi 8u inet 0x23e14400 0t2004 TCP neptune-2.hdc.bio-rad.com:1969->jupiter-2.hdc.bio-rad.com:1521 (
Database Server

lsof | grep 1969

oraclebaa 10805 oracle 11u inet 0x4d6afc68 0t2004 TCP jupiter-2.hdc.bio-rad.com:listener->neptune-2.hdc.bio-rad.com:196
9 (ESTABLISHED)

If it found the corresponding inet that means connection is fine . But this script takes lots of time to run and is not very effective under some circustnaces. Any better method of achieving the results ?

[Santosh Nair_1]

One thing I can think of is trying to reduce the output from lsof. I assume you're only looking at TCP connections...

The first lsof could be changed to

lsof -i TCP

This would list all the TCP connections from/to the machine.

Then you can search for the particular connection by:

lsof -i TCP:1969 (from your example).

Also, since the database listener is on a particular port, you could probably narrow the initial search even further, i.e.:

lsof -i TCP:1521

Hope this helps.

-Santosh

[linuxfan]

Hi Deepak,

Look at the man page for lsof (it has lots of options)

To simplify your search

lsof -i TCP |grep oracle |grep -v ESTABLISH

Would list all the TCP sessions that are not established.

lsof -i | grep oracle |grep -v ESTABLISH
would list all the sessions that are not extablished

You could modify your script accordingly, based on what you are interested

-HTH
Ramesh

[A. Clay Stephenson · Accepted solution]

Hi Deepak:

Let's restrict the lsof output. The other guys have mentioned -i and that's good now let's improve it with -c and -a too.

let's say I want all inet sockets the for command the begin with the letter a OR the letter s. All the -c commands are OR'ed automatically; we then use -a to AND with -i (inet).

e.g.

lsof -c a -c s -a -i

The smaller this list is; the faster the rest of the code executes.

Clay

[Deepak Seth_1]

Here is logic of my script . Anyway i can make it fast.

set -x
#!/bin/ksh
database="baanpcdg baanpeu3 baantap"
> /tmp/notfound.out
for i in $database
do
# Get the hostname
sysname=`awk -F" " ' $1~ /'"$i"'/ {print $2} ' ~dseth/host.txt `
# collect all the process related to INET for a particular database
lsof | grep inet | grep $sysname | awk -F: '{print $1,":", $3}' | awk -F" " '{print $2,$11}' > /tmp/orabaan.$i
# remsh to application server and collect the informaion of lsof
remsh $sysname -n lsof | grep "inet" | grep jupiter | grep -v root | awk -F" " '{print $3,$9}' > /tmp/lsof.$sysname
while read a b
do
if [ `grep $b /tmp/lsof.$sysname | wc -l ` -eq 0 ]
then
RS="-"
# whodb is a script which shows the user login in a particular database
# This is to find the name of user/login id
NAME=`/usr/local/bin/whodb $i | grep $a | awk '{print $1}'`
if [ -n "$NAME" ]
then
echo "$i $a $sysname $b $NAME" >> /tmp/notfound.out
fi
fi
done < /tmp/orabaan.$i
done
# Finally moving the file a different location for the web server to display in HTML format .
rcp /tmp/notfound.out devsun2:/tmp/notfound.out

[Deepak Seth_1]

Pls. suggest me some modification in my script.

[Mladen Despic]

Deepak,

You could try using Glance in "adviser only" mode.
In your own "adviser syntax" you can specify any metric available in Glance, including "open files" such as sockets.

I've attached a sample adviser syntax that lists all sockets (you can modify it further if it seems useful).
If you save it to, say, sockets.syntax in your current directory, then you can run it as follows:

glance -adviser_only -syntax sockets.syntax

This will give you a "quick start" so you can get some idea if Glance can be useful or not.

The list of available metrics in Glance is under /opt/perf/paperdocs/gp/C/. More adviser syntax examples are available under /opt/perf/examples/adviser.

Regards ... Mladen

[A. Clay Stephenson]

Hi Deepak:

Without rewriting, I see some major things to improve your script.

First that long string of commands 'lsof | grep inet | grep $sysname | awk -F: '{print $1,":", $3}' | awk -F" " '{print $2,$11}' > /tmp/orabaan.$i'


That could be replaced by
lsof (with args above) | awk -f myfile.awk > /tmp/orabann.$i


Let awk do all that stuff you are doing with grep + awk in a single awk script.




Next, if [ `grep $b /tmp/lsof.$sysname | wc -l ` -eq 0 ] can be replaced with
grep -q $b /tmp/lsof.$sysname
STAT=$?
if [ ${STAT} -eq 0 ]
then
do your stuff
fi

In general, the fewer processes you have to fork, the faster your code will execute but thge very first thing to do is limit the input by adding args to lsof.

Clay

[Deepak Seth_1]

I think one of the reason of script taking lot of time is the behaviour of "lsof" command. It just hang from time to time and takes lot of time to complete.

i just tried this command .
lsof -c a -c -a -i

[A. Clay Stephenson]

Hi Again:

Absolutely, lsof can take a very long time on a busy system especially with no args. I would add the -b arg to avoid functions which can cause blocking. This should not exclude any of the sockets yoiu are looking for.

lsof -c a -c s -a -i -b 2>/dev/null | grep or awk

We redirect stderr so that lsof does not warn you about the possible blocking functions it is avoiding. I think it will not run much faster.

[Deepak Seth_1]

I ran this command in one of my app server .

time lsof -c a -c s -a -i

real 4:07.1
user 0.3
sys 0.5

Is it ok .

[Deepak Seth_1]

thanx all of you for your quick responses - specially "stephenson" repies helped me immediatelty to tune it some what . It may not impact much since iam not going to run this script very often - may be once every 2 hour .

I will assign the points shortly as it is giving some error right now when u try to assign points.

thanx once again.

[Robert Binkley]

Trace user script:
#!/bin/ksh
#/***********************************************************
#* $Header:
#* Name: tkprof_rpt
#* Description: Prompt for tkprof parameters and run tkprof.
#*
#* Inputs: Trace filename. (Std. udump directory is assumed)
#* Output filename.
#* Oracle userid.
#* Oracle password.
#*
#* Outputs: tkprof output in current dir., or home dir.
#*
#* Known limitations:
#*
#* Revisions:
#* Date who Description
#************************************************************/

echo "\n\n"

udump="/home/app/oracle/admin/prd1/udump"

ls -lt $udump | head

echo "\n\nEnter the name of the trace file ................ \c"
read ans
if [ ! -r $udump/$ans ]
then
echo "You must enter a full file name which you have read permissions"
echo "from the directroy: $udump"
exit 1
fi
trcfile=$ans

echo "Enter the name of the output (formatted) file ... \c"
read out

[Deepak Seth_1]

hi robert ,
What is this script doing ? Can u explain me.

[Wodisch]

Hello Deepak,

why do you not use the information from "V$SESSION",
as I see you are using Oracle as he DB?
IIRC then all the information you need is available in
there...

Just my ?0.02,
Wodisch

[Deepak Seth_1]

thanx wodish ,
I was thinking on the same line since yesterday as this will make my script run much faster and i don't have to live on the functioning of "LSOF" command - which still behave differently from time to time.

thanx once again.