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

q666123

　　--================================================
　　-- 使用外部表管理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,>　　影响表空间，数据文件及回滚段的命令(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>　　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>>　　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:>
　　2011-02-14 21:36:23 ORA-1507 signalled during:>　　2011-02-14 21:36:27 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html