General
cancel
Showing results for 
Search instead for 
Did you mean: 

Looking for Oracle Ping and Login tester so that I can detect down databases.

SOLVED
Go to solution
Jack C. Mahaffey
Super Advisor

Looking for Oracle Ping and Login tester so that I can detect down databases.

Does anyone have any HP-UX scripts to share that will detect down databases. I want to be able to 1) Verify that remote listeners are up and 2) Verify database instances are still up.

If either is down I'll want to send e-mail notification.

I don't think running TNSPING is sufficient because I'm not so sure that it returns an error if the database is hung.

thanks... jack...
11 REPLIES
MANOJ SRIVASTAVA
Honored Contributor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Hi Jack

Here is a script which will check for databases and give o/p as :


##################################################


Host: vsuncom

Database: PBSCS
Status: DB is UP

Database: PRTX
Status: DB is UP

Listener: UP

##################################################


here is the script that I use.

#!/usr/bin/ksh

## Set the following
SYSTEMPASS=`cat /home/oracle/cron/systempass` ## system password
LISTCOUNT=2 ## listener count
PATH=/usr/local/bin:${PATH} ## local bin dir.

## Setup some variables
OS=`uname -a|awk '{print $1}'`
case ${OS} in
HP-UX) ORATAB=/etc/oratab ;;
SunOS) ORATAB=/var/opt/oracle/oratab ;;
*) echo "\n\tNot a recognized OS!\n" ;;
esac
ID=`id|cut -f2 -d"("|cut -f1 -d")"`
MMDD=`date +%m%d`
TEMPFILE=/tmp/${ID}.${MMDD}.ora

echo "\n##################################################\n"

## Display host
HOST=`hostname`
echo "\n\tHost: ${HOST}"

## Check databases
cat ${ORATAB}|grep ":"|awk -F: '{print $1}'|grep -v "#"|while read ORACLE_SID
do

ORAENV_ASK=NO
. oraenv

echo "\n\tDatabase: ${ORACLE_SID}"

LGWRPROC=`ps -ef|grep ora_lgwr_${ORACLE_SID}|grep -v grep|awk '{print $NF"_"}'|grep ora_lgwr_${ORACLE_SID}_|wc -l|awk '{print $1}'`
if [ ${LGWRPROC} -eq 1 ] ; then

sqlplus -s /nolog <${TEMPFILE}
connect system/${SYSTEMPASS}
set pagesize 0
select count(*) from dba_tables;
select status,count(*) from v\$backup group by status;
EOF

if [ `grep "ORA-" ${TEMPFILE}|wc -l|awk '{print $1}'` -ge 1 ] ; then

echo "\tStatus: DB has ERRORS"

else
echo "\tStatus: DB is UP"

if [ `grep "ACTIVE" ${TEMPFILE}|grep -v "NOT"|wc -l|awk '{print $1}'` -eq 1 ] ; then

echo "\tWarning: Tablespaces in BACKUP mode!"

fi

fi

else
echo "\tStatus: DB is DOWN"

fi

done

## Check listener
LISTPROC=`ps -ef|grep tnslsnr|grep -v grep|wc -l|awk '{print $1}'`
if [ ${LISTPROC} -ge ${LISTCOUNT} ] ; then

echo "\n\tListener: UP"

else
if [ ${LISTPROC} -eq 0 ] ; then

echo "\n\tListener: DOWN"

else

echo "\n\tERROR: Not all listeners running!"

fi

fi

echo "\n##################################################\n"

rm -f ${TEMPFILE}
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Looks like script is driven by oratab. I need to be able to use the script to check remote database instances on other servers.

I want to have a centrally maintained script that will check my databases that exist on 12+ servers.

thanks for the quick response.

MANOJ SRIVASTAVA
Honored Contributor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Hi Jack


What I do is to have this script in bin of all seretrs and call it to excute remotely .Or you can jusr connect to the databse and check for a know table is the the o/p has some value then the database is up otherwise it is down. Here is another one which I use for my ITO .

#!/bin/sh
#**************************************************************************
#
# script name: pbscs_startup.sh
#
# date: 09/08/2000
#
#************************************************************************
hostname=`hostname`
logdate=`date +%m%d`

su - oracle <#. /home/oracle/.profile
export ORAENV_ASK=NO
export ORACLE_SID=PBSCS
. oraenv

svrmgrl <connect internal
spool /tmp/pbscscheck
select count(*) from user_tables;
spool off
exit
EOF1
EOF

so the test condition is if the file /tmp/pbscscheck has the no. of rows then the databse is up.


Manoj Srivastava
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

I have Oracle running on NT and HP-UX. remsh would not work in this case.

This is what I've put together so far... Haven't added code to scan the log file for success yet or do any emailing. Only problem with the script will be if the wrong password is sent because it will hang waiting for login and password.

#!/bin/sh

BNAME=`/usr/bin/basename $0`
NDNAME=`/usr/bin/uname -n`

for y in jet1 frd2
do
CURDB="${y}"
LOGFN1="/tmp/$BNAME.log"
LOGFN2="/tmp/check${CURDB}.log"
for n in $LOGFN1 $LOGFN2
do
echo $n
if [ -f $n ] ;then
echo "rm $n"
rm $n
fi
done

tnsping ${CURDB} | grep TNS | grep -v "TNS Ping"
sqlplus -s /@${CURDB} @check${CURDB}.sql 1>$LOGFN1 2>&1
STATUS=$?
if [ -f $LOGFN2 ] ;then
echo " "
cat $LOGFN2
else
echo "ERMS3122: Error : $LOGFN2 file not created..."
grep "ERROR" $LOGFN1
fi

