Oracle 11g中使用ADR分析告警信息

悠悠 2022-02-05 06:23 255阅读 0赞

### 原文转载：http://blog.csdn.net/tianlesoftware/article/details/8222724 ###

### 1.1 ADR 说明 ###

ADR (AutomaticDiagnostic Repository) ，ADR 是一个基于文件的资料档案库，用于存放[数据库][Link 1]诊断数据（如跟踪、意外事件转储和程序包、预警日志、健康状况监视器报告、核心转储等）。它对存储在任何数据库外的多个实例和多种产品使用一个统一的目录结构。因此，可用于数据库关闭时的问题诊断。从[Oracle][] Database 11g R1 开始，数据库、自动存储管理(ASM)、集群就绪服务(CRS) 和其它[oracle][Oracle]产品或组件将所有诊断数据都存储在ADR 中。每种产品的每个实例都将诊断数据存储在自己的ADR 主目录下。例如，在具有共享存储和ASM 的Real Application Clusters 环境中，每个数据库实例和每个ASM 实例在ADR 中都有一个主目录。ADR的统一目录结构对各种产品和实例使用一致的诊断数据格式，而且还有一组统一的工具，使客户和Oracle 技术支持可以相互关联并分析多个实例的诊断数据。

从Oracle Database11g R1 开始，将忽略传统的…\_DUMP\_DEST 初始化参数。ADR 根目录又称为ADR 基目录，其位置由DIAGNOSTIC\_DEST 初始化参数设定。如果省略此参数或将其保留为空，数据库将在启动时按如下方式设置DIAGNOSTIC\_DEST：

如果已设置了环境变量ORACLE\_BASE，则将DIAGNOSTIC\_DEST 设置为$ORACLE\_BASE。

如果未设置环境变量ORACLE\_BASE，则将DIAGNOSTIC\_DEST 设置为$ORACLE\_HOME/log。

同样数据库警告日志文件也一样  
在oracle 11g中，你不能在目录$ORACLE\_BASE/diag/rdbms/$INSTANCE\_NAME/$ORACLE\_SID/alert中直接读取alert日志。  
而是读取其中的log.xml文件，该文件不是文本文件。  
除了所有标准的数据库操作信息外，alert日志里也包含关于事件的信息。  
你也可以从目录 $ORACLE\_BASE/diag/rdbms/$INSTANCE\_NAME/$ORACLE\_SID/trace中读取以前版本的alert日志。  
problems和incidents （问题和事件）  
围绕着oracle 11g错误诊断结构有两种新的概念：problems和incidents。  
problems：  
任何严重错误称之为problems，比如ORA-600错误，ORA-04031错误。所有涉及数据库problems的源数据都存储在ADR中。每一个问题分配一个关键字，以便于标识和描述问题。问题关键字包括oracle错误号和错误变量值。  
是一次问题的产生。一个问题可能会产生多次。每一次就是一个事件。每个事件有它自己的事件ID.  
当某一个事件产生时。在数据库里做三件事情：  
为该事件创建一个告警日志行并分配合适安全告警。  
在alert日志里创建相关条目。  
收集和存储诊断数据放置在ADR结构中合适子目录中。

ADR 基目录中可以包含多个ADR 主目录，其中每个ADR 主目录都是一个根目录，用于存放特定Oracle 产品或组件的特定实例的全部诊断数据。前一张幻灯片的图形中显示了数据库的ADR主目录位置。

另外，还生成了两个预警文件。一个是文本形式的预警文件（与早期版本Oracle DB 使用的预警文件非常相似），位于各个ADR 主目录的TRACE 目录下。还有一个符合XML 标准的预警消息文件，存储在ADR 主目录内的ALERT 子目录下。可使用EnterpriseManager 和ADRCI 实用程序查看文本格式的预警日志（已删除了XML 标记）。

INCIDENT 目录包含多个子目录，每个子目录均以特定意外事件命名，并且仅包含与该意外事件相关的转储。

HM 目录包含由健康状况监视器生成的检查器运行报告。

还有一个METADATA 目录，其中包含资料档案库自身的重要文件。可以将此目录比作数据库字典。可使用ADRCI 查询此字典。

ADR 命令解释器(ADRCI) 是一个实用程序，可用于执行支持工作台允许的所有任务（但是仅限于在命令行环境中）。使用ADRCI 实用程序，您还可以查看ADR 中跟踪文件的名称以及删除了XML 标记、具有和不具有内容筛选功能的预警日志。

