使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

--================================================

--使用外部表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>createuserusr1 identifiedbyusr1 --创建帐户usr1

2 temporary tablespace temp

3 defaulttablespace users

4 quota unlimitedonusers;

sys@ORCL>grantconnect,resourcetousr1; --为帐户usr1授予connect,resource角色

sys@ORCL>grantcreateanydirectorytousr1; --为帐户usr1授予创建目录的权限

sys@ORCL>conn usr1/usr1 --使用usr1连接数据库

3.下面使用脚本来完成对告警日志的跟踪及管理

脚本描述

cre_ext_tb.

主要是创建了一个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 droptablealert_log;

3 createtablealert_log( --创建表alert_log用于存放告警日志的重要信息

4 alert_date date,

5 alert_text varchar2(&&alert_length)

6 )

7 storage(initial 512k next 512K pctincrease 0);

8 createalert_log_idxonalert_log(alert_date) --为表alert_log创建索引

9 storage(initial 512k next 512K pctincrease 0);

10 columndb new_value _DB noprint;

11 columnbdump new_value _bdump noprint;

12 selectinstance_name dbfromv$instance; --获得实例名以及告警日志路径

13 selectvaluebdumpfromv$parameter

14 wherename='background_dump_dest';

15 drop directory BDUMP;

16 createdirectory BDUMPas'&&_bdump';

17 droptablealert_log_disk;

18 createtablealert_log_disk(textvarchar2(&&alert_length)) --创建外部表

19 organizationexternal(

20 typeoracle_loader

21 defaultdirectory BDUMP

22 access parameters(

23 records delimitedbynewline nologfile nobadfile

24 fields terminatedby"&"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 setserveroutputon

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 selectmax(alert_date)intomax_datefromalert_log;

12 if(max_dateisnull)then

13 max_date:=to_date('01-jan-1980','dd-mon-yyyy');

14 endif;

15 forrin(

16 selectsubstr(text,1,180)textfromalert_log_disk --使用for循环从告警日志过滤信息

17 wheretextnotlike'%offlining%'

18 andtextnotlike'ARC_:%'

19 andtextnotlike'%LOG_ARCHIVE_DEST_1%'

20 andtextnotlike'%Thread 1 advanced to log sequence%'

21 andtextnotlike'%Current log#%seq#%mem#%'

22 andtextnotlike'%Undo Segment%lined%'

23 andtextnotlike'%alter tablespace%back%'

24 andtextnotlike'%Log actively being archived by another process%'

25 andtextnotlike'%alter database backup controlfile to trace%'

26 andtextnotlike'%Created Undo Segment%'

27 andtextnotlike'%started with pid%'

28 andtextnotlike'%ORA-12012%'

29 andtextnotlike'%ORA-06512%'

30 andtextnotlike'%ORA-000060:%'

31 andtextnotlike'%coalesce%'

32 andtextnotlike'%Beginning log switch checkpoint up to RBA%'

33 andtextnotlike'%Completed checkpoint up to RBA%'

34 andtextnotlike'%specifies an obsolete parameter%'

35 andtextnotlike'%BEGIN BACKUP%'

36 andtextnotlike'%END BACKUP%'

37 )

38 loop

39 isdate :=0;

40 alert_text:=null;

41 selectcount(*)intoisdate --设定标志位，用于判断改行是否为时间数据

42 fromdual

43 wheresubstr(r.text,21)in('2009','2010','2011','2012','2013')

44 andr.textnotlike'%cycle_run_year%';

45 if(isdate=1)then --将时间数据格式化

46 selectto_date(substr(r.text,5),'Mon dd hh24:mi:ss rrrr')

47 intoalert_date

48 fromdual;

49 if(alert_date>max_date)then --设定标志位用于判断是否需要update

50 start_updating:=1;

51 endif;

52 else

53 alert_text:=r.text;

54 endif;

55 if(alert_textisnotnull)and(start_updating=1)then --start_updating标志位与alert_text为真，插入记录

56 insertintoalert_logvalues(alert_date,substr(alert_text,1,180));

57 rows_inserted:=rows_inserted+1;

58 commit;

59 endif;

60 endloop;

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/SQLproceduresuccessfully completed.

基于上述方法，可以定期将告警日志更新到本地数据库，然后清空告警日志文件

三、查看告警日志的内容

1.修改会话日期的显示格式

usr1@ORCL>altersessionsetnls_date_format='yyyy-mm-dd hh24:mi:ss';

2.查看告警日志的信息

usr1@ORCL>select*fromalert_logwhererownum<5;

ALERT_DATE ALERT_TEXT

------------------- --------------------------------------------------------------------------------

2011-02-14 21:36:11 SYS auditingisdisabled

2011-02-14 21:36:11 ksdpec:calledforevent13740 priortoeventgroupinitialization

2011-02-14 21:36:11 Starting up ORACLE RDBMS Version:10.2.0.1.0.

2011-02-14 21:36:11 System parameterswithnon-defaultvalues:

3.查看告警日志最新的5条信息

usr1@ORCL>select*fromalert_logwhererownum<5orderbyalert_datedesc;

ALERT_DATE ALERT_TEXT

------------------- --------------------------------------------------------------------------------

2011-02-14 21:36:11 SYS auditingisdisabled

2011-02-14 21:36:11 ksdpec:calledforevent13740 priortoeventgroupinitialization

2011-02-14 21:36:11 Starting up ORACLE RDBMS Version:10.2.0.1.0.

2011-02-14 21:36:11 System parameterswithnon-defaultvalues:

4.查看告警日志ORA错误信息

usr1@ORCL>select*fromalert_logwherealert_textlike'ORA-%';

ALERT_DATE ALERT_TEXT

------------------- --------------------------------------------------------------------------------

2011-02-14 21:36:13 ORA-00202:controlfile:'/u01/oracle/oradata/orcl/control03.ctl'

2011-02-14 21:36:13 ORA-27037:unabletoobtainfilestatus

2011-02-14 21:36:13 ORA-205 signalled during:ALTERDATABASE MOUNT...

2011-02-14 21:36:23 ORA-1507 signalled during:ALTERDATABASECLOSENORMAL...

2011-02-14 21:36:27 ORA-00202:controlfile:'/u01/oracle/oradata/orcl/control03.ctl'