done
harry d brown jr
Honored Contributor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.


http://www.saturn5.com/~jwb/dbi-examples.html



use strict;
use DBI;

my $dbh = DBI->connect( 'dbi:Oracle:orcl',
'jeffrey',
'jeffspassword',
) || die "Database connection not made: $DBI::errstr";
$dbh->disconnect();


no brainer in perl.

live free or die
harry
Live Free or Die
Printaporn_1
Esteemed Contributor
Solution

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Hi Jack,

try this : this is dbtouch script.

=======================
#!/bin/ksh
export HOME=/home/oracle

BASE_NAME=`basename $0`

if [ $# -lt 1 ]
then
echo "Check the availability of database"
echo "----------------------------------"
echo " Usage : ${BASE_NAME} [connection strings]"
echo "Example: ${BASE_NAME} CRMSPROD"
echo ""
exit 0
fi



# Uncomment the next line for debugging
# set -xv

HOME_DIR=`dirname $0`
if [ "${HOME_DIR}" = "." ]; then
HOME_DIR=`pwd`
fi


TMP_PATH=${HOME_DIR}/tempdir
if [ ! -d ${TMP_PATH} ]; then
mkdir -p ${TMP_PATH}
fi

ORACLE_USER=DBADMIN
#ORACLE_USER=DBADMIN
PWDFILE=$HOME/.${ORACLE_USER}
if [ ! -f ${PWDFILE} ]; then
echo "Password file '${PWDFILE}' not found."
echo ""
exit 1
fi

USER_PASSWORD=`cat ${PWDFILE} | head -1 | awk '{ print $1 }'`
if [ "${USER_PASSWORD}" = "" ]; then
echo "Can not find the password for user ${ORACLE_USER}."
echo ""
exit 1
fi
for ORACLE_SID in $*; do

TMP_FILE=${TMP_PATH}/${BASE_NAME}_${ORACLE_SID}.tmp
if [ -f ${TMP_FILE} ]; then
rm ${TMP_FILE}
fi

echo "Check database availability, ORACLE_SID:${ORACLE_SID}" | tee -a ${TMP_FILE}
echo "-------------------------------------------------" | tee -a ${TMP_FILE}


TRY_TIMES=1
NEW_LINE=0

while [ ${NEW_LINE} -le 0 -a ${TRY_TIMES} -le 3 ]; do
echo "Touching database ${ORACLE_SID} (Try ${TRY_TIMES}):" | tee -a ${TMP_FILE}
/u01/app/oracle/product/8.1.6/bin/sqlplus -s ${ORACLE_USER}/${US
ER_PASSWORD}@${ORACLE_SID} <<-@ | tee -a ${TMP_FILE}
set heading off
SELECT 'TOUCHED ==> ',
name
FROM V\$DATABASE;
@
echo "" | tee -a ${TMP_FILE}

date "+Finished: %Y.%m.%d-%H:%M:%S" | tee -a ${TMP_FILE}
echo "" | tee -a ${TMP_FILE}

NEW_LINE=`grep "TOUCHED ==> " ${TMP_FILE} | wc -l`
if [ ${NEW_LINE} -le 0 -a ${TRY_TIMES} -lt 3 ]; then
echo "Sleeping for 120 seconds ..." | tee -a ${TMP_FILE}
sleep 120
echo "" | tee -a ${TMP_FILE}
fi
TRY_TIMES=`expr ${TRY_TIMES} + 1`
done

if [ ${NEW_LINE} -le 0 ]; then
echo "Mail or Page DataBase Administrator ..."
date "+Run mail Database Admin at %Y.%m.%d-%H:%M:%S usin
g message \"${ORACLE_SID}@${HOSTNAME} is down\"" >> ${LOG_FILE}
TIMESTAMP=`date "+%m.%d-%H:%M"`
echo "${ORACLE_SID}@${HOSTNAME} goes down at ${TIMESTAMP
}" | mailx -s "Database Down" root
echo ""
fi

done

exit 0

=========================

you have to have user DBADMIN with password file /home/oracle/.DBADMIN

content of password file is just password string.

you can put in cron and check multiple database like

dbtouch db1 db2 db3

where db1,db2 and db3 is tnsname connection.

HTH,
enjoy any little thing in my life
Andreas D. Skjervold
Honored Contributor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Hi

Don't know if you have considered using the Oracle Enterprise Manager.
Installed on a central server the OEM manages and monitors nodes, listeners and databases.
Events can be set up to monitor essentially everything inside the database and on the node itself.
Such as Database down, SQL*Net down, spaceusage and resources.
Will give you notification by e-mail or pager. The last requires setup of a paging server as well.

The OEM uses the Intelligent Agent that is included in the Net8 product (listener) to monitor the system and notify the OEM server using snmp.

The OEM console can be installed on a different machine than the OEM server, allowing you to monitor your database from different locations or from home!

rgds
Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

Thanks for the perl script. I've had too many frustrations with perl. There seems be something always missing. The install instructions are not always clear. I need to take a good perl programming class to be more comfortable.
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

It looks like the dbtouch script will work for me. Thanks.... I'll likely add some email logic.


As for Oracle Enterprise Manager. There have been issues with using OEM in the past. When I go to failover on another server or change the IP address I pretty much lose the OEM jobs on the new server.

jack...
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.

I spoke too soon. The script does have email included. :)

Thanks...
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle Ping and Login tester so that I can detect down databases.