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

转载于:https://www.cnblogs.com/reaperhero/articles/10274883.html