监控oracle 的alert.log

流程：

定时将alert 部分内容取出来，通过外部表访问，然后以一个过程来分析外部表中内容，将严重警告信息插入到一个警告表中。

 

 


以oracle用户登录后

mkdir $HOME/check_agent


crontab 中
0 8 * * * $HOME/check_agent/cut_alert.sh


touch cut_alert.sh
chmod u+x cut_alert.sh
vi cut_alert.sh

# 获取最近的报警日志

export ALERTLOG=/oracle/admin/orcl/bdump/alert_orcl.log

tail -2000 $ALERTLOG > $HOME/check_agent/alert_temp.$$


sqlplus /nolog
conn /as sysdba
grant connect,resource to brjljk;
conn brjljk/brjljk

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;



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-05-2011', 'dd-mm-yyyy');
    END IF;

    FOR R IN (SELECT SUBSTR(TEXT, 1, 180) TEXT
                FROM ALERT_LOG_DISK r
               WHERE (TEXT LIKE 'ORA-%' --添加自己的过滤信息

                 OR TEXT LIKE 'WARNING%')
                 OR SUBSTR(R.TEXT, 21) IN ('2011',
                                    '2012',
                                    '2013',
                                    '2014'
                                    '2015',
                                    '2016'
                                    '2017',
                                    '2018',
                                    '2019',
                                    '2020')
              ) LOOP

      ISDATE := 0;
      ALERT_TEXT := NULL;

      SELECT COUNT(*)
        INTO ISDATE
        FROM DUAL
       WHERE SUBSTR(R.TEXT, 21) IN ('2011',
                                    '2012',
                                    '2013',
                                    '2014'
                                    '2015',
                                    '2016'
                                    '2017',
                                    '2018',
                                    '2019',
                                    '2020')
         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/RRHH24:MI:SS'));
    SYS.DBMS_OUTPUT.PUT_LINE('Rows Inserted: ' || ROWS_INSERTED);

    COMMIT;

  END;
END;


--

job 来定时刷新当前的alert 并清除旧的