此外，还可以使用V$DIAG\_INFO列出一些重要的ADR 位置。

![1353832178_8424.jpg][]

<table style="font-family:'Helvetica Neue', Helvetica, 'PingFang SC', 'Hiragino Sans GB', 'Microsoft YaHei', '微软雅黑', Arial, sans-serif;border-collapse:collapse;"> 
 <tbody style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
  <tr style="font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
   <td style="font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(175,175,175);"> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      1 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      2 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      3 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      4 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      5 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      6 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      7 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      8 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      9 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      10 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      11 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      12 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      13 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      14 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      15 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      16 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      17 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      18 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      19 
    </div> 
    <div style="font-size:1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;">
      20 
    </div> </td> 
   <td style="font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
    <div style="font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">SQL&gt;&nbsp;col&nbsp;name&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(255,20,147);">format</code>&nbsp; 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">a20</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">SQL&gt;&nbsp;col&nbsp;value&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(255,20,147);">format</code>&nbsp; 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">a70</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">SQL&gt;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(255,20,147);">set</code>&nbsp; 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">lin&nbsp;700</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">SQL&gt;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(255,20,147);">select</code>&nbsp; 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">name,value&nbsp;from&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:rgb(255,20,147);">v</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">$diag_info;</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUE</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">--------------------&nbsp;----------------------------------------------------------------------</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Diag&nbsp;Enabled&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TRUE</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">ADR&nbsp;Base&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">ADR&nbsp;Home&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Diag&nbsp;Trace&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/trace</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Diag&nbsp;Alert&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/alert</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Diag&nbsp;Incident&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/incident</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Diag&nbsp;Cdump&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/cdump</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Health&nbsp;Monitor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/hm</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Default&nbsp;Trace&nbsp;File&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">/oracle/app/oracle/diag/rdbms/udevasm/udevasm/trace/udevasm_ora_10122</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">.</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">trc</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUE</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">--------------------&nbsp;----------------------------------------------------------------------</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Active&nbsp;Problem&nbsp;Count&nbsp;0</code> 
     </div> 
     <div style="font-size:1em;background-image:none;border:0px;line-height:1.1em;vertical-align:baseline;"> 
      <code style="font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;background:none;border:0px;line-height:1.1em;vertical-align:baseline;color:#000000;">Active&nbsp;Incident&nbsp;Coun&nbsp;0</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

V$DIAG\_INFO 视图列出了所有重要的ADR 位置：

(1) ADR Base：ADR 基目录的路径

(2) ADR Home：当前数据库实例的ADR 主目录的路径

(3) Diag Trace：文本预警日志和后台/前台进程跟踪文件的位置

(4) Diag Alert：XML 版本的预警日志的位置

(5) …

(6) Default Trace File：会话的跟踪文件的路径。SQL 跟踪文件将写入到这里。

### 1.4 ADRCI：ADR 命令行工具 ###

ADRCI 是一个命令行工具，包含在Oracle Database 版本11g 中引入的故障可诊断性基础结构中。使用ADRCI，可以：

(1) 查看自动诊断资料档案库(ADR) 中的诊断数据。

(2) 将意外事件和问题信息打包成zip 文件，以传输到Oracle 技术支持。此操作是使用称为意外事件打包服务(IPS) 的服务完成的。

ADRCI 具有丰富的命令集，可在交互模式下使用，也可以在脚本中使用。此外，ADRCI 还可以使用与SQL\*Plus 执行SQL 脚本和PL/SQL 命令相同的方式执行ADRCI 命令脚本。

由于未打算保护ADR 中数据的安全，所以没有必要登录到ADRCI。ADR 数据仅受对ADR目录的[操作系统][Link 2]权限保护。

打包和以其它方式管理诊断数据的最简单方法是使用Oracle Enterprise Manager 的支持工作台。ADRCI 提供了一个可代替支持工作台大部分功能的命令行，并添加了一些功能，如列出和查询跟踪文件。

可以使用ADRCI 查看预警日志文件的内容。可以选择更改当前的ADR 主目录。使用SHOW HOMES 命令列出所有ADR 主目录，然后使用SET HOMEPATH 命令更改当前的ADR 主目录。

请确保已正确设置了操作系统环境变量（如ORACLE\_HOME），然后在操作系统命令提示符下输入以下命令：adrci。

