ORACLE文件之警告文件（alert file）

警告文件（也称警告日志）就是数据库的日记，是个简单的文本文件，从数据库创建那一天就开始编写该文件。

ALERT FILE的位置：

>select name,value from v$diag_info; -- v$diag_info是oracle 11g新增的视图

NAME                 VALUE

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

Diag Enabled         TRUE

ADR Base             /u01/app/oracle

ADR Home             /u01/app/oracle/diag/rdbms/orcl/orcl

Diag Trace           /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Diag Alert           /u01/app/oracle/diag/rdbms/orcl/orcl/alert

Diag Incident        /u01/app/oracle/diag/rdbms/orcl/orcl/incident

Diag Cdump           /u01/app/oracle/diag/rdbms/orcl/orcl/cdump

ALERT FILE有两个版本：

  

1.位于/u01/app/oracle/diag/rdbms/orcl/orcl/trace下的 alert_orcl.log 文本文件形式

 2.位于/u01/app/oracle/diag/rdbms/orcl/orcl/alert下的 log.xml 的XML文件格式

ALERT FILE的查看方法：

  

对于XML文件格式的可以使用一些工具转换生成报告

对于文本文件格式，可以创建一个external table来查询查看：

>create or replace directory data_dir        

 as

 '/u01/app/oracle/diag/rdbms/orcl/orcl/trace'；--文本文件alert file所在目录

>create table alert_log

 ( text_line varchar2(255) )

 organization external

 (

  type oracle_load

  default directory data_dir

  access parameters

  (

   records delimited by newline

   fields

  )

  location ( 'alert_orcl.log' )  

 )

这样我们就可以随时查看了：

>select to_char(last_time,'yyyy-mm-dd HH24:MI:SS') shutdown,

       to_char(start_time,'yyyy-mm-dd HH24:MI:SS') startup,

       round((start_time-last_time)*24*60,2) mins_down,

       round((last_time-lag(start_time) over (order by r)),2) days_up,

       case when (lead(r) over (order by r) is null)

            then round((sysdate-start_time),2)

       end days_still_up

from(

      select r,

             to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,

             to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time

      from(

            select r,

                   text_line,

                   lag(text_line,1) over (order by r) start_time,

                   lag(text_line,2) over (order by r) last_time

            from(

                  select rownum r,text_line

                  from alert_log

                  where text_line like '___ ___ __ __:__:__ 20__'

                  or    text_line like 'Starting ORACLE instance %'

                )

          )

       where text_line like 'Starting ORACLE instance %'

    )

SHUTDOWN                  S   TARTUP                MINS_DOWN         DAYS_UP               DAYS_STILL_UP

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

                                     2013-01-24 00:48:45

2013-01-24 00:53:17   2013-01-24 00:53:17             0                              0

2013-01-24 00:53:35   2013-01-24 00:53:35             0                              0

2013-01-24 00:56:23   2013-01-24 00:56:23             0                              0

2013-01-24 01:00:21   2013-01-24 01:00:33            .2                              0

2013-01-24 01:04:48   2013-01-24 05:57:52         293.07                         0

当然这只是一个思路，alert file里有许多内容，可以参照相关文档了解，这样你就很容易写出查询语句来查看你所需要的信息了