Hi there,
i have created a shell script on a central Machine which checks for the status of databases running remotely on different servers.
i test it by a shell script which connects the remote database instance through tns entry and then executing the sql query.
if the instance is down or for some reason not accessible, query execution fails with Error (any error string will contain "ERROR", which i will grep and send mail).
since there are sometime higher resource consumption, even if database is not down, some time it gives some false alarm that DB is down.Hence to avoid these false alarm, after grepping the ERROR for the first time,i am making the shell script to wait for 15-20 seconds then connect to sqlplus and again grep the ERROR to make sure that DB is really down.
below is my script:
DBALIST="dbasupport@xyzcom;export DBALIST
export PATH=/usr/bin:/usr/sbin
if [ -f /usr/bin/less ]; then
export PAGER="/usr/bin/less -ins"
elif [ -f /usr/bin/more ]; then
export PAGER="/usr/bin/more -s"
fi
case ${SHELL} in
*bash)
typeset +x PS1="\u@\h:\w\\$ "
;;
esac
export ORACLE_HOME=/oracle11/oracle12/app/oracle12/product/12.2.0/dbhome_1
export ORACLE_BASE=/oracle11/oracle12/app/oracle12
export TNS_ADMIN=/oracle11/oracle12/app/oracle12/product/12.2.0/dbhome_1/network/admin
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export LD_LIBRARY_PATH
cat /dev/null/ > /tmp/alert1.txt
sqlplus -s system/*****@db1 << EOF >> /tmp/alert1.txt
select name from v\$database;
exit
EOF
First check==>if cat /tmp/alert1.txt | grep 'ERROR' >/dev/null 2>&1; then
(cat /dev/null/ > /tmp/alert1.txt
for i in {1..1}
do
sleep 5
echo "Hello" ====> to check this step is executed after 10 sec.
done
)
fi
echo "Hello World" ====> to check this step is executed after above block
Second Check==>sqlplus -s system/****@db1 << EOF >> /tmp/alert1.txt #execute successfully#
select name from v\$database;
exit
EOF
##IT DOES NOT EXECUTE BELOW BLOCK##
if cat /tmp/alert1.txt | grep 'ERROR' >/dev/null 2>&1; then
(cat /dev/null/ > /tmp/alert1.txt
echo "To:dbasupport@abc.com"
echo "MIME-Version: 1.0"
echo "Subject: Alert : Database Instance db1 is DOWN.Please Check Immediatly `date` "
echo "Content-Type: text/html"
echo "<body bgcolor="ACFFA2">"
echo "<font size=5 color=Red><b> Database Instance db1 is DOWN. `date` </b></font>"
)| sendmail -t
fi
bash-4.2$ cat /tmp/alert1.txt
ERROR: <=====================GREP THIS STRING
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
where i am doing wrong..spent a lot of time and effort but no luck..help is highly appreciated..
thaks