--================================================
-- 使用外部表管理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'
四、更多参考
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query、Flashback Table)
Oracle 闪回特性(Flashback Version、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考：
Oracle 冷备份
Oracle 热备份
Oracle 备份恢复概念
Oracle 实例恢复
Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)
有关RMAN的恢复与管理请参考：
RMAN 概述及其体系结构
RMAN 配置、监控与管理
RMAN 备份详解
RMAN 还原与恢复
有关Oracle体系结构请参考：
Oracle 实例和Oracle数据库(Oracle体系结构)
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 数据库实例启动关闭过程
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
