ORACLE 11G中 ADR 诊断信息
ADR(Automatic Diagnostic Repository)是一个基于文件的档案库，用于存放数据库的诊断信息，例如跟踪文件，意外dump文件，IPS包，警告日志文件，健康监控报告，核心dump文件以及其它诊断信息
一、ADR的结构和位置
ORACLE_BASE环境变量：ADR的根目录
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 /存储回话trace文件
Diag Alert /u01/app/oracle/diag/rdbms/ prod/prod1/alert //一个实例的告警日志文件（xml格式）
Diag Incident /u01/app/oracle/diag/rdbms/prod/prod1/incident //：每一个事件一个子目录，其中包含所有trace dump文件
Diag Cdump /u01/app/oracle/diag/rdbms/ prod/prod1/cdump //core文件
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
二、 ADRCI 接口使用
oracle为我们提供了一个挖掘ADR里的诊断数据的接口，叫adrci。这个命令行工具提供很多丰富的功能，比如将问题和事件打包为一个zip文件发给oracle技术支持。这些被压缩的诊断数据包括trace及dump文件，alert log条目以及oracle 11g的健康监视报告。
# adrci
# help
adrci> show home
adrci> set home diag\rdbms\ora11g\ora11g //设定一下adrci的主目录
adrci> show home
ADR Homes:
diag\rdbms\ora11g\ora11g
adrci> show alert -tail //检查一下我们的alertlog，默认是10条条目
adrci> show alert -tail -f //跟踪
adrci> show alert -p "MESSAGE_TEXT like '%ORA-%'"(显示alert中关于ORA-报错的内容。其他更多的show alert的选项，可见 help show alert)
adrci> show incident
ADR Home = e:\ora11g\app\administrator\diag\rdbms\ora11g\ora11g:
*************************************************************************
0 rows fetched
adrci> show incident
ADR Home = e:\ora11g\app\administrator\diag\rdbms\ora11g\ora11g:
*************************************************************************
0 rows fetched
人为的制造一个incident
sys@ORA11G(192.168.1.106)> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner='TEST' and object_name='TB_TEST';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
TB_TEST 74270 74270
Elapsed: 00:00:00.00
sys@ORA11G(192.168.1.106)> update sys.tab$ set OBJ# = 999999 where OBJ# = 74270;
1 row updated.
Elapsed: 00:00:00.00
sys@ORA11G(192.168.1.106)> commit;
Commit complete.
Elapsed: 00:00:00.03
sys@ORA11G(192.168.1.106)> alter system checkpoint;
System altered.
Elapsed: 00:00:00.26
sys@ORA11G(192.168.1.106)>
sys@ORA11G(192.168.1.106)>
sys@ORA11G(192.168.1.106)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA11G(192.168.1.106)> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 310379944 bytes
Database Buffers 461373440 bytes
Redo Buffers 5259264 bytes
Database mounted.
Database opened.
sys@ORA11G(192.168.1.106)>conn test/test
test@ORA11G(192.168.1.106)> desc tb_test
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 640
Session ID: 137 Serial number: 2
看看show incident和show problem
adrci> show incident
ADR Home = e:\ora11g\app\administrator\diag\rdbms\ora11g\ora11g:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
32620 ORA 7445 [kqldcdp()+78] 2012-06-24 22:13:56.109000 +08:00
32580 ORA 7445 [kqldcdp()+78] 2012-06-24 22:17:23.890000 +08:00 //再次desc tb_test，会看到这一行。我们可以理解成problem是重复的incident
1 rows fetched
adrci> show problem
ADR Home = e:\ora11g\app\administrator\diag\rdbms\ora11g\ora11g:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2 ORA 7445 [kqldcdp()+78] 32620 2012-06-24 22:13:56.109000 +08:00
1 rows fetched
