Trigger to receive emails of critical alerts on Oracle Database

####### Create a table to log all error alerts ########

CREATE TABLE HOMELOG.ERROR_LOG
(
  TIMESTAMP      DATE,
  USERNAME       VARCHAR2(30 BYTE),
  INSTANCE       NUMBER,
  DATABASE_NAME  VARCHAR2(50 BYTE),
  ERROR_STACK    VARCHAR2(2000 BYTE),
  CLIENT         VARCHAR2(100 BYTE),
  IP_ADDRESS     VARCHAR2(100 BYTE),
  MODULE         VARCHAR2(200 BYTE),
  ERROR_ID       VARCHAR2(200 BYTE),
  ER_CODE        INTEGER
)


######## Create a table containing all oracle errors and add a field IS_CRITICAL which will define whether an alert is critical or not, default will be 'N' (not critical)

CREATE TABLE HOMELOG.ORAALERTLIST_DUMMY
(
  ALERTID      NVARCHAR2(255),
  DESCRIPTION  VARCHAR2(2000 BYTE),
  IS_CRITICAL  VARCHAR2(1 BYTE) DEFAULT 'N'
)

Insert a records which we will use for testing

INSERT INTO HOMELOG.ORAALERTLIST VALUES ('ORA-28009','connection as SYS should be as SYSDBA or SYSOPER','Y');

This is a dummy records, i will share full list of errors and alerts, you can load these in a table to use

######## Create a procedure which will be called by a trigger upon errors to insert records #######

CREATE OR REPLACE PROCEDURE SYS.INSERT_ON_ERROR
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_CLIENT VARCHAR2(100) := SYS_CONTEXT('USERENV', 'TERMINAL');
V_IP VARCHAR2(100) := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
V_MODULE VARCHAR2(100) := SYS_CONTEXT('USERENV', 'MODULE');
BEGIN
INSERT INTO HOMELOG.ERROR_LOG
VALUES
(SYSDATE,SYS.LOGIN_USER,SYS.INSTANCE_NUM,SYS.DATABASE_NAME,DBMS_UTILITY.FORMAT_ERROR_STACK,V_CLIENT,V_IP,V_MODULE,SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,0,9),ER_CODE_HOMELOG_1.NEXTVAL);
COMMIT;
END;
/


-------------------------------------------------------------------------------------


######### Creat a global trigger MAIL_CRITICAL_ERROR in SYS Schema ##########

CREATE OR REPLACE TRIGGER SYS.MAIL_CRITICAL_ERROR
  AFTER SERVERERROR ON DATABASE
DECLARE
  V_CLIENT VARCHAR2(100) := SYS_CONTEXT ('USERENV', 'TERMINAL');
  V_SERVICE VARCHAR2(100) := SYS_CONTEXT ('USERENV', 'SERVICE_NAME');
  V_MODULE VARCHAR2(100) := SYS_CONTEXT('USERENV', 'MODULE');
  V_DB VARCHAR2(25) := SYS_CONTEXT('USERENV', 'DB_NAME');
  V_INSTANCE VARCHAR2(25) := SYS_CONTEXT('USERENV', 'INSTANCE_NAME');
  V_ERROR_STACK varchar2(6000) :=DBMS_UTILITY.FORMAT_ERROR_STACK;
  V_ERROR_ID  varchar2(100)  := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,0,9);
  V_IS_CRITICAL VARCHAR2(1);
  V_FROM      VARCHAR2(80) := 'ORAALERTS@HOME.loc';
  V_RECIPIENT VARCHAR2(80) := 'farhat@HOME.loc';
  V_SUBJECT   VARCHAR2(80) := 'Oracle Database Alert';
  V_MAIL_HOST VARCHAR2(30) := 'MAIL.HOME.loc';
  V_MAIL_CONN UTL_SMTP.CONNECTION;
  MSG CLOB :='';
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
  BEGIN
  --********************************************************
  --IF ERROR IS CRITICAL PROCEDURE SYS.INSERT_ON_ERROR WILL LOG ERROR IN HOMELOG.ERROR_LOG TABLE AND EMAIL WILL BE SENT
  --********************************************************
  SELECT IS_CRITICAL INTO V_IS_CRITICAL FROM HOMELOG.ORAALERTLIST WHERE ALERTID= V_ERROR_ID;
  IF
  V_IS_CRITICAL = 'Y'
  THEN
  --INSERT A RECORD IN HOMELOG.ERROR_LOG TABLE
  SYS.INSERT_ON_ERROR;
  --START EMAIL SENDINGA
   MSG := MSG||' Dear';
 V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
   'DATE: '   || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||
   'FROM: '   || V_FROM || CRLF ||
   'SUBJECT: '|| V_SUBJECT || CRLF ||
   'TO: '     || V_RECIPIENT || CRLF ||
   CRLF ||
   'ERROR CODE' || CRLF || V_ERROR_ID ||
   ' ' ||'' || CRLF || '' || CRLF || 'Error Details ' || CRLF || V_ERROR_STACK || ' Occured on database ' || V_DB || CRLF ||' Instance Name ' || V_INSTANCE || ' Service Name ' || V_SERVICE
);
UTL_SMTP.QUIT(V_MAIL_CONN);
--SEND EMAIL FINISHED
--********************************************************
  --IF ERROR NOT CRITICAL PROCEDURE SYS.INSERT_ON_ERROR WILL ONLY LOG AN ERROR IN HOMELOG.ERROR_LOG TABLE
--********************************************************
   ELSE
SYS.INSERT_ON_ERROR;
END IF;  
EXCEPTION          
WHEN NO_DATA_FOUND
THEN
NULL;
--insert into HOMELOG.ALERT_TRG_NULL_LOG values (SYSTIMESTAMP, DBMS_UTILITY.FORMAT_ERROR_STACK || ' NOT LOGGED IN REF TABLE HOMELOG.ERROR_LOG'  );
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
   RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL: '||SQLERRM);    
  END MAIL_CRITICAL_ERROR;
/


------------------------------------------------------------------------------
######Now our trigger is ready it will log all errors in table HOMElog schema and if error in lookup table is categorized 'y' (critical) then it will send email on users emails

Open a command prompt and try connecting through user sys without sysdba privilages

C:\Users\farhat.HOME>sqlplus sys/ORACLE11g@HOMEstage

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 12:38:49 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

###########Check your email there should be one email

ERROR CODE
ORA-28009

ERROR DETAILS
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER  Occured on Database HOMEDB  Instance Name HOMEdb Service Name is HOMEstage.HOME.loc