ADR(Automatic Diagnostic Repository)是Oracle 11g的一个存放数据库诊断日志、跟踪文件的目录,也作为ADR base。在实例中通过设置初始化参数DIAGNOSTIC_DEST来改变,这个参数是动态参数,修改这个参数后对应的目录和日志文件会立刻被生成并使用,默认在ORACLE_BASE下。

Oracle 11g同时提供了一个ADRCI(ADR Command-Line Utility)命令行工具来查看ADR中的alert日志和trace信息,并生成健康报告,ADRCI工具在$ORACLE_HOME/bin下面,安装oracle 11g以后默认就有·

1、登录adrci

[oracle@Web_121_127 ~]$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Mon Nov 27 10:38:30 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle/app/oracle"

2、设置ADR HOME
查看ADR HOME

adrci> show home
ADR Homes: 
diag/rdbms/powerdes/powerdes
diag/tnslsnr/Web_121_127/listener
diag/diagtool/user_oracle/host_363427775_80

设置并指定单一ADR HOME:

adrci> set homepath diag/tnslsnr/Web_121_127/listener
adrci> show home
ADR Homes: 
diag/tnslsnr/Web_121_127/listener

3、show alert
show alert用于格式化log.xml中的全部信息。输入普通alert_.log格式。保存到临时的文本文件中用编辑器打开。使用set editor vi 来指定编辑器(退出show alert显示就是关闭该编辑器。
用-tail获得最后20条的数据,或者指定一个大小

 show alert -tail 20

实时跟踪日志

show alert -tail -f 

-p选项可以筛选条件,如

show alert -tail -f -p "MESSAGE_TEXT like '%ORA-%'"

4、配置控制起点


adrci> show control

ADR Home = /oracle/app/oracle/diag/rdbms/powerdes/powerdes:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 
3414728576           720                  8760                 2017-06-21 13:46:14.637609 +08:00        2017-11-23 16:26:12.458787 +08:00        2017-11-25 16:20:15.399776 +08:00        1                    2                    80                   1                    2017-06-21 13:46:14.637609 +08:00       
1 rows fetched

SHORTP_POLICY trace file 保留时间, LONGP_POLICY incident信息保留时间,单位是小时。这两个参数可以使用下面两个命令修改:
set control (LONGP_POLICY = 2160)
set control (SHORTP_POLICY = 360)

5、清理数据

adrci> show problem

ADR Home = /oracle/app/oracle/diag/rdbms/powerdes/powerdes:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
-------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 
3                    ORA 7445 [kkorminl()+348]                                   216364               2017-11-20 22:00:18.227000 +08:00       
2                    ORA 7445 [kkorminl()+317]                                   216359               2017-11-26 13:37:37.993000 +08:00       
1                    ORA 7445 [kkorminl()+306]                                   240004               2017-11-27 01:38:13.972000 +08:00       
3 rows fetched

delete from problem where problem_id=3
删除incident同理:delete from incident where INCIDENT_ID=216364;

删除alter日志等文件推荐使用purge

adrci> help purge

  Usage: PURGE [[-i <id1> | <id1> <id2>] | 
               [-age <mins> [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]: 

  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

    [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of 
    data to be purged.

  Examples:  
    purge 
    purge -i 123 456
    purge -age 60 -type incident

ADRCI Purge命令的操作单元是文件而不是文件内容,只有整个文件的最后更新时间在我们指定的purge命令条件之前,该文件才会被删除,也就是说,要不整个 文件都删除,要不一点儿也不删除。
删除一天前的alert log

adrci> show homes
ADR Homes: 
diag/rdbms/powerdes/powerdes
diag/tnslsnr/Web_121_127/listener
adrci> set homepath diag/rdbms/powerdes/powerdes
adrci> purge -age 1440 -type ALERT
adrci> show alert

删除20分钟前的所有信息

 purge -age 20
带符号 * 的表示必填项