utl_smtp for sending emails: an example.

• The first section of the script checks four of the Oracle processes that must run on a UNIX system; if any of them is not found, the script sends an email using mailx and exits, because the instance is needed to check the three other events.
• The third section of the script determines the location of the archive logs directory and checks the available free space.
• The fourth section of the scrips looks for "ORA-" errors in the final part of the alert log file.
  
  This UNIX script will probably be defined in the crontab and executed at regular intervals.
  
  N.B. The escape sequence \$ is necessary on UNIX, or $session is interpreted as a shell variable. If the code is used directly in sqlplus, you must use v$session.

#!/bin/ksh
#
# FRS  Sends an email with utl_smtp when some Oracle critical condition occurs
#
# 2-FEB-2004 Creation
#
###########################################################################
if [ $# -ne 1 ]; then
  echo "Missing parameter "
  echo "Usage: sendTrap.ksh SID "
  exit 1
fi
SID=${1}
############################## Instance up ##################################
flg=`ps -ef | grep ${SID} | grep -c _lgwr'
fpm='ps -ef | grep ${SID} | grep -c _pmon'
fdb=`ps -ef | grep ${SID} | grep -c _dbw'
fsm=`ps -ef | grep ${SID} | grep -c _smo'
if [ $flg -ne 1 -o $fpm -ne 1 ]; then
  echo "The instance ${SID} is probably down" > /tmp/down
  mailx -s "Instance ${SID} down " support@front-row-seat.com  < /tmp/down
  exit 1
fi
sqlplus -s /nolog <<EOF_SQL 
  spool /tmp/trap.log
  connect / as sysdba
  set serverout on size 1000000
  DECLARE
    MIN_FREE_SPACE INTEGER := 20;
    freeMb         INTEGER;
    pctF           INTEGER;
    hostName       VARCHAR2(32);
    instanceName   VARCHAR2(16);
    message        VARCHAR2(254);
    crlf           VARCHAR2(2):= CHR(13) || CHR(10); 
    myConnection   sys.utl_smtp.connection;
    c              sys.utl_smtp.connection;
    myReply        sys.utl_smtp.reply;
    CURSOR selTbs IS
      SELECT tablespace_name, SUM(bytes)/1024/1024 AS mb
      FROM dba_data_files
      GROUP BY tablespace_name
      ORDER BY tablespace_name;
  BEGIN
    SELECT host_name, instance_name INTO hostName, instanceName
    FROM v\$instance;
    dbms_output.put_line('Entering the loop');
    FOR recTbs IN selTbs
    LOOP
--      dbms_output.put_line(recTbs.tablespace_name||'   '||TO_CHAR(recTbs.mb));
      SELECT SUM(bytes)/1024/1024 
      INTO freeMb
      FROM dba_free_space
      WHERE tablespace_name = recTbs.tablespace_name;
      pctF := ROUND((freeMb/recTbs.mb)*100,2);
      dbms_output.put_line(recTbs.tablespace_name||' '||TO_CHAR(recTbs.mb)||
	  ' '||TO_CHAR(freeMb)||' '||' '||TO_CHAR(pctF)||'% free');
      IF (pctF < MIN_FREE_SPACE) THEN
         dbms_output.put_line('========== Mailing ============');
         dbms_output.put_line('Trying the connection  ');
         c  := utl_smtp.open_connection('mail.front-row-seat.com');
         utl_smtp.helo(c, 'front-row-seat.com');
         utl_smtp.mail(c, 'oracle@myLocalserver.com');
         utl_smtp.rcpt(c, 'support@front-row-seat.com');
         utl_smtp.open_data(c);
         utl_smtp.write_data(c,'Subject' || ':' || recTbs.tablespace_name ||
		 ' is '||TO_CHAR(pctF)||'% free on '||instanceName||utl_tcp.CRLF);
         message := TO_CHAR(freeMb)||' MB are free out of '||TO_CHAR(recTbs.mb)||' MB';
         utl_smtp.write_data(c,utl_tcp.CRLF || 'On the server '||hostName||',
		 instance '||instanceName||' '||message);
         utl_smtp.close_data(c);
         utl_smtp.quit(c);
         dbms_output.put_line('The connection was ok  '||
		 TO_CHAR(myReply.code)||' '||myReply.text);
         dbms_output.put_line('Connection closed '||TO_CHAR(myReply.code)||
		 ' '||myReply.text);
      END IF;
    END LOOP;

  END;
/
spool off
spool /tmp/archiveDest.log
archive log list
spool off
spool /tmp/bdump.log
show parameter background_dump_dest
spool off
exit
EOF_SQL
echo "============================ Archive destination ========================"
archDest=`cat /tmp/archiveDest.log | grep 'Archive destination' | awk '{ print $3 }'`
cap=`df -k $archDest|awk '{ print $5 }' | sed "s/%//g"`
echo $cap"<"
freeK=`df -k $archDest|awk '{ print $4 }'`
echo $freeK
if [ $cap -gt 90 ]; then
  echo "The archive directory is $capacity full" > /tmp/arc.log
  mailx -s "Archive almost full" support@front-row-seat.com  < /tmp/arc.log
  rm /tmp/arc.log
fi
echo "============================= ALERT.LOG =============================="
ALERTLOG=`cat /tmp/bdump.log | grep background_dump_dest | awk '{ print $3 }'`
tail -100 $ALERTLOG | grep 'ORA-' > /tmp/alert.log
nL=`cat /tmp/alert.log|wc -l `
if [ $nL -gt 0 ]; then
  mailx -s "Alert log contains errors" support@front-row-seat.com  < /tmp/alert.log
  rm /tmp/alert.log
fi

• utl_smtp.open_connection('your mail server') is responsible for opening the connection.
• utl_smtp.helo(c, 'mail.front-row-seat.com'); starts the hand-shake with the mail server.
• utl_smtp.mail(c, 'oracle@myLocalserver.com'); specifies the sender.
• utl_smtp.rcpt(c, 'support@front-row-seat.com'); specifies the recepient.
• utl_smtp.open_data(c); opens the data flow.
• utl_smtp.write_data(c,'Subject' || ':' || recTbs.tablespace_name || ' is '||TO_CHAR(pctF)||'% free on '||instanceName||utl_tcp.CRLF); The first time, write_data is passed the subject of the email.
  message := TO_CHAR(freeMb)||' MB are free out of '||TO_CHAR(recTbs.mb)||' MB';
• utl_smtp.write_data(c,utl_tcp.CRLF || 'On the server '||hostName||', instance '||instanceName||' '||message); using write_data a second time the body of the message is passed to the email server.
• utl_smtp.close_data(c); closes the data flow.
• utl_smtp.quit(c); closes the connection.