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
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
select * from v$diag_info;
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 )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 statementsselect 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
Hi Jos,
ReplyDeleteYou say:
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.”
External tables can't be indexed? What do you mean?
Cheers
Joaquin Gonzalez
Hi Joaquin,
ReplyDeleteThis is correct. The relation of the xml format of the logfile and the external table. The logical structure information.