[Amit Bansal]

Hi,

Find below a simple script to monitor db and listener. It uses utl_mail procedure to connect to a mailserver db and send a mail in case of failure.

You can also use mailx function to send mail.

I have hard-coded password , but you can also use Oracle wallet to store password. Refer to my blog post here for details

You need to pass ORACLE_SID as parameter. So to schedule a cron to check every 10 mins, you need to specify like below

<br />
00,10,20,30,40,50 * * * * sh /home/oracle/monitor/db_check.sh orcl<br />

Find below the script. Any suggestions/improvements are welcome

<br />
<br />
###################################################################<br />
## Check Database Availability<br />
###################################################################<br />
##########Env Variables #############<br />
export DBA_MAIL1='[email protected]'<br />
export DBA_MAIL2='[email protected]'<br />
export ORACLE_HOME=/oracle/ora_db<br />
export TNS_ADMIN=$ORACLE_HOME/network/admin<br />
export BASE_DIR=/home/oracle/monitor<br />
export ORACLE_SID=$1<br />
export LOG_FILE=$BASE_DIR/$ORACLE_SID.log<br />
export PATH=$ORACLE_HOME/bin:$PATH<br />
export machinename=<code>hostname</code><br />
###################################<br />
cd $BASE_DIR<br />
echo "<code>date</code>   " &gt; $LOG_FILE<br />
echo  "Oracle Database(s) Status <code>hostname</code> :n" &gt;&gt;  $LOG_FILE<br />
db_cnt=<code>ps -ef|grep pmon|grep $ORACLE_SID|wc -l</code><br />
if [ $db_cnt -gt 0 ]; then<br />
echo "$ORACLE_SID is Up" &gt;&gt; $LOG_FILE<br />
else<br />
echo "$ORACLE_SID is Down" &gt;&gt; $LOG_FILE<br />
$ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" &lt;&lt;EOF  &gt;&gt; $LOG_FILE<br />
alter session SET smtp_out_server = 'mailserv:100';<br />
BEGIN<br />
UTL_MAIL.send(<br />
sender =&gt; '[email protected]'<br />
,recipients =&gt; '${DBA_MAIL1}'<br />
,cc =&gt; '${DBA_MAIL2}'<br />
,subject =&gt; '${ORACLE_SID} is down on ${machinename}'<br />
,message =&gt; '${ORACLE_SID} is Down on ${machinename}'<br />
,mime_type =&gt; 'text/plain; charset=us-ascii'<br />
,priority =&gt; 3);<br />
END;<br />
/<br />
exit<br />
EOF<br />
fi<br />
<br />
lsn_cnt=<code>ps -ef|grep tnslsnr|grep oracle|grep -v grep|wc -l</code><br />
if [ $lsn_cnt -gt 0 ]; then<br />
echo "Listener is up" &gt;&gt;$LOG_FILE<br />
else<br />
echo "Listener is down" &gt;&gt;$LOG_FILE<br />
$ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" &lt;&lt;EOF  &gt;&gt; $LOG_FILE<br />
alter session SET smtp_out_server = 'mailserv:100';<br />
BEGIN<br />
UTL_MAIL.send(<br />
sender =&gt; '[email protected]'<br />
,recipients =&gt; '${DBA_MAIL1}'<br />
,cc =&gt; '${DBA_MAIL2}'<br />
,subject =&gt; 'Listener is down on ${machinename}'<br />
,message =&gt; 'Listener is Down on ${machinename}'<br />
,mime_type =&gt; 'text; charset=us-ascii'<br />
,priority =&gt; 3);<br />
END;<br />
/<br />
exit<br />
EOF<br />
fi<br />

[Author]

Posts