http://www.forum.askdba.org/ Forum for Oracle DBA's en Sun, 12 Feb 2012 10:32:52 +0000 http://www.forum.askdba.org/topic/db-and-listener-availability-script#post-158 Sat, 12 Jun 2010 11:04:23 +0000 Anonymous 158@http://www.forum.askdba.org/ <p>Nice thing shared but i cant recognize the instruction of"Oracle wallet" to store password will you make this clear to me that how to store password?? </p> http://www.forum.askdba.org/topic/db-and-listener-availability-script#post-134 Thu, 11 Mar 2010 06:13:39 +0000 Amit Bansal 134@http://www.forum.askdba.org/ <p>Hi,</p> <p>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.<br /> You can also use mailx function to send mail.<br /> I have hard-coded password , but you can also use Oracle wallet to store password. Refer to my blog post <a href="http://askdba.org/weblog/2009/09/using-oracle-wallet-to-execute-shell-scriptcron-without-hard-coded-oracle-database-password/"> here </a> for details</p> <p>You need to pass ORACLE_SID as parameter. So to schedule a cron to check every 10 mins, you need to specify like below</p> <pre> 00,10,20,30,40,50 * * * * sh /home/oracle/monitor/db_check.sh orcl </pre> <p>Find below the script. Any suggestions/improvements are welcome</p> <pre> ################################################################### ## Check Database Availability ################################################################### ##########Env Variables ############# export DBA_MAIL1='amit.bansal@askdba.org' export DBA_MAIL2='saurabh.sood@askdba.org' export ORACLE_HOME=/oracle/ora_db export TNS_ADMIN=$ORACLE_HOME/network/admin export BASE_DIR=/home/oracle/monitor export ORACLE_SID=$1 export LOG_FILE=$BASE_DIR/$ORACLE_SID.log export PATH=$ORACLE_HOME/bin:$PATH export machinename=<code>hostname</code> ################################### cd $BASE_DIR echo "<code>date</code> " &gt; $LOG_FILE echo "Oracle Database(s) Status <code>hostname</code> :\n" &gt;&gt; $LOG_FILE db_cnt=<code>ps -ef|grep pmon|grep $ORACLE_SID|wc -l</code> if [ $db_cnt -gt 0 ]; then echo "$ORACLE_SID is Up" &gt;&gt; $LOG_FILE else echo "$ORACLE_SID is Down" &gt;&gt; $LOG_FILE $ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" &lt;&lt;EOF &gt;&gt; $LOG_FILE alter session SET smtp_out_server = 'mailserv:100'; BEGIN UTL_MAIL.send( sender =&gt; 'webmaster@askdba.org' ,recipients =&gt; '${DBA_MAIL1}' ,cc =&gt; '${DBA_MAIL2}' ,subject =&gt; '${ORACLE_SID} is down on ${machinename}' ,message =&gt; '${ORACLE_SID} is Down on ${machinename}' ,mime_type =&gt; 'text/plain; charset=us-ascii' ,priority =&gt; 3); END; / exit EOF fi lsn_cnt=<code>ps -ef|grep tnslsnr|grep oracle|grep -v grep|wc -l</code> if [ $lsn_cnt -gt 0 ]; then echo "Listener is up" &gt;&gt;$LOG_FILE else echo "Listener is down" &gt;&gt;$LOG_FILE $ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" &lt;&lt;EOF &gt;&gt; $LOG_FILE alter session SET smtp_out_server = 'mailserv:100'; BEGIN UTL_MAIL.send( sender =&gt; 'webmaster@askdba.org' ,recipients =&gt; '${DBA_MAIL1}' ,cc =&gt; '${DBA_MAIL2}' ,subject =&gt; 'Listener is down on ${machinename}' ,message =&gt; 'Listener is Down on ${machinename}' ,mime_type =&gt; 'text; charset=us-ascii' ,priority =&gt; 3); END; / exit EOF fi </pre>