Queries to view Alert Log content and Alert Location

Time:2019-9-11

Quering alert log using SQL query

Oracle 11g started with X $DBGALERTEXT, which can be used to access database alert logs using sql. When it is inconvenient to access the server directly, it provides another way to access database alert logs. The definition of X $DBGALERTEXT is as follows:

SYS> desc X$DBGALERTEXT
 Name                                            Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ADDR                                                 RAW(8)
 INDX                                                 NUMBER
 INST_ID                                              NUMBER
 ORIGINATING_TIMESTAMP                                        TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                                         TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                          VARCHAR2(64)
 COMPONENT_ID                                             VARCHAR2(64)
 HOST_ID                                              VARCHAR2(64)
 HOST_ADDRESS                                             VARCHAR2(46)
 MESSAGE_TYPE                                             NUMBER
 MESSAGE_LEVEL                                            NUMBER
 MESSAGE_ID                                           VARCHAR2(64)
 MESSAGE_GROUP                                            VARCHAR2(64)
 CLIENT_ID                                            VARCHAR2(64)
 MODULE_ID                                            VARCHAR2(64)
 PROCESS_ID                                           VARCHAR2(32)
 THREAD_ID                                            VARCHAR2(64)
 USER_ID                                              VARCHAR2(64)
 INSTANCE_ID                                              VARCHAR2(64)
 DETAILED_LOCATION                                        VARCHAR2(160)
 PROBLEM_KEY                                              VARCHAR2(64)
 UPSTREAM_COMP_ID                                         VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                       VARCHAR2(100)
 EXECUTION_CONTEXT_ID                                         VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                                   NUMBER
 ERROR_INSTANCE_ID                                        NUMBER
 ERROR_INSTANCE_SEQUENCE                                      NUMBER
 VERSION                                              NUMBER
 MESSAGE_TEXT                                             VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                        VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                                      VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                                         VARCHAR2(128)
 PARTITION                                            NUMBER
 RECORD_ID                                            NUMBER


The commonly used SQL is as follows (you can get results similar to the alert log style of direct access text format):

SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT;

Finding Trace File Path using SQL

Starting with 11g, Oracle provides an X $DBGDIREXT interface to view directories and files under diagnostic_dest. The definition of X $DBGDIREXT is as follows:

 SYS> desc X$DBGDIREXT;
 Name                                            Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ADDR                                                 RAW(8)
 INDX                                                 NUMBER
 INST_ID                                              NUMBER
 PHYSICAL_PATH                                            VARCHAR2(444)
 LOGICAL_PATH                                             VARCHAR2(444)
 PHYSICAL_FILE                                            VARCHAR2(68)
 LOGICAL_FILE                                             VARCHAR2(68)
 CREATION_TIME                                            TIMESTAMP(3) WITH TIME ZONE
 MODIFY_TIME                                              TIMESTAMP(3) WITH TIME ZONE
 LVL                                                  NUMBER
 TYPE                                                 NUMBER

A very handy usage: Sometimes people who are not familiar with Oracle need it to help extract the alert log. Because 11g alert is placed in diagnostic, it takes a lot of effort to describe the path of the log. Now we can use the following SQL to get the path of the alert log directly:

SELECT PHYSICAL_PATH || CHR(47) || PHYSICAL_FILE
  FROM X$DBGDIREXT
 WHERE 1 = 1
   AND PHYSICAL_FILE LIKE 'alert_%'
   AND PHYSICAL_PATH LIKE '%rdbms%'
/