Thursday, March 21, 2013

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

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