然后，使用SHOW ALERT 命令。要限制输出，可以使用–TAIL 选项查看最近的记录。这将显示预警日志的最后部分（大约20 到30 条消息），然后等待更多消息进入预警日志中。这些刚刚进入到预警日志的消息也会显示出来。使用此命令，可以对预警日志进行实时监视。按CTRL-C 可停止等待并返回到ADRCI 提示符处。如果需要，还可以指定要打印的行数。

注：ADRCI 允许您将输出假脱机到文件中，与在SQL\*Plus 中使用的方法非常相似。

二．ADRCI 使用示例

### 2.1 帮助 ###

\[oracle@dave dave\]$ adrci

ADRCI: Release 11.2.0.3.0 - Production onThu Oct 11 18:34:29 2012

ADR base = "/u01/app/oracle"

adrci> show homepath

ADR Homes:

diag/rdbms/dave/dave

adrci> help

HELP\[topic\]

Available Topics:

CREATE REPORT

ECHO

EXIT

HELP

HOST

IPS

PURGE

RUN

SET BASE

SET BROWSER

SET CONTROL

SET ECHO

SET EDITOR

SET HOMES | HOME | HOMEPATH

SET TERMOUT

SHOW ALERT

SHOW BASE

SHOW CONTROL

SHOW HM\_RUN

SHOW HOMES | HOME | HOMEPATH

SHOW INCDIR

SHOW INCIDENT

SHOW PROBLEM

SHOW REPORT

SHOW TRACEFILE

SPOOL

There are other commands intended to beused directly by Oracle, type "HELP EXTENDED" to see the list

2.2 查看alert log

adrci> help show alert

Usage: SHOW ALERT \[-p <predicate\_string>\] \[-term\]

\[ \[-tail \[num\] \[-f\]\] |\[-file <alert\_file\_name>\] \]

Purpose: Show alert messages.

Options:

\[-p <predicate\_string>\]: The predicate string must bedouble-quoted.

The fields in the predicate are the fields:

ORIGINATING\_TIMESTAMP timestamp

NORMALIZED\_TIMESTAMP timestamp

ORGANIZATION\_ID text(65)

COMPONENT\_ID text(65)

HOST\_ID text(65)

HOST\_ADDRESS text(17)

MESSAGE\_TYPE number

MESSAGE\_LEVEL number

MESSAGE\_ID text(65)

MESSAGE\_GROUP text(65)

CLIENT\_ID text(65)

MODULE\_ID text(65)

PROCESS\_ID text(33)

THREAD\_ID text(65)

USER\_ID text(65)

INSTANCE\_ID text(65)

DETAILED\_LOCATION text(161)

UPSTREAM\_COMP\_ID text(101)

DOWNSTREAM\_COMP\_ID text(101)

EXECUTION\_CONTEXT\_ID text(101)

EXECUTION\_CONTEXT\_SEQUENCE number

ERROR\_INSTANCE\_ID number

ERROR\_INSTANCE\_SEQUENCE number

MESSAGE\_TEXT text(2049)

MESSAGE\_ARGUMENTS text(129)

SUPPLEMENTAL\_ATTRIBUTES text(129)

SUPPLEMENTAL\_DETAILS text(129)

PROBLEM\_KEY text(65)

\[-tail \[num\] \[-f\]\]: Output last part of the alert messages and

output latest messages as the alert log grows. If num is not specified,

the last 10 messages are displayed. If "-f" is specified, newdata

will append at the end as new alert messages are generated.

\[-term\]: Direct results to terminal. If this option is notspecified, the results will be open inan editor. By default, it will open in emacs, but "set editor" can beused to set other editors.

\--如果直接showalert ，会启用editor模式。

\[-file <alert\_file\_name>\]: Allow users to specify an alert filewhich

may not be in ADR. <alert\_file\_name> must be specified with fullpath.

Note that this option cannot be used with the -tail option

Examples:

show alert

show alert -p "message\_text like '%incident%'"

show alert -tail 20

**小技巧：**  
当有多个homepath的时候，我们在使用show alert 时会让我们选择。 这样不方便查看，我们可以直接先指定我们的homepath，这样就避免出现这种问题了。 如下：  
  
adrci> show homepath  
ADR Homes:  
diag/rdbms/dave/dave  
diag/tnslsnr/dave/listener  
adrci> show alert  
  
  
Choose the alert log from the following homes to view:  
  
  
1: diag/rdbms/dave/dave  
2: diag/tnslsnr/dave/listener  
Q: to quit  
  
  
Please select option: 1  
  
  
adrci> set homepath diag/rdbms/dave/dave  
adrci> show alert -tail 20

