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…
