Scripts to monitor database is up
Scenario is I do not have access to Oracle Enterprise Manager or any other monitoring utility and I need to make sure the database is up. Simply checking pmon is not enough, so logging in and querying the database is in my opinion the only proper way to ensure it is running.
The solution in a nutshell for me is to cron a query that runs every 5 minutes and writes a timestamp to a logfile. Another script is then run every 8 minutes to check the contents of the logfile and alert if the last successful timestamp was more than 10 minutes ago.
Script 1 - Log on to Database and write the Sysdate to a File
############################################################################ #!/bin/ksh # # Description : This script logs into a database and writes a # timestamp to a file # An accompanying script will then check to see # if the database was successfully connected to # # 13/12/2017 # nm # # #Usage: Set the Database Environment on with the right database ############################################################################ # Set Database Environment . /usr/local/env/ LOG=/oracle/logs/${ORACLE_SID}_database_upcheck.log sqlplus -s "/ as sysdba"<<DATA | tee -a $LOG set pagesize 0 set heading off set underline off set verify off set termout off set trimspool on set feedback off select to_char(sysdate,'YYYY-MM-DD HH24:MI') from dual; exit; DATA
Script 2 - Check the above logfile, Alert if last successful query of timestamp was more than 10 minutes ago and clean up the first 20 lines of the logfile to keep it clean
########################################################################### #!/bin/ksh # # Description : This script checks the datestamp entry # of the last two lines of a file and alerts # if the last value exceed 10 minutes # # # 13/12/2017 # nm # #Usage: Set environment variable for the Database ############################################################################ # Set environment for database . /usr/local/env/ LOG=/oracle/logs/${ORACLE_SID}_database_upcheck.log LOGALERT=/oracle/logs/${ORACLE_SID}_dbalert.log MAIL_LIST="email@company.com" sd=`date +"%Y-%m-%d %H:%M"` dt1=`sed 'x;$!d' $LOG` dt2=`sed '$!d' $LOG` echo -e "$sd Is the Current sysdate\n" > $LOGALERT echo -e "$dt1 Was the second last successful connection to the database\n" >> $LOGALERT echo -e "$dt2 Was the last successful connection to the database\n" >> $LOGALERT echo -e "====================================================================\n" >> $LOGALERT sdEpoch=`date -d "$sd" +%s` dt1Epoch=`date -d "$dt1" +%s` dt2Epoch=`date -d "$dt2" +%s` #echo $sdEpoch #echo $dt1Epoch #echo $dt2Epoch td1=$(($sdEpoch-$dt1Epoch)) td2=$(($sdEpoch-$dt2Epoch)) echo -e "Compared to sysdate the second last successful connection to the database was $td1 Seconds ago\n" >> $LOGALERT echo -e "Compared to sysdate the last successful connection to the database was $td2 Seconds ago\n" >> $LOGALERT echo -e "====================================================================\n" >> $LOGALERT if [ $td2 -gt 600 ]; then # echo $td2 mail -a /oracle/logs/${ORACLE_SID}_dbalert.log -s "${ORACLE_SID} DB ALERT: DB Down" $MAIL_LIST < $LOGALERT fi # Clean up Log file numlines=`wc -l $LOG | awk '{print $1}'` #echo $numlines if [ $numlines -gt 22 ]; then echo "cleaning log file" sed -i '1,20d' $LOG fi