shell scripts for tnsping

[Shivkumar]

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

[Arunvijai_4]

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

[Muthukumar_5 · Accepted solution]

#!/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.

[Muthukumar_5]

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.

[Arunvijai_4]

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

-Arun

[Yogeeraj_1]

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

[Frank de Vries]

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.

[Arturo Galbiati]

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 <