--================================================
-- 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
--================================================
Oracle 告警日志时DBA维护数据库经常需要关注的一部分内容。然而告警日志以文本文件，按时间的先后顺序不断累积的形式来存储，久而
久之，势必造成告警日志的过大，难于维护和查找相关的信息。使用外表表方式来管理告警日志将大大简化维护工作量，也更直关的获取所需的
信息。
有关外部表的使用请参考：Oracle 外部表
一、告警日志的内容
消息和错误的类型(Types of messages and errors)
ORA-600内部错误(ORA-600 internal errors that need immediate support from Oracle's customer support )'
ORA-1578块损坏错误(ORA-1578 block corruption errors that require recovery)
ORA-12012(作业队列错误(ORA-12012 job queue errors)
实例启动关闭，恢复等信息(STARTUP & SHUTDOWN, and RECOVER statement execution messages)
特定的DDL命令(Certain CREATE, ALTER, & DROP statements )
影响表空间，数据文件及回滚段的命令(Statements that effect TABLESPACES, DATAFILES, and ROLLBACK SEGMENTS )
可持续的命令被挂起(When a resumable statement is suspended )
LGWR不能写入到日志文件(When log writer (LGWR) cannot write to a member of a group )
归档进程启动信息(When new Archiver Process (ARCn) is started )
调度进程的相关信息(Dispatcher information)
动态参数的修改信息(The occurrence of someone changing a dynamic parameter)
二、建立外部表
1.查看后台日志路径
sys@ORCL> show parameter %b%_dump_dest --此可以省略，在后面直接用脚本cre_ext_tb.sql 实现
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/oracle/admin/orcl/bdump
2.创建用户并赋予特定的权限，并创建数据库目录
sys@ORCL> create user usr1 identified by usr1 --创建帐户usr1
2 temporary tablespace temp
3 default tablespace users
4 quota unlimited on users;
sys@ORCL> grant connect,resource to usr1; --为帐户usr1授予connect,resource角色
sys@ORCL> grant create any directory to usr1; --为帐户usr1授予创建目录的权限
sys@ORCL> conn usr1/usr1 --使用usr1连接数据库
3.下面使用脚本来完成对告警日志的跟踪及管理
脚本描述
cre_ext_tb.sql
主要是创建了一个alert_log表用于存放告警日志的重要信息，一个外部表alert_log_disk使得查看告警日志可以直接在本地数据
库中完成。
update_alert_log.sql
用于从外部表将重要信息经过过滤并且将没有存放到alert_log表中的最新信息更新到alert_log表。
4.使用下面的脚本来创建alert_log表及alert_log_disk外部表
usr1@ORCL> get /u01/bk/scripts/cre_ext_tb.sql --查看建表的代码
1 define alert_length="500"
2 drop table alert_log;
3 create table alert_log ( --创建表alert_log用于存放告警日志的重要信息
4 alert_date date,
5 alert_text varchar2(&&alert_length)
6 )
7 storage (initial 512k next 512K pctincrease 0);
8 create index alert_log_idx on alert_log(alert_date) --为表alert_log创建索引
9 storage (initial 512k next 512K pctincrease 0);
10 column db new_value _DB noprint;
11 column bdump new_value _bdump noprint;
12 select instance_name db from v$instance; --获得实例名以及告警日志路径
13 select value bdump from v$parameter
14 where name ='background_dump_dest';
15 drop directory BDUMP;
16 create directory BDUMP as '&&_bdump';
17 drop table alert_log_disk;
18 create table alert_log_disk ( text varchar2(&&alert_length) ) --创建外部表
19 organization external (
20 type oracle_loader
21 default directory BDUMP
22 access parameters (
23 records delimited by newline nologfile nobadfile
24 fields terminated by "&" ltrim
25 )
26 location('alert_&&_DB..log')
27 )
28* reject limit unlimited;
usr1@ORCL> start /u01/bk/scripts/cre_ext_tb.sql --执行建表的代码
5.使用下面的脚本填充alert_log表
usr1@ORCL> get /u01/bk/scripts/update_alert_log.sql --脚本update_alert_log.sql用于将外部表的重要信息填充到alert_log
1 set serveroutput on
2 declare
3 isdate number := 0;
4 start_updating number := 0;
5 rows_inserted number := 0;
6 alert_date date;
7 max_date date;
8 alert_text alert_log_disk.text%type;
9 begin
10 /* find a starting date */
11 select max(alert_date) into max_date from alert_log;
12 if (max_date is null) then
13 max_date := to_date('01-jan-1980', 'dd-mon-yyyy');
14 end if;
15 for r in (
16 select substr(text,1,180) text from alert_log_disk --使用for循环从告警日志过滤信息
17 where text not like '%offlining%'
18 and text not like 'ARC_:%'
19 and text not like '%LOG_ARCHIVE_DEST_1%'
20 and text not like '%Thread 1 advanced to log sequence%'
21 and text not like '%Current log#%seq#%mem#%'
22 and text not like '%Undo Segment%lined%'
23 and text not like '%alter tablespace%back%'
24 and text not like '%Log actively being archived by another process%'
25 and text not like '%alter database backup controlfile to trace%'
26 and text not like '%Created Undo Segment%'
27 and text not like '%started with pid%'
28 and text not like '%ORA-12012%'
29 and text not like '%ORA-06512%'
30 and text not like '%ORA-000060:%'
31 and text not like '%coalesce%'
32 and text not like '%Beginning log switch checkpoint up to RBA%'
33 and text not like '%Completed checkpoint up to RBA%'
34 and text not like '%specifies an obsolete parameter%'
35 and text not like '%BEGIN BACKUP%'
36 and text not like '%END BACKUP%'
37 )
38 loop
39 isdate := 0;
40 alert_text := null;
41 select count(*) into isdate --设定标志位，用于判断改行是否为时间数据
42 from dual
43 where substr(r.text, 21) in ('2009','2010','2011','2012','2013')
44 and r.text not like '%cycle_run_year%';
45 if (isdate = 1) then --将时间数据格式化
46 select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr')
47 into alert_date
48 from dual;
49 if (alert_date > max_date) then --设定标志位用于判断是否需要update
50 start_updating := 1;
51 end if;
52 else
53 alert_text := r.text;
54 end if;
55 if (alert_text is not null) and (start_updating = 1) then --start_updating标志位与alert_text为真，插入记录
56 insert into alert_log values (alert_date, substr(alert_text, 1, 180));
57 rows_inserted := rows_inserted + 1;
58 commit;
59 end if;
60 end loop;
61 sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS'));
62 sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);
63 commit;
64* end;
65
usr1@ORCL> start /u01/bk/scripts/update_alert_log.sql
Inserting after date 01/01/80 00:00:00
Rows Inserted: 632
PL/SQL procedure successfully completed.
基于上述方法，可以定期将告警日志更新到本地数据库，然后清空告警日志文件
三、查看告警日志的内容
1.修改会话日期的显示格式
usr1@ORCL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
2.查看告警日志的信息
usr1@ORCL> select * from alert_log where rownum < 5;
ALERT_DATE ALERT_TEXT
------------------- --------------------------------------------------------------------------------
2011-02-14 21:36:11 SYS auditing is disabled
2011-02-14 21:36:11 ksdpec: called for event 13740 prior to event group initialization
2011-02-14 21:36:11 Starting up ORACLE RDBMS Version: 10.2.0.1.0.
2011-02-14 21:36:11 System parameters with non-default values:
3.查看告警日志最新的5条信息
usr1@ORCL> select * from alert_log where rownum < 5 order by alert_date desc;
ALERT_DATE ALERT_TEXT
------------------- --------------------------------------------------------------------------------
2011-02-14 21:36:11 SYS auditing is disabled
2011-02-14 21:36:11 ksdpec: called for event 13740 prior to event group initialization
2011-02-14 21:36:11 Starting up ORACLE RDBMS Version: 10.2.0.1.0.
2011-02-14 21:36:11 System parameters with non-default values:
4.查看告警日志ORA错误信息
usr1@ORCL> select * from alert_log where alert_text like 'ORA-%';
ALERT_DATE ALERT_TEXT
------------------- --------------------------------------------------------------------------------
2011-02-14 21:36:13 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'
2011-02-14 21:36:13 ORA-27037: unable to obtain file status
2011-02-14 21:36:13 ORA-205 signalled during: ALTER DATABASE MOUNT...
2011-02-14 21:36:23 ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
2011-02-14 21:36:27 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'