Oracle 11g: Reading alert log via SQL

• Tweet

• Tweet

Oracle has done some major improvements in the diagnosability infrastructure in version 11g. Here’s one little detail.

Before Oracle 11g it is possible to access the alert log via SQL using an external table or a pipelined function which in turn uses utl_file.
After reading the text you need to parse it to extract the information you need from there.

Starting from 11g Oracle does all this work for you. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

SQL> select message_text from X$DBGALERTEXT where rownum <= 20;

MESSAGE_TEXT
-----------------------------------------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile /u01/app/oracle/admin/LIN11G/pfile/init.ora on machine linux03
System parameters with non-default values:
  processes                = 150
  memory_target            = 404M
  control_files            = "/u01/oradata/LIN11G/control01.ctl"
  control_files            = "/u01/oradata/LIN11G/control02.ctl"
  control_files            = "/u01/oradata/LIN11G/control03.ctl"
  db_block_size            = 8192

20 rows selected.

This is the text representation, but you can get individual details from other columns as listed below:

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

There’s also a fixed table X$DBGDIREXT, which returns all file and directory names under [diagnostic_dest]/diag directory:

SQL> select lpad(' ',lvl,' ')||logical_file file_name
  2  from X$DBGDIREXT
  3  where rownum <=20;

FILE_NAME
--------------------------------------------------------
asm
lsnrctl
diagtool
rdbms
 lin11g
  lin11g
   ir
    recovery_history.ir
   incident
    incdir_33815
     lin11g_ora_10330_i33815.trc
     lin11g_ora_10330_i33815.trm
    incdir_25347
     lin11g_ora_32614_i25347.trc
     lin11g_ora_32614_i25347.trm
    incdir_25417
     lin11g_ora_1225_i25417.trm
     lin11g_ora_1225_i25417.trc
    incdir_43459
     lin11g_ora_9467_i43459.trm

20 rows selected.

If you’re building some custom alert log monitoring, then starting from 11g these x$ tables can help you. On the other hand, I would prefer to monitor logfiles using plain and simple scripts as accessing this X$ table requires the instance to be up and operational. The better use case I see is that if you don’t have access to OS filesystem and nevertheless want to see alert log contents, that can be the easiest option in 11g…