RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view [ADR - Automatic Diagnostic Re
浏览数：307 /
时间：2015年06月12日
With the V$DIAG_ALERT_EXT it is possible to read the logs of all the databases and listeners from the ADR location Great for monitoring. Now only one connection to a database is needed to see all the database
alert files and listener logs registered inside the ADR structure.Yes Multiple databases.
There is NO GV$DIAG_ALERT_EXT.
ADR is node, database depended and not global. Still we can use a shared ADR on a shared file system on Oracle CLusterware RAC and then is everthing from one place.
The parameter DIAGNOSTIC_DEST represents the root for the Automatic Diagnostic Repository (ADR), which includes the alertfile states. If this parameter is not set and the ORACLE_BASE is than it will use this information for the DIAGNOSTIC_DEST. If ORACLE_BASE is not set then the ADR root is in log Oracle release 11.2 has introduced new v$ diagnostic (diag) views.
select object_name from dba_objects where object_name like ‘V$DIAG%‘ order by 1;
OBJECT_NAME
--------------------------------------------------------------------------------
V$DIAG_ADR_CONTROL
V$DIAG_ADR_INVALIDATION
V$DIAG_ALERT_EXT
V$DIAG_AMS_XACTION
V$DIAG_CRITICAL_ERROR
V$DIAG_DDE_USER_ACTION
V$DIAG_DDE_USER_ACTION_DEF
V$DIAG_DDE_USR_ACT_PARAM
V$DIAG_DDE_USR_ACT_PARAM_DEF
V$DIAG_DDE_USR_INC_ACT_MAP
V$DIAG_DDE_USR_INC_TYPE
V$DIAG_DFW_CONFIG_CAPTURE
V$DIAG_DFW_CONFIG_ITEM
V$DIAG_DIAGV_INCIDENT
V$DIAG_DIR_EXT
V$DIAG_EM_DIAG_JOB
V$DIAG_EM_TARGET_INFO
V$DIAG_EM_USER_ACTIVITY
V$DIAG_HM_FDG_SET
V$DIAG_HM_FINDING
V$DIAG_HM_INFO
V$DIAG_HM_MESSAGE
V$DIAG_HM_RECOMMENDATION
V$DIAG_HM_RUN
V$DIAG_INCCKEY
V$DIAG_INCIDENT
V$DIAG_INCIDENT_FILE
V$DIAG_INC_METER_CONFIG
V$DIAG_INC_METER_IMPT_DEF
V$DIAG_INC_METER_INFO
V$DIAG_INC_METER_PK_IMPTS
V$DIAG_INC_METER_SUMMARY
V$DIAG_INFO
V$DIAG_IPS_CONFIGURATION
V$DIAG_IPS_FILE_COPY_LOG
V$DIAG_IPS_FILE_METADATA
V$DIAG_IPS_PACKAGE
V$DIAG_IPS_PACKAGE_FILE
V$DIAG_IPS_PACKAGE_HISTORY
V$DIAG_IPS_PACKAGE_INCIDENT
V$DIAG_IPS_PKG_UNPACK_HIST
V$DIAG_IPS_PROGRESS_LOG
V$DIAG_IPS_REMOTE_PACKAGE
V$DIAG_PICKLEERR
V$DIAG_PROBLEM
V$DIAG_RELMD_EXT
V$DIAG_SWEEPERR
V$DIAG_VEM_USER_ACTLOG
V$DIAG_VEM_USER_ACTLOG1
V$DIAG_VHM_RUN
V$DIAG_VIEW
V$DIAG_VIEWCOL
V$DIAG_VINCIDENT
V$DIAG_VINCIDENT_FILE
V$DIAG_VINC_METER_INFO
V$DIAG_VIPS_FILE_COPY_LOG
V$DIAG_VIPS_FILE_METADATA
V$DIAG_VIPS_PACKAGE_FILE
V$DIAG_VIPS_PACKAGE_HISTORY
V$DIAG_VIPS_PACKAGE_MAIN_INT
V$DIAG_VIPS_PACKAGE_SIZE
V$DIAG_VIPS_PKG_FILE
V$DIAG_VIPS_PKG_INC_CAND
V$DIAG_VIPS_PKG_INC_DTL
V$DIAG_VIPS_PKG_INC_DTL1
V$DIAG_VIPS_PKG_MAIN_PROBLEM
V$DIAG_VNOT_EXIST_INCIDENT
V$DIAG_VPROBLEM
V$DIAG_VPROBLEM1
V$DIAG_VPROBLEM2
V$DIAG_VPROBLEM_BUCKET
V$DIAG_VPROBLEM_BUCKET1
V$DIAG_VPROBLEM_BUCKET_COUNT
V$DIAG_VPROBLEM_INT
V$DIAG_VPROBLEM_LASTINC
V$DIAG_VSHOWCATVIEW
V$DIAG_VSHOWINCB
V$DIAG_VSHOWINCB_I
V$DIAG_VTEST_EXISTS
V$DIAG_V_ACTINC
V$DIAG_V_ACTPROB
V$DIAG_V_INCCOUNT
V$DIAG_V_INCFCOUNT
V$DIAG_V_INC_METER_INFO_PROB
V$DIAG_V_IPSPRBCNT
V$DIAG_V_IPSPRBCNT1
V$DIAG_V_NFCINC
V$DIAG_V_SWPERRCOUNT
88 rows selected.
Describe V$DIAG_ALERT_EXT
SQL> desc V$DIAG_ALERT_EXT
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
ADR_PATH_IDX VARCHAR2(445)
ADR_HOME VARCHAR2(445)
ORIGINATING_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(67)
COMPONENT_ID VARCHAR2(67)
HOST_ID VARCHAR2(67)
HOST_ADDRESS VARCHAR2(49)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(67)
MESSAGE_GROUP VARCHAR2(67)
CLIENT_ID VARCHAR2(67)
MODULE_ID VARCHAR2(67)
PROCESS_ID VARCHAR2(35)
THREAD_ID VARCHAR2(67)
USER_ID VARCHAR2(67)
INSTANCE_ID VARCHAR2(67)
DETAILED_LOCATION VARCHAR2(163)
UPSTREAM_COMP_ID VARCHAR2(103)
DOWNSTREAM_COMP_ID VARCHAR2(103)
EXECUTION_CONTEXT_ID VARCHAR2(103)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
MESSAGE_TEXT VARCHAR2(2051)
MESSAGE_ARGUMENTS VARCHAR2(131)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(131)
SUPPLEMENTAL_DETAILS VARCHAR2(131)
PARTITION NUMBER
RECORD_ID NUMBER
FILENAME VARCHAR2(515)
PROBLEM_KEY VARCHAR2(67)
VERSION NUMBER
Demo Output of a RAC instance
select distinct adr_home from v$diag_alert_ext;
ADR_HOME COMPONENT_ID
------------------------------------------------------- -------------------------
diag/rdbms/joord/JOORD1 rdbms
diag/rdbms/TEST/TEST1 rdbms
diag/tnslsnr/rdbms11gr2/listener_scan2 tnslsnr
diag/tnslsnr/rdbms11gr2/listener tnslsnr
INST_ID NAME VALUE
---------- ---------------------------------------- -----------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /home/oracle/app/oracle
1 ADR Home /home/oracle/app/oracle/diag/rdbms/prod1/PROD1
1 Diag Trace /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/trace
1 Diag Alert /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/alert
1 Diag Incident /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/incident
1 Diag Cdump /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/cdump
1 Health Monitor /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/hm
1 Default Trace File /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_3486.trc
1 Active Problem Count 0
1 Active Incident Count 0
SELECT * FROM V$DIAG_CRITICAL_ERROR;
FACILITY ERROR
---------- -----------------------------------------------------------------
ORA 7445
ORA 4030
ORA 4031
ORA 29740
ORA 255
ORA 355
ORA 356
ORA 239
ORA 240
ORA 494
ORA 3137
ORA 227
ORA 353
ORA 1578
ORA 32701
ORA 32703
ORA 29770
ORA 29771
ORA 445
ORA 25319
ORA 56729
OCI 3106
OCI 3113
OCI 3135
XML markup V$DIAG_ALERT_EXT
The V$DIAG_ALERT_EXT read the log.xml file from de ADR location. Here are sample queries using XML markup with V$DIAG_ALERT_EXT info from ( Marco Gralike ) view sourceprint?01 set pagesize 120
set linesize 200
set long 99999
column ADR_HOME format a40
column "mylog.xml" format a180
select
xmlelement(noentityescaping "msg",
xmlattributes( alt.originating_timestamp as "time",
alt.organization_id as "org_id",
alt.component_id as "comp_id",
alt.message_id as "msg_id",
alt.message_type as "type",
alt.message_group as "group",
alt.message_level as "level",
alt.host_id as "host_id",
alt.host_address as "host_addr",
alt.process_id as "pid_id",
alt.version as "version"
),
xmlelement("txt", message_text)
) as "mylog.xml"
from
v$diag_alert_ext alt order by alt.component_id, alt.inst_id, alt.originating_timestamp
Most of the "normal errors", if such thing exists, are level 16. Data mining through the V$DIAG_ALERT_EXT table is now possible. Good thing is that there are errors for the component "tnslsnr", which means that it is now possible to catch Oracle*Net errors, too. Maybe in release 12C Oracle has made this complet functional, now it is a hidden not described structure.
Summary:
V$DIAG_ALERT_EXT (X$DBGALERTEXT) is done by External table referencing log*.xml files. Effectively indexed by alert date, so date-based queries are “efficient and performant.” (MOS Doc ID 961682.1) Has inst_id column, but not RAC-aware. As of 11.2, V$DIAG_ALERT_EXT Includes alerts from all ADR homes (ASM, listeners, clients, etc.) SQLPLUS statements view sourceprint?1 select ORIGINATING_TIMESTAMP, MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > systimestamp - INTERVAL ‘15‘ minute
and trim(COMPONENT_ID)=‘rdbms‘
and inst_id=1
order by originating_timestamp;
set pagesize 120
set linesize 300
column adr_home format a40
column message_text format a80
select call_monitor , adr_home, inst_id, ORIGINATING_TIMESTAMP, message_text
from ( select adr_home, inst_id, ORIGINATING_TIMESTAMP, message_text
,dense_rank() over (PARTITION BY adr_home order by ORIGINATING_TIMESTAMP DESC NULLS LAST) as call_monitor
from v$diag_alert_ext )
where
-- call_monitor < 31
--and
ORIGINATING_TIMESTAMP > systimestamp - INTERVAL ‘24‘ hour
order by ORIGINATING_TIMESTAMP
There is NO GV$DIAG_ALERT_EXT.
ADR is node, database depended and not global. Still we can use a shared ADR on a shared file system on Oracle CLusterware RAC and then is everthing from one place.
The parameter DIAGNOSTIC_DEST represents the root for the Automatic Diagnostic Repository (ADR), which includes the alertfile states. If this parameter is not set and the ORACLE_BASE is than it will use this information for the DIAGNOSTIC_DEST. If ORACLE_BASE is not set then the ADR root is in log Oracle release 11.2 has introduced new v$ diagnostic (diag) views.
select object_name from dba_objects where object_name like ‘V$DIAG%‘ order by 1;
OBJECT_NAME
--------------------------------------------------------------------------------
V$DIAG_ADR_CONTROL
V$DIAG_ADR_INVALIDATION
V$DIAG_ALERT_EXT
V$DIAG_AMS_XACTION
V$DIAG_CRITICAL_ERROR
V$DIAG_DDE_USER_ACTION
V$DIAG_DDE_USER_ACTION_DEF
V$DIAG_DDE_USR_ACT_PARAM
V$DIAG_DDE_USR_ACT_PARAM_DEF
V$DIAG_DDE_USR_INC_ACT_MAP
V$DIAG_DDE_USR_INC_TYPE
V$DIAG_DFW_CONFIG_CAPTURE
V$DIAG_DFW_CONFIG_ITEM
V$DIAG_DIAGV_INCIDENT
V$DIAG_DIR_EXT
V$DIAG_EM_DIAG_JOB
V$DIAG_EM_TARGET_INFO
V$DIAG_EM_USER_ACTIVITY
V$DIAG_HM_FDG_SET
V$DIAG_HM_FINDING
V$DIAG_HM_INFO
V$DIAG_HM_MESSAGE
V$DIAG_HM_RECOMMENDATION
V$DIAG_HM_RUN
V$DIAG_INCCKEY
V$DIAG_INCIDENT
V$DIAG_INCIDENT_FILE
V$DIAG_INC_METER_CONFIG
V$DIAG_INC_METER_IMPT_DEF
V$DIAG_INC_METER_INFO
V$DIAG_INC_METER_PK_IMPTS
V$DIAG_INC_METER_SUMMARY
V$DIAG_INFO
V$DIAG_IPS_CONFIGURATION
V$DIAG_IPS_FILE_COPY_LOG
V$DIAG_IPS_FILE_METADATA
V$DIAG_IPS_PACKAGE
V$DIAG_IPS_PACKAGE_FILE
V$DIAG_IPS_PACKAGE_HISTORY
V$DIAG_IPS_PACKAGE_INCIDENT
V$DIAG_IPS_PKG_UNPACK_HIST
V$DIAG_IPS_PROGRESS_LOG
V$DIAG_IPS_REMOTE_PACKAGE
V$DIAG_PICKLEERR
V$DIAG_PROBLEM
V$DIAG_RELMD_EXT
V$DIAG_SWEEPERR
V$DIAG_VEM_USER_ACTLOG
V$DIAG_VEM_USER_ACTLOG1
V$DIAG_VHM_RUN
V$DIAG_VIEW
V$DIAG_VIEWCOL
V$DIAG_VINCIDENT
V$DIAG_VINCIDENT_FILE
V$DIAG_VINC_METER_INFO
V$DIAG_VIPS_FILE_COPY_LOG
V$DIAG_VIPS_FILE_METADATA
V$DIAG_VIPS_PACKAGE_FILE
V$DIAG_VIPS_PACKAGE_HISTORY
V$DIAG_VIPS_PACKAGE_MAIN_INT
V$DIAG_VIPS_PACKAGE_SIZE
V$DIAG_VIPS_PKG_FILE
V$DIAG_VIPS_PKG_INC_CAND
V$DIAG_VIPS_PKG_INC_DTL
V$DIAG_VIPS_PKG_INC_DTL1
V$DIAG_VIPS_PKG_MAIN_PROBLEM
V$DIAG_VNOT_EXIST_INCIDENT
V$DIAG_VPROBLEM
V$DIAG_VPROBLEM1
V$DIAG_VPROBLEM2
V$DIAG_VPROBLEM_BUCKET
V$DIAG_VPROBLEM_BUCKET1
V$DIAG_VPROBLEM_BUCKET_COUNT
V$DIAG_VPROBLEM_INT
V$DIAG_VPROBLEM_LASTINC
V$DIAG_VSHOWCATVIEW
V$DIAG_VSHOWINCB
V$DIAG_VSHOWINCB_I
V$DIAG_VTEST_EXISTS
V$DIAG_V_ACTINC
V$DIAG_V_ACTPROB
V$DIAG_V_INCCOUNT
V$DIAG_V_INCFCOUNT
V$DIAG_V_INC_METER_INFO_PROB
V$DIAG_V_IPSPRBCNT
V$DIAG_V_IPSPRBCNT1
V$DIAG_V_NFCINC
V$DIAG_V_SWPERRCOUNT
88 rows selected.
Describe V$DIAG_ALERT_EXT
SQL> desc V$DIAG_ALERT_EXT
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
ADR_PATH_IDX VARCHAR2(445)
ADR_HOME VARCHAR2(445)
ORIGINATING_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(67)
COMPONENT_ID VARCHAR2(67)
HOST_ID VARCHAR2(67)
HOST_ADDRESS VARCHAR2(49)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(67)
MESSAGE_GROUP VARCHAR2(67)
CLIENT_ID VARCHAR2(67)
MODULE_ID VARCHAR2(67)
PROCESS_ID VARCHAR2(35)
THREAD_ID VARCHAR2(67)
USER_ID VARCHAR2(67)
INSTANCE_ID VARCHAR2(67)
DETAILED_LOCATION VARCHAR2(163)
UPSTREAM_COMP_ID VARCHAR2(103)
DOWNSTREAM_COMP_ID VARCHAR2(103)
EXECUTION_CONTEXT_ID VARCHAR2(103)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
MESSAGE_TEXT VARCHAR2(2051)
MESSAGE_ARGUMENTS VARCHAR2(131)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(131)
SUPPLEMENTAL_DETAILS VARCHAR2(131)
PARTITION NUMBER
RECORD_ID NUMBER
FILENAME VARCHAR2(515)
PROBLEM_KEY VARCHAR2(67)
VERSION NUMBER
Demo Output of a RAC instance
select distinct adr_home from v$diag_alert_ext;
ADR_HOME COMPONENT_ID
------------------------------------------------------- -------------------------
diag/rdbms/joord/JOORD1 rdbms
diag/rdbms/TEST/TEST1 rdbms
diag/tnslsnr/rdbms11gr2/listener_scan2 tnslsnr
diag/tnslsnr/rdbms11gr2/listener tnslsnr
diag/asm/+asm/+ASM1 rdbms
INST_ID NAME VALUE
---------- ---------------------------------------- -----------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /home/oracle/app/oracle
1 ADR Home /home/oracle/app/oracle/diag/rdbms/prod1/PROD1
1 Diag Trace /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/trace
1 Diag Alert /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/alert
1 Diag Incident /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/incident
1 Diag Cdump /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/cdump
1 Health Monitor /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/hm
1 Default Trace File /home/oracle/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_3486.trc
1 Active Problem Count 0
1 Active Incident Count 0
SELECT * FROM V$DIAG_CRITICAL_ERROR;
FACILITY ERROR
---------- -----------------------------------------------------------------
ORA 7445
ORA 4030
ORA 4031
ORA 29740
ORA 255
ORA 355
ORA 356
ORA 239
ORA 240
ORA 494
ORA 3137
ORA 227
ORA 353
ORA 1578
ORA 32701
ORA 32703
ORA 29770
ORA 29771
ORA 445
ORA 25319
ORA 56729
OCI 3106
OCI 3113
OCI 3135
XML markup V$DIAG_ALERT_EXT
The V$DIAG_ALERT_EXT read the log.xml file from de ADR location. Here are sample queries using XML markup with V$DIAG_ALERT_EXT info from ( Marco Gralike ) view sourceprint?01 set pagesize 120
set linesize 200
set long 99999
column ADR_HOME format a40
column "mylog.xml" format a180
select
xmlelement(noentityescaping "msg",
xmlattributes( alt.originating_timestamp as "time",
alt.organization_id as "org_id",
alt.component_id as "comp_id",
alt.message_id as "msg_id",
alt.message_type as "type",
alt.message_group as "group",
alt.message_level as "level",
alt.host_id as "host_id",
alt.host_address as "host_addr",
alt.process_id as "pid_id",
alt.version as "version"
),
xmlelement("txt", message_text)
) as "mylog.xml"
from
v$diag_alert_ext alt order by alt.component_id, alt.inst_id, alt.originating_timestamp
Most of the "normal errors", if such thing exists, are level 16. Data mining through the V$DIAG_ALERT_EXT table is now possible. Good thing is that there are errors for the component "tnslsnr", which means that it is now possible to catch Oracle*Net errors, too. Maybe in release 12C Oracle has made this complet functional, now it is a hidden not described structure.
Summary:
V$DIAG_ALERT_EXT (X$DBGALERTEXT) is done by External table referencing log*.xml files. Effectively indexed by alert date, so date-based queries are “efficient and performant.” (MOS Doc ID 961682.1) Has inst_id column, but not RAC-aware. As of 11.2, V$DIAG_ALERT_EXT Includes alerts from all ADR homes (ASM, listeners, clients, etc.) SQLPLUS statements view sourceprint?1 select ORIGINATING_TIMESTAMP, MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > systimestamp - INTERVAL ‘15‘ minute
and trim(COMPONENT_ID)=‘rdbms‘
and inst_id=1
order by originating_timestamp;
set pagesize 120
set linesize 300
column adr_home format a40
column message_text format a80
select call_monitor , adr_home, inst_id, ORIGINATING_TIMESTAMP, message_text
from ( select adr_home, inst_id, ORIGINATING_TIMESTAMP, message_text
,dense_rank() over (PARTITION BY adr_home order by ORIGINATING_TIMESTAMP DESC NULLS LAST) as call_monitor
from v$diag_alert_ext )
where
-- call_monitor < 31
--and
ORIGINATING_TIMESTAMP > systimestamp - INTERVAL ‘24‘ hour
order by ORIGINATING_TIMESTAMP
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view [ADR - Automatic Diagnostic Re
