[20141027]通过视图查看alert.log的问题

[20141027]通过视图查看alert.log的问题.txt

--曾经写过一篇通过X$DBGALERTEXT查看alert*.log的文章,实际上在11G,定义了许多v$diag的视图,通过这些视图就可以获得许多信息.

--甚至不需要直接查看相关文件X$DBGALERTEXT.

<a href="http://blog.itpub.net/267265/viewspace-775126/">http://blog.itpub.net/267265/viewspace-775126/</a>

SCOTT@test&gt; @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&gt; 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&gt; 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&gt; 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&gt; @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&gt; 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

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/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更大些例外).