DBA必备，监控alert日志文件

  用到数据库经常会遇到数据库报的各种错误， 存储过程在执行中报错、job轮询报错、表空间不足等等错误引起的异常等都会影响生产，造成不必要的损失。

  但ORACLE 不会做事不管，它会很智能的将错误记录到ALERT日志文件中的。（我的 alert_SID.log存放位置：D:/oracle/product/10.2.0/admin/orcl/bdump/alert_orcl.log）。

  打开alert文件，哇塞还真是大啊，小的几十M大的就不说了。这找错误可是非常麻烦的，想要数据库给我们提醒也很困难啊。

 

  于是乎google之，找到解决办法（请参考http://www.adp-gmbh.ch/ora/concepts/alert_log.html，后者继续阅读本文）。当能够顺利读出我们想要的信息之后，就可以用自己编写的发送邮件过着发送短信的网关程序发出警告信息了。

 

 

 

具体操作：

  我们要创建两张表  read_alert and read_alert_disk

（原文地址： http://www.adp-gmbh.ch/ora/admin/scripts/read_alert_log.html）

 

    1） read_alert_disk is an external table and contains the content of the alert log .（一个外部表，包含了所有的alert日志信息，创建脚本请见 read_alert.sql）

    2） read_alert will be empty after this script has been executed. It is used by the update_alert_log script, shown further below.（一个记录我们需要的错误信息表，是从alert文件中提取出来的信息，开始为空，执行更新脚本后即载入最新的信息，这里的最新信息初始是01-01-1980之后的信息， 以后每次执行脚本都会载入最后一次日期之后的信息，创建脚本请见 update_read_alert.sql，在原文上作出了修改，改成存储过程，日后定时执行）。

   3） 最后我们查看下我们的两张表

--查看所有alert日志信息
SELECT * from alert_log_disk t;
--查看挑选出来的信息
SELECT * FROM alert_log t
WHERE t.alert_text LIKE '%ORA-%'
ORDER BY t.alert_date DESC;

代码部分：

/***************read_alert.sql*****************************************/

define alert_length="2000"

drop table alert_log;

create table alert_log (
  alert_date date,
  alert_text varchar2(&&alert_length)
)
storage (initial 512k next 512K pctincrease 0);

create index alert_log_idx on alert_log(alert_date)
storage (initial 512k next 512K pctincrease 0);

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;

/**********************update_alert_log.sql*****************************/

 

  CREATE OR REPLACE PROCEDURE UPDATE_ALERT_LOG AS
BEGIN
  DECLARE
 
    ISDATE         NUMBER := 0;
    START_UPDATING NUMBER := 0;
    ROWS_INSERTED  NUMBER := 0;
 
    ALERT_DATE DATE;
    MAX_DATE   DATE;
 
    ALERT_TEXT ALERT_LOG_DISK.TEXT%TYPE;
 
  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('01-01-1980', 'dd-mm-yyyy');
    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-%'
              ) LOOP
   
      ISDATE     := 0;
      ALERT_TEXT := NULL;
   
      SELECT COUNT(*)
        INTO ISDATE
        FROM DUAL
       WHERE SUBSTR(R.TEXT, 21) IN           
             ('2003',
              '2004',
              '2005',
              '2006',
              '2007',
              '2008',
              '2009',
              '2010',
              '2011')
         AND R.TEXT NOT LIKE '%cycle_run_year%'; --这里做过修改，添加了当前年份
   
      IF (ISDATE = 1) THEN
      --  DBMS_OUTPUT.PUT_LINE(SUBSTR(R.TEXT, 5));
        SELECT TO_DATE(SUBSTR(R.TEXT, 5), 'Mon dd hh24:mi:ss rrrr','NLS_DATE_LANGUAGE = American')- -这里做了修改，alert文件日期格式为英文格式
          INTO ALERT_DATE
          FROM DUAL;
     
        IF (ALERT_DATE > MAX_DATE) 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;
        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);
 
    COMMIT;
 
  END;
END;