11g新特性:A useful View V$DIAG_INFO
在11g中引入了自动诊断资料档案库(ADR)特性,默认情况下各种trace,dump存放的目录位置区别于9i/10g显得更加难以查找了。
2.Client Directory Structure
一个需要注意的细节是启用了ADR自动诊断资料档案库后,LISTENER监听器日志的默认位置也被移动到diagnostic_dest下了,而不在如9i/10g那样存放在$ORACLE_HOME/network/log目录下,有不少人因为忘记了这个细节而花费了大量时间去寻找listener.log,这个人也包括我。实际上我们还是可以将listener.log日志的位置还原回10g的形式,这一点可以通过修改$ORACLE_HOME/network/admin/listener.ora来达成:
ADR 基目录中可以包含多个 ADR 主目录,其中每个 ADR 主目录都是一个根目录,用于存放特定 Oracle 产品或组件的特定实例的全部诊断数据。前一张幻灯片的图形中显示了数据库的 ADR 主目录位置。 另外,还生成了两个预警文件。一个是文本形式的预警文件(与早期版本 Oracle DB 使用的预警文件非常相似),位于各个 ADR 主目录的 TRACE 目录下。还有一个符合 XML 标准的预警消息文件,存储在 ADR 主目录内的 ALERT 子目录下。可使用 Enterprise Manager 和 ADRCI 实用程序查看文本格式的预警日志(已删除了 XML 标记)。 此幻灯片中的图形显示了 ADR 主目录的目录结构。INCIDENT 目录包含多个子目录, 每个子目录均以特定意外事件命名,并且仅包含与该意外事件相关的转储。 HM 目录包含由健康状况监视器生成的检查器运行报告。 还有一个 METADATA 目录,其中包含资料档案库自身的重要文件。可以将此目录比作数据库字典。可使用 ADRCI 查询此字典。 ADR 命令解释器 (ADRCI) 是一个实用程序,可用于执行支持工作台允许的所有任务(但是仅限于在命令行环境中)。使用 ADRCI 实用程序,您还可以查看 ADR 中跟踪文件的名称以及删除了 XML 标记、具有和不具有内容筛选功能的预警日志。 此外,还可以使用 V$DIAG_INFO 列出一些重要的 ADR 位置。自动诊断资料档案库 (ADR)的主要目录结构: 1.Server Directory Structure
![adr1](http://farm6.static.flickr.com/5221/5775569419_9dfb104a19_z.jpg)
![adr2](http://farm4.static.flickr.com/3048/5775569493_ff5e75985c_z.jpg)
[oracle@rh2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rh2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /s01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh2.oracle.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /s01/orabase
将以上listener.ora文件的ADR_BASE_LISTENER条目删除,并加上
DIAG_ADR_ENABLED_LISTENER = OFF
[oracle@rh2 log]$ lsnrctl reload
[oracle@rh2 admin]$ tnsping PROD
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 30-MAY-2011 21:39:21
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/s01/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rh-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
OK (0 msec)
[oracle@rh2 log]$ cat $ORACLE_HOME/network/log/listener.log
TNSLSNR for Linux: Version 11.2.0.2.0 - Production on 30-MAY-2011 21:42:52
Copyright (c) 1991, 2010, Oracle. All rights reserved.
System parameter file is /s01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /s01/oracle/product/11.2.0/dbhome_1/network/log/listener.log
Trace information written to /s01/oracle/product/11.2.0/dbhome_1/network/trace/listener.trc
Trace level is currently 6
Started with pid=24331
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh2)(PORT=1521)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
30-MAY-2011 21:42:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=rh2.oracle.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.122)(PORT=1521)))
30-MAY-2011 21:42:59 * service_register * PROD1 * 12542
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
Error listening on: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.133)(PORT=1521)))
30-MAY-2011 21:42:59 * service_register * PROD1 * 12542
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
30-MAY-2011 21:43:02 * service_update * PROD1 * 0
30-MAY-2011 21:43:02 * service_update * PROD1 * 0
30-MAY-2011 21:43:37 * service_register * G10R2 * 0
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.122)(PORT=1521)))
30-MAY-2011 21:43:39 * service_register * +ASM1 * 12542
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
从以上ADR目录结构图中可以看到整个架构变得更复杂了,实际使用中的ADR档案资料库的复杂度要更高:
[grid@rh2 raw]$ tree -d /s01/orabase/diag /s01/orabase/diag |-- asm | `-- +asm | `-- +ASM1 | |-- alert | |-- cdump | |-- hm | |-- incident | | |-- incdir_4897 | | |-- incdir_4898 | | |-- incdir_4899 | | |-- incdir_4900 | | |-- incdir_4901 | | |-- incdir_4902 | | `-- incdir_4903 | |-- incpkg | |-- ir | |-- lck | |-- metadata | |-- metadata_dgif | |-- metadata_pv | |-- stage | |-- sweep | `-- trace | |-- cdmp_20110424212230 | |-- cdmp_20110424214914 | |-- cdmp_20110424220022 | |-- cdmp_20110424221131 | |-- cdmp_20110424222254 | |-- cdmp_20110424223546 | `-- cdmp_20110427154653 |-- clients | `-- user_oracle | `-- host_3070836769_80 | |-- alert | |-- cdump | |-- incident | |-- incpkg | |-- lck | |-- metadata | |-- metadata_dgif | |-- metadata_pv | |-- stage | |-- sweep | `-- trace |-- crs |-- diagtool |-- lsnrctl |-- netcman |-- ofm |-- rdbms | |-- maclean | | `-- MACLEAN | | |-- alert | | |-- cdump | | |-- hm | | |-- incident | | |-- incpkg | | |-- ir | | |-- lck | | |-- metadata | | |-- metadata_dgif | | |-- metadata_pv | | |-- stage | | |-- sweep | | `-- trace | `-- prod | `-- PROD1 | |-- alert | |-- cdump | | |-- core_27975 | | |-- core_28006 | | |-- core_28013 | | `-- core_28034 | |-- hm | |-- incident | | |-- incdir_100831 | | |-- incdir_104831 | | |-- incdir_108831 | | |-- incdir_18201 | | |-- incdir_18202 | | |-- incdir_18266 | | |-- incdir_18361 | | |-- incdir_18362 | | |-- incdir_18369 | | |-- incdir_18385 | | |-- incdir_18387 | | |-- incdir_18393 | | |-- incdir_18402 | | |-- incdir_18410 | | |-- incdir_18411 | | |-- incdir_18441 | | |-- incdir_18457 | | |-- incdir_18458 | | |-- incdir_18465 | | |-- incdir_19737 | | |-- incdir_19738 | | |-- incdir_19739 | | |-- incdir_19745 | | |-- incdir_19746 | | |-- incdir_20589 | | |-- incdir_20701 | | |-- incdir_20736 | | |-- incdir_20737 | | |-- incdir_20738 | | |-- incdir_20739 | | |-- incdir_20740 | | |-- incdir_20957 | | |-- incdir_20973 | | |-- incdir_20989 | | |-- incdir_21005 | | |-- incdir_21778 | | |-- incdir_21936 | | |-- incdir_21937 | | |-- incdir_21938 | | |-- incdir_22200 | | |-- incdir_22201 | | |-- incdir_22216 | | |-- incdir_22232 | | |-- incdir_22233 | | |-- incdir_23306 | | |-- incdir_23506 | | |-- incdir_28123 | | |-- incdir_28147 | | |-- incdir_28148 | | |-- incdir_28227 | | |-- incdir_28228 | | |-- incdir_28229 | | |-- incdir_28230 | | |-- incdir_28235 | | |-- incdir_29323 | | |-- incdir_29324 | | |-- incdir_29325 | | |-- incdir_29326 | | |-- incdir_29327 | | |-- incdir_30681 | | |-- incdir_30682 | | |-- incdir_30683 | | |-- incdir_31724 | | |-- incdir_35253 | | |-- incdir_36453 | | |-- incdir_37653 | | |-- incdir_38853 | | |-- incdir_40053 | | |-- incdir_41253 | | |-- incdir_42453 | | |-- incdir_43653 | | |-- incdir_44853 | | |-- incdir_46053 | | |-- incdir_47253 | | |-- incdir_48453 | | |-- incdir_49653 | | |-- incdir_54525 | | |-- incdir_56918 | | |-- incdir_89183 | | |-- incdir_89184 | | |-- incdir_89185 | | |-- incdir_89186 | | |-- incdir_89187 | | |-- incdir_89327 | | |-- incdir_89343 | | |-- incdir_89351 | | |-- incdir_89359 | | |-- incdir_90270 | | |-- incdir_90271 | | |-- incdir_90272 | | |-- incdir_90273 | | |-- incdir_90274 | | |-- incdir_90275 | | `-- incdir_96831 | |-- incpkg | | `-- pkg_1 | | `-- seq_1 | | |-- crs | | `-- export | |-- ir | |-- lck | |-- metadata | |-- metadata_dgif | |-- metadata_pv | |-- stage | |-- sweep | `-- trace | |-- cdmp_20110502214850 | |-- cdmp_20110502214947 | |-- cdmp_20110502221010 | |-- cdmp_20110502221029 | |-- cdmp_20110502221204 | |-- cdmp_20110502221221 | |-- cdmp_20110502221257 | |-- cdmp_20110502221318 | |-- cdmp_20110502221450 | |-- cdmp_20110502221505 | |-- cdmp_20110502222225 | |-- cdmp_20110502222315 | |-- cdmp_20110502222402 | |-- cdmp_20110502224708 | |-- cdmp_20110502230815 | |-- cdmp_20110503202436 | |-- cdmp_20110503202521 | |-- cdmp_20110509231250 | |-- cdmp_20110512171047 | |-- cdmp_20110512171231 | |-- cdmp_20110512171437 | |-- cdmp_20110512171635 | |-- cdmp_20110520165537 | |-- cdmp_20110520232601 | |-- cdmp_20110520234343 | |-- cdmp_20110521000119 | |-- cdmp_20110521000218 | |-- cdmp_20110521065437 | |-- cdmp_20110521065455 | |-- cdmp_20110521065512 | |-- cdmp_20110521074224 | |-- cdmp_20110521074737 | |-- cdmp_20110521075206 | |-- cdmp_20110521080019 | |-- cdmp_20110521080642 | |-- cdmp_20110521140052 | |-- cdmp_20110530202441 | |-- cdmp_20110530202442 | |-- cdmp_20110530202846 | |-- cdmp_20110530202848 | |-- cdmp_20110530202916 | |-- cdmp_20110530202918 | |-- cdmp_20110530203156 | `-- cdmp_20110530203158 `-- tnslsnr `-- rh2 `-- listener |-- alert |-- cdump |-- incident |-- incpkg |-- lck |-- metadata |-- metadata_dgif |-- metadata_pv |-- stage |-- sweep `-- trace 240 directories所幸的是在11g中提供了比传统的gettrcname.sql脚本更为给力的诊断文件位置信息汇总的视图V$DIAG_INFO:
V$DIAG_INFO 视图列出了所有重要的 ADR 位置: ADR Base:ADR 基目录的路径 ADR Home:当前数据库实例的 ADR 主目录的路径 Diag Trace:文本预警日志和后台/前台进程跟踪文件的位置 Diag Alert:XML 版本的预警日志的位置 Default Trace File:会话的跟踪文件的路径。SQL 跟踪文件将写入到这里。 Health Monitor: 健康检查报告所在目录 Active Problem Count:当前激活的问题总数 Active Incident Count 当前激活的事故总数 SQL> select name,value from v$diag_info; NAME VALUE --------------------------------------- ------------------------------------------------------------ Diag Enabled TRUE ADR Base /s01/orabase ADR Home /s01/orabase/diag/rdbms/prod/PROD1 Diag Trace /s01/orabase/diag/rdbms/prod/PROD1/trace Diag Alert /s01/orabase/diag/rdbms/prod/PROD1/alert Diag Incident /s01/orabase/diag/rdbms/prod/PROD1/incident Diag Cdump /s01/orabase/diag/rdbms/prod/PROD1/cdump Health Monitor /s01/orabase/diag/rdbms/prod/PROD1/hm Default Trace File /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_22893.trc Active Problem Count 7 Active Incident Count 373 11 rows selected. adrci> show hm_run ********************************************************** HM RUN RECORD 119 ********************************************************** RUN_ID 2481 RUN_NAME HM_RUN_2481 CHECK_NAME DB Structure Integrity Check NAME_ID 2 MODE 2 START_TIME 2011-05-21 20:11:38.612669 +08:00 RESUME_TIME END_TIME 2011-05-21 20:11:38.619530 +08:00 MODIFIED_TIME 2011-05-21 20:11:38.619530 +08:00 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE adrci> create report hm_run HM_RUN_2481 adrci> show hm_run -p "RUN_ID=2481" ADR Home = /s01/orabase/diag/rdbms/prod/PROD1: ************************************************************************* ********************************************************** HM RUN RECORD 1 ********************************************************** RUN_ID 2481 RUN_NAME HM_RUN_2481 CHECK_NAME DB Structure Integrity Check NAME_ID 2 MODE 2 START_TIME 2011-05-21 20:11:38.612669 +08:00 RESUME_TIME END_TIME 2011-05-21 20:11:38.619530 +08:00 MODIFIED_TIME 2011-05-30 21:09:43.071150 +08:00 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE /s01/orabase/diag/rdbms/prod/PROD1/hm/HMREPORT_HM_RUN_2481.hm [oracle@rh2 ~]$ cd /s01/orabase/diag/rdbms/prod/PROD1/hm [oracle@rh2 hm]$ cat HMREPORT_HM_RUN_2481.hm <?xml version="1.0" encoding="US-ASCII"?> <HM-REPORT REPORT_ID="HM_RUN_2481"> <TITLE>HM Report: HM_RUN_2481</TITLE> <RUN_INFO> <CHECK_NAME>DB Structure Integrity Check</CHECK_NAME> <RUN_ID>2481</RUN_ID> <RUN_NAME>HM_RUN_2481</RUN_NAME> <RUN_MODE>REACTIVE</RUN_MODE> <RUN_STATUS>COMPLETED</RUN_STATUS> <RUN_ERROR_NUM>0</RUN_ERROR_NUM> <SOURCE_INCIDENT_ID>0</SOURCE_INCIDENT_ID> <NUM_INCIDENTS_CREATED>0</NUM_INCIDENTS_CREATED> <RUN_START_TIME>2011-05-21 20:11:38.612669 +08:00</RUN_START_TIME> <RUN_END_TIME>2011-05-21 20:11:38.619530 +08:00</RUN_END_TIME> </RUN_INFO> <RUN_PARAMETERS/> <RUN-FINDINGS/> </HM-REPORT>通过查询V$diag_info可以很容易找到自身服务进程的trace文件位置,对于其他进程的trace文件则可以查询v$process新加入的tracefile列:
SQL> select spid,tracefile from v$process; SPID TRACEFILE ------------------------ -------------------------------------------------------------------------------- /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_0.trc 22789 /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_pmon_22789.trc 22791 /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_psp0_22791.trc
posted on 2013-03-19 00:47 Oracle和MySQL 阅读(339) 评论(0) 编辑 收藏 举报