DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, January 10, 2018

RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view


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;

Describe V$DIAG_ALERT_EXT


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 

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;

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

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;

No comments: