Sending Oracle Errors Messages in Alert Log to Mail

A) Using oracle database to send error message

1) CREATE DIRECTORY ALERT_DIR AS '/home/app/oracle/diag/rdbms/test/TEST/trace';


2)

CREATE TABLE ALERTLOG_CONTENT
(
text_line varchar2(255)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY  ALERT_DIR
    ACCESS PARAMETERS
    (
        records delimited by newline
        fields
        REJECT ROWS WITH ALL NULL FIELDS
    )
    LOCATION
    (
        'alert_TEST.log'
    )
)
REJECT LIMIT unlimited

3)

CREATE TABLE  ALERT_LOG_ERRORS (
ID NUMBER(8),
TIMEID VARCHAR2(200),
ERROR_MSG VARCHAR2(4000),
CREATE_DATE DATE,
IS_MAILED VARCHAR2(2));

4)


CREATE OR REPLACE PROCEDURE SYS.SEND_MAIL IS
    mailhost   VARCHAR2(64) := '127.0.0.1';
    sender     VARCHAR2(64) := 'krishnasatya.121@gmail.com';
    recipient  VARCHAR2(64) := 'radhakrishna.kommuri@gmail.com';
    mail_conn  utl_smtp.connection;
    EXCPT_ERROR VARCHAR2(300);
    ALERT_ERR VARCHAR2(4000):=NULL;
    IDD NUMBER(10);
BEGIN

  
INSERT INTO ALERT_LOG_ERRORS
SELECT * FROM (
select rownum RWNUM,CASE WHEN TIMEID NOT LIKE '___ ___ __ __:__:__ 20__' THEN NULL ELSE TIMEID END TIMEID,text_line,SYSDATE AS CREATE_DATE,NULL AS IS_MAILED 
from (
select  text_line ,lag(text_line,1) over(order by 1)
  timeid from ALERTLOG_CONTENT
where
text_line like '___ ___ __ __:__:__ 20__' or text_line like 'Shutting down instance (%' or text_line like 'ORA-%' or text_line like 'Starting ORACLE instance %'
     )     
     where text_line like 'Shutting down instance (%' or text_line like 'ORA-%' or text_line like 'Starting ORACLE instance %')
          WHERE NOT EXISTS (SELECT 1 FROM ALERT_LOG_ERRORS WHERE ID=RWNUM);
         
          COMMIT;

FOR RC IN (SELECT ID AS IDD,TIMEID,ERROR_MSG FROM ALERT_LOG_ERRORS WHERE NVL(IS_MAILED,'N')!='Y') LOOP

IF NVL(LENGTH(ALERT_ERR),0)<=4000 THEN
ALERT_ERR:=ALERT_ERR||','||RC.IDD||' -- '||RC.TIMEID||' -- '||RC.ERROR_MSG||',';
IDD:=RC.IDD;

END IF;

END LOOP;

IF ALERT_ERR IS NOT NULL THEN

    mail_conn := utl_smtp.open_connection (mailhost, 25);
    utl_smtp.helo (mail_conn, mailhost);
    utl_smtp.mail (mail_conn, sender);
    utl_smtp.rcpt (mail_conn, recipient);
    utl_smtp.open_data (mail_conn);
    utl_smtp.write_data (mail_conn, 'Oracle Test Server got following Error Message # '||ALERT_ERR || chr(13));
    utl_smtp.close_data (mail_conn);
    utl_smtp.quit (mail_conn);

END IF;

FOR RC IN (SELECT ID AS IDD,TIMEID,ERROR_MSG FROM ALERT_LOG_ERRORS WHERE NVL(IS_MAILED,'N')!='Y') LOOP

    UPDATE ALERT_LOG_ERRORS SET IS_MAILED='Y' WHERE ID=RC.IDD AND RC.IDD<=IDD;
    COMMIT;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
EXCPT_ERROR:=SUBSTR(SQLERRM,1,200);
DBMS_OUTPUT.PUT_LINE(EXCPT_ERROR);
END;
/


5)

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'SEND_MAIL_ERROR_MSG',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'SEND_MAIL',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=MINUTELY;INTERVAL=15',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'SENDING ORACLE ERRORS TO MAIL FROM ALERT LOG');
END;
/


B ) Using shell script to send ORA errors to mail


#grep -A 2 -B 2 -n "ORA-" alert_vsftrac1.log > test_err.txt

if [ ! -e lines.txt ]
then
prev_n=0
pres_n=`cat test_err.txt|wc -l`
else
prev_n=`cat lines.txt`
pres_n=`cat test_err.txt|wc -l`
echo "prev_n = $prev_n , pres_n=$pres_n "
fi


if [ $prev_n -eq 0 ] ; then

tail -n 10 test_err.txt | mail -s "alert log errors "  abcd@gmail.com

echo $pres_n > lines.txt

elif [ $prev_n -lt $pres_n ]; then

sed -n "$prev_n,${pres_n}p" test_err.txt |mail -s "alert log erros" abcd@gmail.com

echo $pres_n > lines.txt

else

echo "nothing to display"

fi