Oracle 自動(dòng)診斷信息庫(Automatic Diagnostic Repository,ADR)

ORACLE 11G中的ADR介紹：

ADRCI 之oracle 日志查看
 在Oracle的11g版本中，alert文件的位置發(fā)生了變化，與此同時(shí)Oracle提供了一個(gè)命令行工具ADRCI，它可以很便利的查看數(shù)據(jù)庫中出現(xiàn)的異常。 以下給大家展示一下使用ADRCI (ADR Command Interface) 在11g中查看Oracle的警告日志的方法。

1.進(jìn)入到adrci命令行模式
ora11g@RHEL53 /home/oracle$ which adrci
/oracle/u01/app/oracle/product/1101/db/bin/adrci
ora11g@RHEL53 /home/oracle$ adrci -help
Syntax:
   adrci [-help] [script=script_filename]
         [exec = "one_command [;one_command;...]"]

Options      Description                     (Default)
------------------------------------------------------
script       script. file name               (None)
help         help on the command options     (None)
exec         exec a set of commands          (None)
------------------------------------------------------

ora11g@RHEL53 /home/oracle$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Feb 27 15:23:52 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = "/oracle/u01/app/oracle"
adrci>

2.使用 help show alert 命令查看幫助文檔
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 be used directly by Oracle, type
 "HELP EXTENDED" to see the list

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 be double 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, new data
    will append at the end as new alert messages are generated.

    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.

    [-file <alert_file_name>]: Allow users to specify an alert file which
    may not be in ADR. <alert_file_name> must be specified with full path.
    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

adrci>

3.使用 show alert 列出各個(gè)目錄下的日志目錄，輸入編號(hào)4，系統(tǒng)會(huì)自動(dòng)調(diào)用vi編輯器查看數(shù)據(jù)庫的alert日志
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/RHEL53/listener
2: diag/clients/user_unknown/host_411310321_11
3: diag/clients/user_oracle/host_2175824367_11
4: diag/rdbms/ora11g/ora11g
Q: to quit

Please select option:4

4.另外一種查看方式是，指定具體的 homepath 然后使用“show alert -tail 15”查看對(duì)應(yīng)日志文件的后15行
adrci> show homepath
ADR Homes:
diag/tnslsnr/RHEL53/listener
diag/clients/user_unknown/host_411310321_11
diag/clients/user_oracle/host_2175824367_11
diag/rdbms/ora11g/ora11g
adrci> set homepath diag/rdbms/ora11g/ora11g
adrci> show alert -tail 15
2009-02-27 14:25:05.036000 +08:00
Starting background process SMCO
SMCO started with pid=21, OS id=3855
Starting background process FBDA
FBDA started with pid=22, OS id=3857
replication_dependency_tracking turned off (no async multimaster replication found)
2009-02-27 14:25:07.246000 +08:00
Starting background process QMNC
QMNC started with pid=23, OS id=3859
2009-02-27 14:25:17.325000 +08:00
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2009-02-27 14:25:33.727000 +08:00
Completed: ALTER DATABASE OPEN
2009-02-27 14:29:59.158000 +08:00
Starting background process CJQ0
CJQ0 started with pid=25, OS id=3892
adrci>


ADR的結(jié)構(gòu)和位置：
   初始化參數(shù)diagnostic_dest指定了ADR的根目錄。即使你忽略了該初始化參數(shù)。oracle也會(huì)創(chuàng)建ADR。
如果你設(shè)置了ORACLE_BASE環(huán)境變量，那么diagnostic_dest缺省值是該目錄。如果你沒有設(shè)置ORACLE_BASE變量，該參數(shù)值為$ORACLE_HOME/log目錄。
   ADR存儲(chǔ)所有oracle產(chǎn)品的診斷數(shù)據(jù)。ADR為每一個(gè)oracle產(chǎn)品及其實(shí)例分配一個(gè)單獨(dú)的home目錄。因此一個(gè)ADR目錄包含多個(gè)home目錄。
   每一個(gè)ADR的home目錄是一個(gè)為每一個(gè)數(shù)據(jù)庫實(shí)例或其它oracle產(chǎn)品或組件存儲(chǔ)其診斷文件。其目錄結(jié)構(gòu)如下：
   ADR_base/diag/product_type/product_id/instance_id/
   比如一個(gè) diagnostic_dest初始化參數(shù)為 /u05/app/oracle。那么一個(gè)oracle數(shù)據(jù)庫的sid為prod1的home目錄就如下所示：
   /u05/app/oracle/diag/rdbms/prod1/prod1/
  在一個(gè)ADR的home目錄下有很多子目錄：

      alert：一個(gè)實(shí)例的告警日志文件（xml格式）。
      cdump：core文件。
      hm：健康檢查報(bào)告。
      incident：每一個(gè)事件一個(gè)子目錄，其中包含所有trace dump文件。
      incpkg：你創(chuàng)建的事件包，用于發(fā)給oracle支持所用。
      ir：包含實(shí)例事件報(bào)告。
      trace：存儲(chǔ)回話trace文件。
    可以通過視圖v$diag_info來查詢目錄及其位置：
    SQL> select name, value from v$diag_info;
    NAME                                    VALUE
    -------------------     ---------------------------
    Diag Enabled            TRUE
    ADR Base                /u01/app/oracle
    ADR Home                /01/app/oracle/diag/rdbms/prod/prod1
    Diag Trace              /u01/app/oracle/diag/rdbms/prod/prod1/trace
    Diag Alert              /u01/app/oracle/diag/rdbms/ prod/prod1/alert
    Diag Incident           /u01/app/oracle/diag/rdbms/prod/prod1/incident
    Diag Cdump              /u01/app/oracle/diag/rdbms/ prod/prod1/cdump
    Health Monitor          /u01/app/oracle/diag/rdbms/ prod/prod1/hm
    Default Trace File      /u01/app/oracle/diag/rdbms/ prod/prod1/
                            trace/eleven_ora_9417.trc
    Active Problem Count    3
    Active Incident Count   8

 正如你所見，視圖v$diag_info中包括問題和事件數(shù)量。
 
 
 
 
 比如使用如下命令查看所有ORA-錯(cuò)誤：
adrci> set homepath diag/rdbms/rac/rac
adrci> set editor vi
adrci> show alert -p "message_text like '%ORA-%'"
ADR Home = /home/oracle/diag/rdbms/rac/rac:
*************************************************************************
Output the results to file: /tmp/alert_10191_1638_rac_1.ado

2012-03-29 20:56:12.266000 +08:00
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
2012-03-29 20:56:27.810000 +08:00
Errors in file /home/oracle/diag/rdbms/rac/rac/trace/rac_ora_10453.trc:
ORA-00313: ??????? 1 (???? 1) ???
ORA-00312: ???? 1 ?? 1: '/home/oracle/oradata/rac/redo01.log'



像vi編輯器一樣退出后，可以在文件/tmp/alert_10191_1638_rac_1.ado中找到屏幕輸出結(jié)果。便于分析，
可見這個(gè)特性和unix中的grep |ORA-  命令是大不相同，因?yàn)楹笳咧荒茌敵鲥e(cuò)誤行，但是錯(cuò)誤行發(fā)生的時(shí)間卻不能顯示，而 show alert則同時(shí)顯示錯(cuò)誤和發(fā)生時(shí)間。



5.小結(jié)
  使用ADRCI命令可以很好的對(duì)各個(gè)目錄中的日志文件進(jìn)行集中查看，可以有效的提高工作效率。