#### 2.2.1 显示最近10条alert log ####

adrci> showalert -tail

2012-10-11 17:40:04.975000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174004\], requested by (instance=1, osid=16650 (CJQ0)),summary=\[incident=13378\].

2012-10-11 17:40:08.193000 +08:00

Restarting dead background process CJQ0

CJQ0 started with pid=22, OS id=23205

Sweep \[inc\]\[13458\]: completed

2012-10-11 17:40:09.273000 +08:00

Sweep \[inc\]\[13378\]: completed

Sweep \[inc2\]\[13378\]: completed

2012-10-11 17:40:10.805000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174010\], requested by (instance=1, osid=18804),summary=\[incident=13458\].

2012-10-11 17:41:08.886000 +08:00

Sweep \[inc2\]\[13458\]: completed

2012-10-11 18:14:32.747000 +08:00

Time drift detected. Please check VKTMtrace file for more details.

#### 2.2.2 显示最近15条alert log ####

adrci> showalert -tail 15

2012-10-11 15:57:01.996000 +08:00

Time drift detected. Please check VKTMtrace file for more details.

2012-10-11 17:00:05.236000 +08:00

Time drift detected. Please check VKTMtrace file for more details.

2012-10-11 17:39:24.364000 +08:00

Thread 1 advanced to log sequence 36 (LGWRswitch)

Current log\# 3 seq\# 36 mem\# 0: /u01/app/oracle/oradata/anqing/redo03.log

2012-10-11 17:39:59.093000 +08:00

Exception \[type: SIGSEGV, Address notmapped to object\] \[ADDR:0x0\] \[PC:0x0, \{empty\}\] \[flags: 0x0, count: 1\]

2012-10-11 17:40:00.347000 +08:00

Errors in file /u01/app/oracle/diag/rdbms/dave/dave/trace/dave\_cjq0\_16650.trc (incident=13378):

ORA-07445: exception encountered: core dump\[PC:0x0\] \[SIGSEGV\] \[ADDR:0x0\] \[PC:0x0\] \[Address not mapped to object\] \[\]

Errors in file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave\_ora\_18804.trc (incident=13458):

ORA-07445: °é”è: è¨ \[PC:0x0\] \[SIGSEGV\] \[ADDR:0x0\] \[PC:0x0\] \[Address not mapped toobject\] \[\]

Incident details in:/u01/app/oracle/diag/rdbms/dave/dave/incident/incdir\_13458/dave\_ora\_18804\_i13458.trc

Incident details in:/u01/app/oracle/diag/rdbms/dave/dave/incident/incdir\_13378/dave\_cjq0\_16650\_i13378.trc

Use ADRCI or Support Workbench to packagethe incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

Use ADRCI or Support Workbench to packagethe incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2012-10-11 17:40:04.975000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174004\], requested by (instance=1, osid=16650 (CJQ0)),summary=\[incident=13378\].

2012-10-11 17:40:08.193000 +08:00

Restarting dead background process CJQ0

CJQ0 started with pid=22, OS id=23205

Sweep \[inc\]\[13458\]: completed

2012-10-11 17:40:09.273000 +08:00

Sweep \[inc\]\[13378\]: completed

Sweep \[inc2\]\[13378\]: completed

2012-10-11 17:40:10.805000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174010\], requested by (instance=1, osid=18804),summary=\[incident=13458\].

2012-10-11 17:41:08.886000 +08:00

Sweep \[inc2\]\[13458\]: completed

2012-10-11 18:14:32.747000 +08:00

Time drift detected. Please check VKTMtrace file for more details.

adrci>

#### 2.2.3 时时监控alert log ####

adrci> showalert -tail -f

2012-10-11 17:40:04.975000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174004\], requested by (instance=1, osid=16650 (CJQ0)),summary=\[incident=13378\].

2012-10-11 17:40:08.193000 +08:00

Restarting dead background process CJQ0

CJQ0 started with pid=22, OS id=23205

Sweep \[inc\]\[13458\]: completed

2012-10-11 17:40:09.273000 +08:00

Sweep \[inc\]\[13378\]: completed

Sweep \[inc2\]\[13378\]: completed

2012-10-11 17:40:10.805000 +08:00

Dumping diagnostic data indirectory=\[cdmp\_20121011174010\], requested by (instance=1, osid=18804),summary=\[incident=13458\].

2012-10-11 17:41:08.886000 +08:00

Sweep \[inc2\]\[13458\]: completed

