使用外部表管理Oracle 告警日志.doc

使用外部表管理Oracle 告警日志-=-使用外部表管理Oracle告警日志(ALAERT_$SID.LOG)-= Oracle告警日志时DBA维护数据库经常需要关注的一部分内容。然而告警日志以文本文件，按时间的先后顺序不断累积的形式来存储，久而久之，势必造成告警日志的过大，难于维护和查找相关的信息。使用外表表方式来管理告警日志将大大简化维护工作量，也更直关的获取所需的信息。 有关外部表的使用请参考：Oracle 外部表 一、告警日志的内容 消息和错误的类型(Types of messages and errors) ORA-600内部错误(ORA-600 internal errors that need immediate support from Oracles 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.查看后台日志路径 sysORCL show parameter %b%_dump_dest -此可以省略，在后面直接用脚本cre_ext_tb.sql实现 NAME TYPE VALUE - - - background_dump_dest string /u01/oracle/admin/orcl/bdump 2.创建用户并赋予特定的权限，并创建数据库目录 sysORCL create user usr1 identified by usr1 -创建帐户usr1 2 temporary tablespace temp 3 default tablespace users 4 quota unlimited on users; sysORCL grant connect,resource to usr1; -为帐户usr1授予connect,resource角色 sysORCL grant create any directory to usr1; -为帐户usr1授予创建目录的权限 sysORCL 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外部表 usr1ORCL 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; usr1ORCL start /u01/bk/scripts/cre_ext_tb.sql -执行建表的代码 5.使用下面的脚本填充alert_log表 usr1ORCL 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 usr1ORCL 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.修改会话日期的显示格式 usr1ORCL alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss; 2.查看告警日志的信息 usr1ORCL select * from alert_log where rownum select * from alert_log where rownum 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 duri