Friday, December 20, 2013

How to send a email if any ERROR occurs in oracle database alert log files in oracle 10g, 11g, 12c. Steps

 1) Create a external table for the alert log content.  
  How to create external table for alert log.  
 2) Create a view from this external table like...  
 ---------------------------------------------------------  
   CREATE OR REPLACE FORCE VIEW DBA_MAIN.VW_ALERT_LOG  
 (  
   NO_OF_LINE,  
   TEXT  
 )  
 AS  
   SELECT NO_of_line, text  
    FROM ( SELECT ROWNUM NO_of_line, text  
         FROM dba_main.alert_log_external  
       ORDER BY ROWNUM DESC)  
   WHERE UPPER (text) LIKE UPPER ('%ERROR%')   
 /  
 -----------------------------------------------------------  
 3) Create a table from this above view like....  
 -------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.TBL_ALERT_LOG_ERROR  
 AS  
   SELECT No_of_line, text, 'Y' send_flg FROM DBA_MAIN.VW_ALERT_LOG;  
 ---------------------------------------------------------------  
 3) Create a email address table and insert some email addresses like below...  
 ------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.EMAIL_ADDRESS  
 (  
  ID    NUMBER,  
  NAME   VARCHAR2(50 BYTE),  
  EMAIL   VARCHAR2(100 BYTE),  
  ACT_FLAG VARCHAR2(1 BYTE)          DEFAULT 'Y'  
 ) ;  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (1, 'Abdul Halim', 'halim@test.com', 'Y');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (2, 'user2', 'test@test.com', 'N');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (3, 'user1', 'test2@test.com', 'N');  
 COMMIT;  
 4) Create a package for sending email from the oracle database   
 (In this package, i created two procedures , one for sending email with attachment   
 another is sending email without attachment ment. I am using first one here)  
 -------------------------------------------------------------  
 SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
 -----------------------------------------------------------  
 4) create belows trigger on "DBA_MAIN.TBL_ALERT_LOG_ERROR" this table..  
 --------------------------------------------------------------------------  
 CREATE OR REPLACE TRIGGER DBA_MAIN.TRG_ALERT_LOG_ERROR_EMAIL  
 before INSERT ON DBA_MAIN.TBL_ALERT_LOG_ERROR  
 REFERENCING NEW AS new     
 FOR EACH ROW  
 WHEN (  
 new.send_flg='N'  
    )  
 begin  
 /*  
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( sender_email_address     varchar2, -- Must be single E-mail address  
      recipient_email_addresses  varchar2,   
      email_subject        varchar2,  
      email_body_text       varchar2,  
      email_attachment_text    clob,   -- No restriction on size of text  
      email_attachment_file_name  varchar2    
     )  
    */  
  for i in (select email from dba_main.email_address where act_flag='Y') loop   
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( 'halim@test.com', -- Must be single E-mail address  
      i.email,      'Test Email from Database!',  
      'Plz check the Alert for more info.... ' || :new.text,  
      :new.text,   -- No restriction on size of text  
      'Attention'   
     ) ;  
     end loop;  
 end;  
 /  
 -------------------------------------------------------------------------  
 5) create a procedure...for job....  
 ---------------------------------------------------------  
 CREATE OR REPLACE PROCEDURE dpr_alter_log_error_marge  
 IS  
 BEGIN  
   MERGE INTO TBL_ALERT_LOG_ERROR f_table  
     USING DBA_MAIN.VW_ALERT_LOG v_table  
       ON (v_table.text = f_table.text) --- and UPPER (v_table.text) LIKE UPPER ('%ERROR%') )  
   WHEN MATCHED  
   THEN  
    UPDATE SET f_table.SEND_FLG = 'Y'  
   WHEN NOT MATCHED  
   THEN  
    INSERT   (NO_OF_LINE, TEXT, SEND_FLG)  
      VALUES (ROWNUM, v_table.text, 'N');  
 END;  
 ----------------------------------------------------------  
 6) create a job like belows ( it will check alert log error and if found then send it email to DBA..  
 -----------------------------------------------------------  
 DECLARE  
  X NUMBER;  
 BEGIN  
  SYS.DBMS_JOB.SUBMIT  
  ( job    => X   
   ,what   => 'DBA_MAIN.DPR_ALTER_LOG_ERROR_MARGE;'  
   ,next_date => to_date('20/12/2013 10:55:34','dd/mm/yyyy hh24:mi:ss')  
   ,interval => 'SYSDATE+60/1440 '  
   ,no_parse => FALSE  
  );  
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));  
 COMMIT;  
 END;  
 /  
 ------------------------------------------------------------