2012-10-11 18:14:32.747000 +08:00

Time drift detected. Please check VKTMtrace file for more details.

\--该命令会显示最近10条记录，并时时更新新的log。 可以按CTRL-C来结束等待，返回ADRCI。

#### 2.2.4 查看所有的alert log ####

如果直接使用show alert，默认使用editor模式，如vi，所以退出使用vi的命令。可以指定-term参数，直接显示，而不是打开。 但这样，如果日志过长，不方便查看。

adrci> show alert

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Output the results to file:/tmp/alert\_26566\_1402\_dave\_1.ado

2012-09-07 13:12:22.622000 +08:00

Starting ORACLE instance (normal)

2012-09-07 13:12:24.456000 +08:00

LICENSE\_MAX\_SESSION = 0

LICENSE\_SESSIONS\_WARNING = 0

Shared memory segment for instancemonitoring created

Picked latch-free SCN scheme 3

Using LOG\_ARCHIVE\_DEST\_1 parameter defaultvalue as USE\_DB\_RECOVERY\_FILE\_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE\_MAX\_USERS = 0

**注意：**

show alert的起始点是在control中设置的保留时间之后的日志，或者说是purge之后的日志，之前的日志无法显示。后文有说明。

#### 2.2.5 查看具体的ORA-错误 ####

adrci> showalert -P "message\_text LIKE '%ORA-600%'"

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

adrci>

adrci> showalert -P "message\_text LIKE '%ORA-27037%'"

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Output the results to file:/tmp/alert\_26566\_1402\_dave\_7.ado

2012-09-07 13:21:06.387000 +08:00

Errors in file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave\_ora\_12435.trc:

ORA-00313: open failed for members of loggroup 1 of thread 1

ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/dave/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86\_64 Error: 2: No such file ordirectory

Additional information: 3

Errors in file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave\_ora\_12435.trc:

ORA-00313: open failed for members of loggroup 1 of thread 1

ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/dave/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86\_64 Error: 2: No such file ordirectory

…

### 2.3 查找trace files ###

通过ADRCI，我们可以查看ADR下的所有tracefiles，并可以对这些trace 文件进行过滤，只查看我们关注的信息。

如： 获取trace 文件包含‘mmon’字符串的文件：

adrci> show tracefile

diag/rdbms/dave/dave/trace/dave\_mmon\_16622.trc

diag/rdbms/dave/dave/trace/dave\_vktm\_16598.trc

diag/rdbms/dave/dave/trace/alert\_dave.log

diag/rdbms/dave/dave/incident/incdir\_8529/dave\_mmnl\_6039\_i8529.trc

diag/rdbms/dave/dave/incident/incdir\_13378/dave\_cjq0\_16650\_i13378.trc

diag/rdbms/dave/dave/incident/incdir\_13458/dave\_ora\_18804\_i13458.trc

adrci> showtracefile %mmon%

diag/rdbms/dave/dave/trace/dave\_mmon\_16622.trc

### 2.4 查看incident ###

adrci> show incident

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

INCIDENT\_ID PROBLEM\_KEY CREATE\_TIME

\------------------------------------------------------------------------------- ----------------------------------------

8529 ORA 7445 \[PC:0x0\] 2012-09-12 04:22:16.811000 +08:00

13378 ORA 7445 \[PC:0x0\] 2012-10-11 17:40:00.347000 +08:00

13458 ORA 7445 \[PC:0x0\] 2012-10-11 17:40:00.356000 +08:00

3 rows fetched

### 2.5 purge alert log ###

在2.2.4 节里，我们通过show alert 查看了所有的alert log信息。

show alert的起始点是在control中设置的保留时间之后的日志，或者说是purge之后的日志，之前的日志无法显示。

ADRCI的purge 命令会清空alert log的内容，注意这里的内容仅仅是XML的alert log。对于trace目录下的text 格式的alert log 不会进行清空。 我们在使用purge 时，可以指定alert log中entries保留的时间。 单位是分钟。

adrci> help purge

