--================================================
-- 使用外部表管理 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 基于用户管理恢复的处理 ( 详细描述了介质恢复及其处理 )
有关 RMAN 的恢复与管理请参考：
有关 Oracle 体系结构请参考：
Oracle 实例和Oracle 数据库(Oracle 体系结构)
