首页 > 代码库 > RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view [ADR - Automatic Diagnostic Re

RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view [ADR - Automatic Diagnostic Re

2024-07-30 16:05:20   218人阅读

  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  

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 ) 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


联系
我们