Alert log x$dbgalertext tips

Prior to Oracle 11g, the DBA had to define the Oracle alert log as an external table in order to query the alert log with native SQL.

• SQL against the alert log - You can define the alert log file as an external table and detect messages with SQL.
   
• Scripts - Write your own OS shell scripts to detect alert log messages.
   
• PL/SQL - You can write a SQL*Plus procedure using utl_file to read the alert log.
• In 11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

Now in 11g, Oracle gives us an x$ fixed table that maps to the alert log, allowing for native SQL queries against the alert log.

Most people make a "positive" list of serious errors (e.g. ORA-00600) and add these to a large "IN" list when querying x$dbgalertext.  Over time, the IN list becomes longer, eventually becoming a list of serious messages.

Note: Also see these important techniques for reading the Oracle alert log file.

The x$dbgalertext table is mapped to the alert log file located at  $ORACLE_BASE/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/alert/log_XX.xml.

We see that the x$dbgalertext appears as a table, a direct mapping that allows us to specify columns, just as-if the alert log flat file was an Oracle table.  Here are the columns in the x$dbgalertext fixed table:

SQL> desc X$DBGALERTEXT

           Name                            Null?    Type

           ------------------------------- -------- -------------------------

    1      ADDR                                     RAW(4)

    2      INDX                                     NUMBER

    3      INST_ID                                  NUMBER

    4      ORIGINATING_TIMESTAMP                    TIMESTAMP(3) WITH TIME ZONE

    5      NORMALIZED_TIMESTAMP                     TIMESTAMP(3) WITH TIME ZONE

    6      ORGANIZATION_ID                          VARCHAR2(64)

    7      COMPONENT_ID                             VARCHAR2(64)

    8      HOST_ID                                  VARCHAR2(64)

    9      HOST_ADDRESS                             VARCHAR2(16)

   10      MESSAGE_TYPE                             NUMBER

   11      MESSAGE_LEVEL                            NUMBER

   12      MESSAGE_ID                               VARCHAR2(64)

   13      MESSAGE_GROUP                            VARCHAR2(64)

   14      CLIENT_ID                                VARCHAR2(64)

   15      MODULE_ID                                VARCHAR2(64)

   16      PROCESS_ID                               VARCHAR2(32)

   17      THREAD_ID                                VARCHAR2(64)

   18      USER_ID                                  VARCHAR2(64)

   19      INSTANCE_ID                              VARCHAR2(64)

   20      DETAILED_LOCATION                        VARCHAR2(160)

   21      PROBLEM_KEY                              VARCHAR2(64)

   22      UPSTREAM_COMP_ID                         VARCHAR2(100)

   23      DOWNSTREAM_COMP_ID                       VARCHAR2(100)

   24      EXECUTION_CONTEXT_ID                     VARCHAR2(100)

   25      EXECUTION_CONTEXT_SEQUENCE               NUMBER

   26      ERROR_INSTANCE_ID                        NUMBER

   27      ERROR_INSTANCE_SEQUENCE                  NUMBER

   28      VERSION                                  NUMBER

   29      MESSAGE_TEXT                             VARCHAR2(2048)

   30      MESSAGE_ARGUMENTS                        VARCHAR2(128)

   31      SUPPLEMENTAL_ATTRIBUTES                  VARCHAR2(128)

   32      SUPPLEMENTAL_DETAILS                     VARCHAR2(128)

   33      PARTITION                                NUMBER

   34      RECORD_ID                                NUMBER

Here is a sample filtered query against the x$dbgalertext table for the past two days:

select distinct
   originating_timestamp,
   message_text
from
   x$dbgalertext
where
   originating_timestamp > sysdate-2
end
(
   message_text = 'ORA-00600'
   or
   message_text like '%Fatal%'
);

Here is another sample query against x$dbgalertext:

select
   rownum       "line",
   message_text "error"
from
   sys.v_x$dbgalertext
where
   originating_timestamp > (sysdate - 5/1440)
and
   message_text like '%ORA-%'
order by
   originating_timestamp;

Here are sample queries using XML markup with x$dbgalertext by Marco Gralike

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  
   x$dbgalertext alt
where 
   rownum < = 30;

---

select
   xmlserialize
     (content
      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)                        
        )
     indent size=0 hide defaults
     ) as "mylog.xml"
from  
   x$dbgalertext alt
where 
   rownum < = 30;

For more 11g new features, see the great book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain and Brian Carr.

---