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
