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

妖狐艹你老母 2022-05-15 03:38 167阅读 0赞

\--================================================

\-- 使用外部表管理Oracle 告警日志(ALAERT\_$SID.LOG)

\--================================================

Oracle 告警日志时DBA维护数据库经常需要关注的一部分内容。然而告警日志以文本文件，按时间的先后顺序不断累积的形式来存储，久而

久之，势必造成告警日志的过大，难于维护和查找相关的信息。使用外表表方式来管理告警日志将大大简化维护工作量，也更直关的获取所需的

信息。

有关外部表的使用请参考：[Oracle 外部表][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;

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 DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

有关基于用户管理的备份和备份恢复的概念请参考：

[Oracle 冷备份][Oracle 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

[Oracle 基于用户管理恢复的处理][Oracle 5](详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考：

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 6]

[Oracle 密码文件][Oracle 7]

[Oracle 参数文件][Oracle 8]

[Oracle 数据库实例启动关闭过程][Oracle 9]

[Oracle 联机重做日志文件(ONLINE LOG FILE)][Oracle _ONLINE LOG FILE]

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 10]

[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078481.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 10]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx