Below mentioned is a ready to use Linux script to check one or more Oracle database instance status and alert if connection is down. The script uses oracle sqlplus command and gv$instance table to capture the details. Copy the code below highlighted in blue , save it in a file and execute for output. In the below example - there are two DB instances ORAINST1 and ORAINST2 - you should modify this as per your need. Also note the oracle sys password "oracle" - same can be changed as per your installation.
#!/bin/sh
instance='$instance'
instancename="ORAINST1 ORAINST2"
status='OPEN'
echo -e "\n\033[1;44m Oracle DB Status \033[0m \n"
for i in $instancename
do
sqlplus -silent sys/oracle@bplora as sysdba >> /dev/null << EOF &
set feed off
set heading off
spool /tmp/dbstatus.txt
select status from gv$instance where INSTANCE_NAME in ('$i');
spool OFF
EOF
dbstatus=`cat /tmp/dbstatus.txt |tr -d " " |grep -v "^$"`
checkdbstatus=`echo $dbstatus |grep OPEN`
if [ $? -eq 0 ]
then
echo -e "\033[1;32m$i\t - $dbstatus\033[0m"
else
echo -e "\033[1;31mProblem with Oracle DB conection - $i - $dbstatus \033[0m"
fi
rm -f /tmp/dbstatus.txt
done
Hope this helps to some Linux beginners