- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Looking for Oracle Ping and Login tester so that I...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 07:23 AM
тАО04-22-2002 07:23 AM
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...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 07:30 AM
тАО04-22-2002 07:30 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
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 <
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}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 07:38 AM
тАО04-22-2002 07:38 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
I want to have a centrally maintained script that will check my databases that exist on 12+ servers.
thanks for the quick response.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 07:59 AM
тАО04-22-2002 07:59 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
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 <
export ORAENV_ASK=NO
export ORACLE_SID=PBSCS
. oraenv
svrmgrl <
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 11:40 AM
тАО04-22-2002 11:40 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
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
STATUS=$?
if [ -f $LOGFN2 ] ;then
echo " "
cat $LOGFN2
else
echo "ERMS3122: Error : $LOGFN2 file not created..."
grep "ERROR" $LOGFN1
fi
done
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 11:51 AM
тАО04-22-2002 11:51 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 05:49 PM
тАО04-22-2002 05:49 PM
Solutiontry 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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-22-2002 11:18 PM
тАО04-22-2002 11:18 PM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-23-2002 04:28 AM
тАО04-23-2002 04:28 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-23-2002 04:34 AM
тАО04-23-2002 04:34 AM
Re: Looking for Oracle Ping and Login tester so that I can detect down databases.
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...