ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

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

原创 Linux操作系统 作者:beatony 时间:2011-07-29 11:27:00 0 删除 编辑
使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
分类: Oracle 其它特性 118人阅读 评论(0) 收藏 举报

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

-- 使用外部表管理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'

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-703467/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-05-18

  • 博文量
    192
  • 访问量
    455704