|
以oracle用户登录后
mkdir $home/check_agent
crontab 中
0 8 * * * $home/check_agent/cut_alert.sh
touch cut_alert.sh
chmod ux 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 并清除旧的
|
