月归档：十二月 2012

自动诊断存储库(Automatic Diagnostic Repository) 是oracle database 11g的一个新特性,它提供了一个中心化存储trace files, dumps, health monitor reports, alert logs等. 不同的产品和实例都有统一的目录结构, 把各自的诊断数据存储在自己的ADR_HOME里.从11gr1–>11gr2–>12cr1 adrci的功能逐渐增强,也确实需要关注下其最基本的功能,管理alert日志和trace文件.
adrci启动

[oracle@xifenfei ~]$ adrci

ADRCI: Release 12.1.0.0.2        on Sun Dec 16 15:27:51 2012

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

ADR base = "/u01/app/oracle"

adrci帮助

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 LOG
        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设置home
当adrci中含有了多个home目录的时候,需要手工设置home值才能够很好的运行该工具

adrci> show home
ADR Homes: 
diag/tnslsnr/xifenfei/listener
diag/tnslsnr/xifenfei/listener_a
diag/clients/user_oracle/host_2460950761_80
diag/rdbms/yxhe_pitr_xifenfei/yxhE
diag/rdbms/aief_pitr_xifenfei/aief
diag/rdbms/xifenfei/xff
diag/rdbms/xifenfei/xifenfei
diag/rdbms/iwcn_pitr_xifenfei/iwcn
diag/rdbms/abrn_pitr_xifenfei/aBrn
diag/rdbms/efqn_pitr_xifenfei/efqn
diag/rdbms/gwix_pitr_xifenfei/gwix
adrci> show alert -tail 1
DIA-48449: Tail alert can only apply to single ADR home
adrci> set home diag/rdbms/xifenfei/xff
adrci> show home
ADR Homes: 
diag/rdbms/xifenfei/xff

adrci help命令具体使用

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(4000)
        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查看alert日志
1.使用alert命令为例子
2.这里的tail n并非传统的tail 命令指定的行数结果

--显示tail 1日志
adrci> show alert -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633

--显示含PSP0行日志
adrci> show alert -p "message_text like '%PSP0%'"  

ADR Home = /u01/app/oracle/diag/rdbms/xifenfei/xff:
*************************************************************************
Output the results to file: /tmp/alert_26813_1400_xff_1.ado

2012-12-06 22:47:43.072000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=23605
2012-12-06 23:29:19.362000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=25006
2012-12-08 20:38:43.602000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3942
2012-12-12 21:24:19.874000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3673
2012-12-12 21:34:19.647000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=4254
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633

--含PSP0 tail 1行记录
adrci> show alert -p "message_text like '%PSP0%'" -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633

补充其他常用命令

--清理60分钟前alert
purge -age 60 -type  ALERT

--清理120分钟钱trace文件
purge -age 120 -type TRACE

--监控alert日志
SHOW ALERT -tail 1 -f

--查看trace文件
SHOW TRACEFILE -tr/t

--查看特定进程trace文件并排序
show tracefile %log% -tr

--查看特定spid进程
show tracefile %27476% 

补充说明:对于adr的进一步使用,可以通过使用help命令来逐步查询并且尝试操作,adrci中的help是非常强大工具.