Usage: PURGE \[\[-i <id1> | <id1> <id2>\] |

Purpose: Purge the diagnostic data in the current ADR home. If no

option is specified, the default purging policy will be used.

Options:

\[-i id1 | id1 id2\]: Users can input a single incident ID, or a

range of incidents to purge.

\[-age <mins>\]: Users can specify the purging policy either to all

the diagnostic data or the specified type. The data older than<mins>

ago will be purged

Examples:

purge

purge -i 123 456

purge-age 60 -type incident

如清除超过60分钟前的alert log，命令如下：

adrci> purge -age 60 -type ALERT

adrci> show alert -term

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

这里已经没有日志了。 我们手工的制造一条日志。 在查看：

SQL> alter system switch logfile;

System altered.

adrci> show alert -term

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

2012-10-11 19:39:31.042000 +08:00

Thread 1 advanced to log sequence 37 (LGWRswitch)

Current log\# 1 seq\# 37 mem\# 0: /u01/app/oracle/oradata/anqing/redo01.log

\--这里显示了我们日志切换的记录。

### 2.6 修改ADR 的保留时间 ###

adrci> show control

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ADRID SHORTP\_POLICY LONGP\_POLICY

\-------------------- ----------------------------------------

3642307927 720 8760

1 rows fetched

\--之前格式有点乱，我调整了一下。

官网对这2个参数的解释：

<table style="font-family:'Helvetica Neue', Helvetica, 'PingFang SC', 'Hiragino Sans GB', 'Microsoft YaHei', '微软雅黑', Arial, sans-serif;font-size:16px;border-collapse:collapse;color:rgb(51,51,51);"> 
 <thead> 
  <tr> 
   <td style="border:1px solid rgb(221,221,221);"> <p>Attribute Name</p> </td> 
   <td style="border:1px solid rgb(221,221,221);"> <p>Description</p> </td> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="border:1px solid rgb(221,221,221);"> <p>SHORTP_POLICY</p> </td> 
   <td style="border:1px solid rgb(221,221,221);"> <p>Number of hours after which to purge ADR &nbsp;contents that have a short life.<span style="color:rgb(255,0,0);">&nbsp;Default is 720 (30 &nbsp;days).</span></p> <p>A setting of 0 (zero) means that all &nbsp;contents that have a short life can be purged. The maximum setting is &nbsp;35791394. If a value greater than 35791394 is specified, then this attribute &nbsp;is set to 0 (zero).</p> <p><span style="color:rgb(255,0,0);">The ADR contents that &nbsp;have a short life include the following:</span></p> <p>Trace files</p> <p>Core dump files</p> <p>Packaging information</p> </td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(221,221,221);"> <p>LONGP_POLICY</p> </td> 
   <td style="border:1px solid rgb(221,221,221);"> <p>Number of hours after which to purge ADR &nbsp;contents that have a long life.&nbsp;<span style="color:rgb(255,0,0);">Default is 8760 (365 &nbsp;days).</span></p> <p>A setting of 0 (zero) means that all &nbsp;contents that have a long life can be purged. The maximum setting is &nbsp;35791394. If a value greater than 35791394 is specified, then this attribute &nbsp;is set to 0 (zero).</p> <p><span style="color:rgb(255,0,0);">The ADR contents that &nbsp;have a long life include the following:</span></p> <p>Incident information</p> <p>Incident dumps</p> <p>Alert logs</p> </td> 
  </tr> 
 </tbody> 
</table>

SHORTP\_POLICY 默认是720小时，30天。其控制如下三种文件的保留时间：

(1) Trace files

(2) Core dump files

(3) Packaging information

LONGP\_POLICY默认值是8760小时，即365天，1年，其控制如下三种文件的保留时间：

(1) Incident information

(2) Incident dumps

(3) Alert logs

修改ADR的保留策略：

adrci> set control (SHORTP\_POLICY = 360)  
adrci> set control (LONGP\_POLICY = 2160)

adrci> show control

ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ADRID SHORTP\_POLICY LONGP\_POLICY

\-------------------- ----------------------------------------

3642307927 360 2160

1 rows fetched

关于ADRCI 的更多内容，参考官网手册：

[http://docs.oracle.com/cd/E11882\_01/server.112/e10701/adrci.htm][http_docs.oracle.com_cd_E11882_01_server.112_e10701_adrci.htm]

本文转自xiaocao1314051CTO博客，原文链接：http://blog.51cto.com/xiaocao13140/1947973 ，如需转载请自行联系原作者

[Link 1]: http://lib.csdn.net/base/mysql
[Oracle]: http://lib.csdn.net/base/oracle
[1353832178_8424.jpg]: https://img-my.csdn.net/uploads/201211/25/1353832178_8424.jpg
[Link 2]: http://lib.csdn.net/base/operatingsystem
[http_docs.oracle.com_cd_E11882_01_server.112_e10701_adrci.htm]: http://docs.oracle.com/cd/E11882_01/server.112/e10701/adrci.htm