topic Re: shell scripts for tnsping in Operating System - HP-UX

shell scripts for tnsping

Shivkumar — Tue, 27 Sep 2005 01:03:25 GMT

Dear Sirs;

I need to know a shell script which can perform tnsping on 5 oracle database every 30 minutes and record the output in a file.

Thanks,
Shiv

Re: shell scripts for tnsping

Arunvijai_4 — Tue, 27 Sep 2005 01:06:51 GMT

Shiv,

You can use "ping" script to perform tnsping. Just replace ping with tnsping.. and change sleep to 1800.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=960333

-Arun

Re: shell scripts for tnsping

Muthukumar_5 — Tue, 27 Sep 2005 01:09:02 GMT

#!/bin/ksh
# tnsping.ksh

db1=
db2=
db3=
db4=
db5=
stime=1800

while [ 1 ]
do
tnsping $db1 >> /tmp/tnsping_$db1.log 2>&1
tnsping $db2 >> /tmp/tnsping_$db2.log 2>&1
tnsping $db3 >> /tmp/tnsping_$db3.log 2>&1
tnsping $db4 >> /tmp/tnsping_$db4.log 2>&1
tnsping $db5 >> /tmp/tnsping_$db5.log 2>&1
sleep $stime
done

# END
exit 0

Again you can use the script without sleep + while [ 1 ] with cron as,

0,30 * * * * /tmp/tnsping.ksh 1>/dev/null 2>&1

hth.

Re: shell scripts for tnsping

Muthukumar_5 — Tue, 27 Sep 2005 01:12:35 GMT

Arun,

We can not use the same script as your link. It is needed some modification to check the DB not hostname ;). tnsping will not use option of -c count also ;) :)

Shiv,

Running command line using sleep will not be a effecient way. Use cron schedular with the scripts.

hth.

Re: shell scripts for tnsping

Arunvijai_4 — Tue, 27 Sep 2005 01:16:00 GMT

Thanks for your information Muthukumar. ofcourse, you need to modify the script even a newbie can do that ;;)

-Arun

Re: shell scripts for tnsping

Yogeeraj_1 — Tue, 27 Sep 2005 01:28:19 GMT

hi shiv,

allow me also to add that tnsping only shows that "there be a listener on that port". It does not show that "the database you want that listener to hook you up with exists and is up"

in short, the listener can be up without the database being up!

I would suggest that you also include something like:
sqlplus /nolog </dev/null
connect scott/tiger@mydb
select to_char(sysdate,'dd/mm/yyyy hh24:mi') from dual;
exit;
EOF

kind regards
yogeeraj

Re: shell scripts for tnsping

Frank de Vries — Wed, 28 Sep 2005 00:59:30 GMT

I find the best solution
to do a dummy login like
create a list of remote connect descriptors for each db's to check.

Then for each db connect descriptor in the list do

sqlplus t/t@yourdb.world

and check for the return message which
exclusively should be only:

Enter user-name:
ERROR: ORA-01017: invalid username/password; logon denied

and do this 3 times with sleep pauses of 10 seconds intervals, thus where any success is success, to account for temporary time-outs
or other glitches.

If you would like the complete script let me
know.

Re: shell scripts for tnsping

Arturo Galbiati — Wed, 28 Sep 2005 07:06:18 GMT

Hi Shiv,
the tnsping really is used to check the /etc/tnsnames.ora syntax and if the listener of a remote server is up.
To be sure that teh databse be up you have to connect to it by listener using a sample script xx:

cat xx
remote_database=xx
sqlplus -s <