topic Re: Script Required in Operating System - HP-UX

Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 19:50:53 GMT

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 ?

Re: Script Required

Santosh Nair_1 — Tue, 02 Oct 2001 19:59:34 GMT

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

Re: Script Required

linuxfan — Tue, 02 Oct 2001 20:04:56 GMT

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

Re: Script Required

A. Clay Stephenson — Tue, 02 Oct 2001 20:15:46 GMT

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

Re: Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 20:31:11 GMT

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

Re: Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 20:40:22 GMT

Pls. suggest me some modification in my script.

Re: Script Required

Mladen Despic — Tue, 02 Oct 2001 20:42:52 GMT

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

Re: Script Required

A. Clay Stephenson — Tue, 02 Oct 2001 21:04:52 GMT

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

Re: Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 21:07:58 GMT

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

Re: Script Required

A. Clay Stephenson — Tue, 02 Oct 2001 21:16:14 GMT

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.

Re: Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 21:26:01 GMT

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 .

Re: Script Required

Deepak Seth_1 — Tue, 02 Oct 2001 21:49:11 GMT

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.

Re: Script Required

Robert Binkley — Wed, 03 Oct 2001 14:46:20 GMT

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

Re: Script Required

Deepak Seth_1 — Wed, 03 Oct 2001 14:57:58 GMT

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

Re: Script Required

Wodisch — Thu, 04 Oct 2001 18:20:13 GMT

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

Re: Script Required

Deepak Seth_1 — Thu, 04 Oct 2001 19:09:32 GMT

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.