ADR自动诊断信息库
1.ADR概念（链接介绍）
Document 422893.1 – 11g Understanding Automatic Diagnostic Repository.
Document 453125.1 – 11g Diagnosability: Frequently Asked Questions
《Database Administrator’s Guide》 第九章
2.ADR设定
Automatic Diagnostic Repository（ADR)是oracle 11g推出专门用于管理数据库运行日志和dump文件管理的一个
功能。10g之前的USER_DUMP_DEST,CORE_DUMP_DEST,BACKGROUND_DUMP_DEST参数都废弃，整合在指
定的目录中，DIAGNOSTIC_DEST默认指向$ORACLE_BASE,如果不存在$ORACLE_BASE,指定$ORACLE_HOME/log.
SQL> show parameter dia
NAME TYPE VALUE
———————————— ———– ——————————
diagnostic_dest string /oracle
SQL> !echo $ORACLE_BASE
/oracle
3.ADR的目录结构
ADR的目录存在多级结构，按照实例，功能区分，目录结构如下：
- 3.1 系统层面结构
- diag/
- |– rdbms
- | `– luda
- | `– luda
- | |– alert
- | |– cdump
- | |– hm
- | |– incident
- | |– incpkg
- | |– ir
- | |– lck
- | |– metadata
- | |– stage
- | |– sweep
- | `– trace
- |– tnslsnr
- |– luda
- |– listener
- |– alert
- |– cdump
- |– incident
- |– incpkg
- |– lck
- |– metadata
- |– stage
- |– sweep
- |– trace
- 3.2 数据库层面结构
在数据库层面可以通过视图v$diag_info查询ADR相关信息：
SQL> col value for a40
SQL> /INST_ID NAME VALUE
———- —————————————- —————————————-
1 Diag Enabled TRUE
1 ADR Base /oracle
1 ADR Home /oracle/diag/rdbms/luda/luda
1 Diag Trace /oracle/diag/rdbms/luda/luda/trace
1 Diag Alert /oracle/diag/rdbms/luda/luda/alert
1 Diag Incident /oracle/diag/rdbms/luda/luda/incident
1 Diag Cdump /oracle/diag/rdbms/luda/luda/cdump
1 Health Monitor /oracle/diag/rdbms/luda/luda/hm
1 Default Trace File /oracle/diag/rdbms/luda/luda/trace/luda_
ora_13859.trcINST_ID NAME VALUE
———- —————————————- —————————————-
1 Active Problem Count 0
1 Active Incident Count 011 rows selected.
SQL> l
1 select * from v$diag_info
- 3.3 目录存储信息
alter：包含实例的预警日志（XML格式）；
cdump：包含核心文件；
hm：包含Health Monitor报表；
incident：包含每个意外事件的子目录，每个意外事件的子目录中还包含该意外事件的所有跟踪转储；
incpkg：包含你为上传到Oracle Support而创建的意外事件包；
ir：包含为每个实例创建的意外事件报表；
trace：存储用户会话跟踪文件。
4.ADRCI接口
Oracle提供了一个名为adrci的新命令行工具，用来帮助挖掘存储在ADR中的诊断数据。除了让你查看数据库存储在
ADR中的诊断数据外，adrci还提供了其他一些重要功能，它允许把意外事件和问题信息打包成ZIP文件发送给
Oracle Support。诊断数据包括跟踪和转储文件、预警日志条目以及Oracle Database 11g的新Health Monitor报
表等
- 4.1登陆ADRCI
[oracle@luda 17:42:27|/oracle]adrci
ADRCI: Release 11.2.0.1.0 – Production on Wed Nov 14 17:42:31 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = “/oracle”
adrci> helpHELP [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
SPOOLThere are other commands intended to be used directly by Oracle, type
“HELP EXTENDED” to see the list
- 4.2 alert日志信息查询
adrci> show home
ADR Homes:
diag/rdbms/luda/luda
diag/tnslsnr/luda/listener
adrci> set home diag/rdbms/luda/luda
adrci> show alert -tail
2012-11-14 11:20:40.846000 +08:00
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2012-11-14 11:20:42.561000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2012-11-14 11:20:44.834000 +08:00
Starting background process QMNC
QMNC started with pid=27, OS id=6792
2012-11-14 11:20:47.933000 +08:00
Completed: ALTER DATABASE OPEN
2012-11-14 11:20:50.439000 +08:00
Starting background process CJQ0
CJQ0 started with pid=32, OS id=6843
2012-11-14 11:25:45.943000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=6956adrci> show alert -tail -f
2012-11-14 11:20:40.846000 +08:00
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2012-11-14 11:20:42.561000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2012-11-14 11:20:44.834000 +08:00
Starting background process QMNC
QMNC started with pid=27, OS id=6792
2012-11-14 11:20:47.933000 +08:00
Completed: ALTER DATABASE OPEN
2012-11-14 11:20:50.439000 +08:00
Starting background process CJQ0
CJQ0 started with pid=32, OS id=6843
2012-11-14 11:25:45.943000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=6956
DIA-48223: Interrupt Requested – Fetch Aborted – Return Code [1] [ALERT_EXT]
- 4.3 事件查询以及执行状态监控
查看未解决事件
adrci> show incident
ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
0 rows fetched
健康监控状态监控
*SQLPLUS下面
SQL> BEGIN
2 DBMS_HM.RUN_CHECK(‘Dictionary Integrity Check’, ‘my_run’);
3 END;
4 /PL/SQL procedure successfully completed.
*ADRCI下面
adrci> show hm_run
ADR Home = /oracle/diag/rdbms/luda/luda:
***********************************************************************************************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 1
RUN_NAME HM_RUN_1
CHECK_NAME DB Structure Integrity Check
NAME_ID 2
MODE 2
START_TIME 2012-11-13 14:30:33.946816 +08:00
RESUME_TIME <NULL>
END_TIME 2012-11-13 14:30:40.052261 +08:00
MODIFIED_TIME 2012-11-13 14:30:40.052261 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE <NULL>**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID 21
RUN_NAME my_run
CHECK_NAME Dictionary Integrity Check
NAME_ID 24
MODE 0
START_TIME 2012-11-14 21:24:32.078107 +08:00
RESUME_TIME <NULL>
END_TIME 2012-11-14 21:24:36.521996 +08:00
MODIFIED_TIME 2012-11-14 21:24:36.521996 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE <NULL>
2 rows fetched
- 4.4诊断信息删除
默认情况下ADR的删除策略如下：
SHORT_POLICY:720小时（30天）
LONG_POLICY：365天
删除策略确认：
adrci> show control
ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME
——————– ——————– ——————– —————————————-
2683996405 720 8760 2012-11-13 14:28:14.919439 +08:00
1 rows fetched
设置删除策略
adrci> set control (SHORTP_POLICY = 3600)
adrci> set control (LONGP_POLICY = 7200)
adrci> show controlADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME
——————– ——————– ——————– —————————————-
2683996405 3600 7200 2012-11-14 21:31:54.271020 +08:00
1 rows fetched
删除策略对应的归类（目录）：
LONGP_POLICY 删除策略对应长时间保留对象，为如下归类：
ALERT
INCIDENT
SWEEP
STAGE
HM
SHORTP_POLICY 删除策略对应短时间保留对象，为如下归类：
TRACE
CDUMP
UTSCDMP
IPS
（在11.1.0.7之前监听日志和trace文件的是不在ADR删除范围之内，11.2版本之后解决。）
- 4.5诊断信息删除
在ADRCI中是用purge命令手工删除信息，purge命令语法如下：
purge:诊断信息删除
purge -i <incident_id>:指定事件ID进行删除
purge -age <value(分単位)>:指定时间范围进行删除
purge -age <value> -type <type>:指定删除特定时间范围内的指定类型文件
命令示例：
purge -age 60 -type ALERT
在11g里使用ADRCI删除归档信息只删除XML格式的告警日志，文本格式的告警日志不会被删除，oracle归类为BUG6800147
删除健康监控状态信息：
adrci> show hm_run
ADR Home = /oracle/diag/rdbms/luda/luda:
***********************************************************************************************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 1
RUN_NAME HM_RUN_1
CHECK_NAME DB Structure Integrity Check
NAME_ID 2
MODE 2
START_TIME 2012-11-13 14:30:33.946816 +08:00
RESUME_TIME <NULL>
END_TIME 2012-11-13 14:30:40.052261 +08:00
MODIFIED_TIME 2012-11-13 14:30:40.052261 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE <NULL>**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID 21
RUN_NAME my_run
CHECK_NAME Dictionary Integrity Check
NAME_ID 24
MODE 0
START_TIME 2012-11-14 21:24:32.078107 +08:00
RESUME_TIME <NULL>
END_TIME 2012-11-14 21:24:36.521996 +08:00
MODIFIED_TIME 2012-11-14 21:24:36.521996 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE <NULL>
2 rows fetchedadrci> purge -age 1 -type hm
adrci> show hm_runADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
0 rows fetched
Pingback: toms sko norge
Pingback: Ludatou's life » 11g OCM复习项目列表（更新中）
沙发