自动实时监控alert log文件并发送email提醒

本文主要讲述实时监控alert log文件并出现错误及时发送email提醒

一．下面这段建立外部表读alert log文件是从

http://www.adp-gmbh.ch/ora/admin/scripts/read_alert_log.html

找到的．

define alert_length="2000"

drop table alert_log;

create table alert_log (
  alert_date date,
  alert_text varchar2(&&alert_length)
)
create index alert_log_idx on alert_log(alert_date)
column db    new_value _DB    noprint;
column bdump new_value _bdump noprint;

select instance_name db from v$instance;

select value bdump from v$parameter
 where name ='background_dump_dest';

drop   directory BDUMP;
create directory BDUMP as '&&_bdump';

drop table alert_log_disk;

create table alert_log_disk ( text varchar2(&&alert_length) )
organization external (
  type oracle_loader
  default directory BDUMP
      access parameters (
          records delimited by newline nologfile nobadfile
          fields terminated by "&" ltrim
      )
  location('alert_&&_DB..log')
)
reject limit unlimited;

 

二．以下是实现过滤无用alert信息将结果写入alert_log表，以下过程参考上面的网址再经过改造成存储过程

create or replace procedure sp_data_monitor_alert_log Authid Current_User is
  /*
  此存储过程用于每天实时监控alert日志，如有错误自动发送email
  */

  isdate         number := 0;
  start_updating number := 0;
  rows_inserted  number := 0;
  alert_date     date;
  max_date       date;
  alert_text     alert_log_disk.text%type;
  v_alert_log    varchar2(5000);
  v_ora_count    number := 0;
  type Text_Type is table of varchar2(50);
  to_addr_list Text_Type :=Text_Type( 'xxx@xxxx.com.cn','xxx@xxxx.com.cn','xxxx@xxx.com.cn');--收件人
  v_html       varchar2(30000);
begin

  /* find a starting date */
  select max(alert_date) into max_date from alert_log;

  if (max_date is null) then
    --max_date := to_date('30-03-2011', 'dd-mm-yyyy');
    max_date := sysdate;
  end if;
 
 --过滤不需要的记录
  for r in (select substr(text, 1, 180) text
              from alert_log_disk
             where text not like '%offlining%'
               and text not like 'ARC_:%'
               and text not like '%LOG_ARCHIVE_DEST_1%'
               and text not like '%Thread 1 advanced to log sequence%'
               and text not like '%Current log#%seq#%mem#%'
               and text not like '%Undo Segment%lined%'
               and text not like '%alter tablespace%back%'
               and text not like
                   '%Log actively being archived by another process%'
               and text not like
                   '%alter database backup controlfile to trace%'
               and text not like '%Created Undo Segment%'
               and text not like '%started with pid%'
               and text not like '%ORA-12012%'
               and text not like '%ORA-06512%'
               and text not like '%ORA-000060:%'
               and text not like '%coalesce%'
               and text not like
                   '%Beginning log switch checkpoint up to RBA%'
               and text not like '%Completed checkpoint up to RBA%'
               and text not like '%specifies an obsolete parameter%'
               and text not like '%BEGIN BACKUP%'
               and text not like '%END BACKUP%'
               and (text like '%ORA-%' or
                   substr(text, 21) in ('2010', '2011', '2012', '2013'))
           
            ) loop
 
    isdate     := 0;
    alert_text := null;
  --日期
    select count(*)
      into isdate
      from dual
     where substr(r.text, 21) in ('2010', '2011', '2012', '2013')
       and r.text not like '%cycle_run_year%';
 
    if (isdate = 1) then
      SELECT TO_DATE(SUBSTR(R.TEXT, 5),
                     'Mon dd hh24:mi:ss rrrr',
                     'NLS_DATE_LANGUAGE = American')
        into alert_date
        from dual;   
    
      if (round((sysdate - alert_date) * 24 * 60) <= 5) then
        start_updating := 1;
      end if;
    else
      alert_text := r.text;
    end if;
 
    if (alert_text is not null) and (start_updating = 1) then
      insert into alert_log
      values
        (alert_date, substr(alert_text, 1, 180));
      rows_inserted := rows_inserted + 1;
   --错误列表拼接成html   
      v_alert_log := v_alert_log || '<tr><td style="width:150px">' || to_char(alert_date,'yyyy/mm/dd hh24:mi:ss') || '</td><td>' ||
                     alert_text || '</td></tr>';
    
      commit;
    end if;
  end loop;

  --打印输出
  sys.dbms_output.put_line('Inserting after date ' ||
                           to_char(max_date, 'MM/DD/RR HH24:MI:SS'));
  sys.dbms_output.put_line('Rows Inserted: ' || rows_inserted);

  --发送邮件
  if (length(v_alert_log) > 5) then
    v_html := '<table style="font-size:13px">' || v_alert_log || '</table>';
    for i in 1 .. to_addr_list.count loop
      sp_common_mail_html( 'xxx@xxxx.com.cn',
                          to_addr_list(i),
                          '数据中心实时alert日志监控！',
                          'test',
                          v_html,
                          'xx.xxxx.com.cn',
                          25);
    end loop;
 
  end if;

  commit;

end sp_data_monitor_alert_log;

三．将上面的存储过程sp_data_monitor_alert_log通过job隔几分钟运行一次
variable job1 number;
begin
  dbms_job.submit(:job1,'sp_data_monitor_alert_log;',trunc(sysdate)+5/1440, 'sysdate+51440');
  commit;
  end;

到此就实现了实时监控alert log啦！哈哈！周末愉快！

 

from: http://space.itpub.net/25989950/viewspace-712642