[20141027]通过视图查看alert.log的问题.txt
--曾经写过一篇通过X$DBGALERTEXT查看alert*.log的文章,实际上在11G,定义了许多v$diag的视图,通过这些视图就可以获得许多信息.
--甚至不需要直接查看相关文件X$DBGALERTEXT.
http://blog.itpub.net/267265/viewspace-775126/
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> select view_name from dba_views where view_name like 'V%DIAG%' and owner='SYS';
VIEW_NAME
------------------------------
V_$DIAG_V_SWPERRCOUNT
V_$DIAG_V_NFCINC
V_$DIAG_V_IPSPRBCNT1
V_$DIAG_V_IPSPRBCNT
V_$DIAG_V_INC_METER_INFO_PROB
V_$DIAG_V_INCFCOUNT
V_$DIAG_V_INCCOUNT
V_$DIAG_V_ACTPROB
V_$DIAG_V_ACTINC
V_$DIAG_VTEST_EXISTS
V_$DIAG_VSHOWINCB_I
V_$DIAG_VSHOWINCB
V_$DIAG_VSHOWCATVIEW
V_$DIAG_VPROBLEM_LASTINC
V_$DIAG_VPROBLEM_INT
V_$DIAG_VPROBLEM_BUCKET_COUNT
V_$DIAG_VPROBLEM_BUCKET1
V_$DIAG_VPROBLEM_BUCKET
V_$DIAG_VPROBLEM2
V_$DIAG_VPROBLEM1
V_$DIAG_VPROBLEM
V_$DIAG_VNOT_EXIST_INCIDENT
V_$DIAG_VIPS_PKG_MAIN_PROBLEM
V_$DIAG_VIPS_PKG_INC_DTL1
V_$DIAG_VIPS_PKG_INC_DTL
V_$DIAG_VIPS_PKG_INC_CAND
V_$DIAG_VIPS_PKG_FILE
V_$DIAG_VIPS_PACKAGE_SIZE
V_$DIAG_VIPS_PACKAGE_MAIN_INT
V_$DIAG_VIPS_PACKAGE_HISTORY
V_$DIAG_VIPS_PACKAGE_FILE
V_$DIAG_VIPS_FILE_METADATA
V_$DIAG_VIPS_FILE_COPY_LOG
V_$DIAG_VINC_METER_INFO
V_$DIAG_VINCIDENT_FILE
V_$DIAG_VINCIDENT
V_$DIAG_VIEWCOL
V_$DIAG_VIEW
V_$DIAG_VHM_RUN
V_$DIAG_VEM_USER_ACTLOG1
V_$DIAG_VEM_USER_ACTLOG
V_$DIAG_SWEEPERR
V_$DIAG_RELMD_EXT
V_$DIAG_PROBLEM
V_$DIAG_PICKLEERR
V_$DIAG_IPS_REMOTE_PACKAGE
V_$DIAG_IPS_PROGRESS_LOG
V_$DIAG_IPS_PKG_UNPACK_HIST
V_$DIAG_IPS_PACKAGE_INCIDENT
V_$DIAG_IPS_PACKAGE_HISTORY
V_$DIAG_IPS_PACKAGE_FILE
V_$DIAG_IPS_PACKAGE
V_$DIAG_IPS_FILE_METADATA
V_$DIAG_IPS_FILE_COPY_LOG
V_$DIAG_IPS_CONFIGURATION
V_$DIAG_INFO
V_$DIAG_INC_METER_SUMMARY
V_$DIAG_INC_METER_PK_IMPTS
V_$DIAG_INC_METER_INFO
V_$DIAG_INC_METER_IMPT_DEF
V_$DIAG_INC_METER_CONFIG
V_$DIAG_INCIDENT_FILE
V_$DIAG_INCIDENT
V_$DIAG_INCCKEY
V_$DIAG_HM_RUN
V_$DIAG_HM_RECOMMENDATION
V_$DIAG_HM_MESSAGE
V_$DIAG_HM_INFO
V_$DIAG_HM_FINDING
V_$DIAG_HM_FDG_SET
V_$DIAG_EM_USER_ACTIVITY
V_$DIAG_EM_TARGET_INFO
V_$DIAG_EM_DIAG_JOB
V_$DIAG_DIR_EXT
V_$DIAG_DIAGV_INCIDENT
V_$DIAG_DFW_CONFIG_ITEM
V_$DIAG_DFW_CONFIG_CAPTURE
V_$DIAG_DDE_USR_INC_TYPE
V_$DIAG_DDE_USR_INC_ACT_MAP
V_$DIAG_DDE_USR_ACT_PARAM_DEF
V_$DIAG_DDE_USR_ACT_PARAM
V_$DIAG_DDE_USER_ACTION_DEF
V_$DIAG_DDE_USER_ACTION
V_$DIAG_CRITICAL_ERROR
V_$DIAG_AMS_XACTION
V_$DIAG_ALERT_EXT
V_$DIAG_ADR_INVALIDATION
V_$DIAG_ADR_CONTROL
88 rows selected.
--查询V$DIAG_ALERT_EXT可以获得许多信息.但是我遇到一个奇怪的问题.
SCOTT@test> select distinct dump(COMPONENT_ID,16) c40 ,COMPONENT_ID c30 from V$DIAG_ALERT_EXT ;
C40 C30
---------------------------------------- ------------------------------
Typ=1 Len=7: 74,6e,73,6c,73,6e,72 tnslsnr
Typ=1 Len=7: 63,6c,69,65,6e,74,73 clients
Typ=1 Len=5: 72,64,62,6d,73 rdbms
Typ=1 Len=8: 64,69,61,67,74,6f,6f,6c diagtool
--但是如果我执行:
SCOTT@test> select * from V$DIAG_ALERT_EXT where COMPONENT_ID='rdbms' ;
no rows selected
--必须要查询才有结果:
select * from V$DIAG_ALERT_EXT where trim(COMPONENT_ID)='rdbms' ;
--做一个跟踪:
SCOTT@test> @spid
SID SERIAL# C50
---------- ---------- --------------------------------------------------
591 13 alter system kill session '591,13' immediate;
SPID
------
4094
# strace -p 4094 -e open
Process 4094 attached - interrupt to quit
open("/u01/app/oracle11g/diag", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 8
open("/u01/app/oracle11g/diag/ofm", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 10
open("/u01/app/oracle11g/diag/asmtool", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 10
open("/u01/app/oracle11g/diag/asmtool/user_oracle11g", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 11
--查询的这些路径没有log*.xml文件.
SCOTT@test> select message_text from V$DIAG_ALERT_EXT where trim(COMPONENT_ID)='rdbms' and rownumMESSAGE_TEXT
--------------------------------------------------------------------------------------------------------
Starting ORACLE instance (restrict)
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
10 rows selected.
$ strace -p 4094 -e open
Process 4094 attached - interrupt to quit
open("/u01/app/oracle11g/diag", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 32
open("/u01/app/oracle11g/diag/ofm", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/asmtool", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/asmtool/user_oracle11g", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 34
open("/u01/app/oracle11g/diag/asmtool/user_oracle11g/host_1293607054_76/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 35
open("/u01/app/oracle11g/diag/asm", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/lsnrctl", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/crs", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/clients", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/clients/user_oracle11g", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 34
open("/u01/app/oracle11g/diag/clients/user_oracle11g/host_1293607054_80/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/clients/user_oracle11g/host_1293607054_80/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 36
open("/u01/app/oracle11g/diag/netcman", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/diagtool", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/diagtool/user_oracle11g", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 34
open("/u01/app/oracle11g/diag/diagtool/user_oracle11g/host_1293607054_80/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/diagtool/user_oracle11g/host_1293607054_80/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 36
open("/u01/app/oracle11g/diag/tnslsnr", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 34
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_1.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_2.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_3.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_4.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_5.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_6.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_7.xml", O_RDONLY) = 36
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_1.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_2.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_3.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_4.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_5.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_6.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log_7.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener1/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/tnslsnr/xxxxx/listener1/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 36
open("/u01/app/oracle11g/diag/rdbms", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 33
open("/u01/app/oracle11g/diag/rdbms/dummy", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 34
open("/u01/app/oracle11g/diag/rdbms/dummy/test/alert/log.xml", O_RDONLY) = 35
open("/u01/app/oracle11g/diag/rdbms/dummy/test/alert", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = 36
Process 4094 detached
--真不知道这种差异如何产生的.另外注意一个细节问题,查询打开的是/u01/app/oracle11g/diag/rdbms/dummy/test/alert/log.xml文件,而这个并不是使用数据库的实例(如果查询rownum更大些例外).