Script for monitoring listener

[PIYUSH D. PATEL_1]

Hi Everybody,

Does anybody have a customized script to monitor multiple listeners running on Oracle database server whether they are UP/running.

Thanks,
Piyush

[Zafar A. Mohammed_1]

Yes, but need airfare to Atlanta.

Zafar

[Zafar A. Mohammed_1]

Check this:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x4177ef70e827d711abdc0090277a778c,00.html

Thanks
Zafar

[PIYUSH D. PATEL_1]

Need something that would

1.parse the listener.ora file to capture listener names.

2. Store listener names from step 1 in a temporary file.

3. execute tnsping against each listener name from temporary file

Thanks,
Piyush

[Bill Douglass]

Install the Oracle client on your monitoring host and use tnsping. Check last line of output for an OK status.


ORACLE_HOME=/path/to/oracle $ORACLE_HOME/bin/tnsping

TNS Ping Utility for Linux: Version 9.2.0.1.0 - Production on 15-APR-2003 13:45:57

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
/home/oracle/OraHome2/network/admin/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=sasidw.))(ADDRESS=(PROTOCOL=TCP)(HOST=sasidw)(PORT=1521)))
OK (10 msec)


We are using the monitoring tool nagios (http://www.nagios.org) to monitoring multiple hosts and services. It includes a check_oracle plugin that uses tnsping.

[Geoff Wild]

Yes - you can do this with OVO/ITO....

Rgds...Geoff

[PIYUSH D. PATEL_1]

We are using ITO but the deafult script which comes with ITO (DBSPI templates) only monitors for LISTENER. But if we have multiple listeners runnning on the system then we need to find out if all of them are up and we can ping them.

Thanks,
Piyush

[Wayne Green]

Was asked to produce something similar for multiple instances on a SG cluster and didn't know of anything elegant at the time. Its not pretty but it works. The only instances we are interested in end in ??PR and we use BMC patrol to notify of a problem but you might be able to do something with it.

[PIYUSH D. PATEL_1]

Wayne, can you attach the script which you are using ?

Thanks....

[Wayne Green]

Thought I'd attached this and check the post for replies but didn't get either so although this is late I'll try now.

[Wayne Green]

That didn't work either so -

#!/usr/bin/sh
#
# to check oracle config file listener.ora and available datrabase instances
#
# directory location
# /tools/oracle/8.0.6/network/admin
# files
# listener.ora
#
FILE="listener.ora"
DIR=/tools/oracle/8.0.6/network/admin
BIN=/usr/bin/local
VAR=/var/patrol/monitor
LOG=$VAR/$(basename $0).log
HOST=$(hostname)
export ORACLE_HOME=/tools/oracle/8.0.6
export ORACLE_BIN=$ORACLE_HOME/bin
#
# cleanup log file
#
[ -f $LOG ] && tail -100 $LOG > ${LOG}.tmp && mv ${LOG}.tmp $LOG
#
#
# is server running a package
cmviewcl -l package | sed -e /^$/d -e /^.*NODE.*$/d| read PKG STATUS STATE PKG_SWITCH PKG_NODE
#
# set the tnsnames server
[ "x$PKG_NODE" = "x$HOST" ] && TNS_HOST=$PKG || echo "$(date "+%d/%m/%y %H:%M") - Package $PKG not running on this host" >> $LOG ; exit 0
#
# get a list of the SIDs that should be available according to
# the listener.ora file
SID_LIST=$(grep $TNS_HOST...PR $DIR/$FILE | awk -F. '{print $NF}' | sed 's/)//')
#
# check tsnping for each server
for SID in $SID_LIST
do
if $($ORACLE_BIN/tnsping $SID | grep -q OK)
then
echo "$(date "+%d/%m/%y %H:%M") - SID $SID is OK in $FILE" >> $LOG
else
echo "$(date "+%d/%m/%y %H:%M") - Error for SID $SID in $FILE" >> $LOG
$BIN/patrol_alert.3181 global "Error for SID $SID in $FILE"
fi
done