8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Read the Alert Log From SQL (X$DBGALERTEXT and V$DIAG_ALERT_EXT)
This article describes how to read the database alert log from SQL using the X$DBGALERTEXT table, introduced in Oracle 11g.
Related articles.
The X$DBGALERTEXT Table
Prior to 11g, if we needed access to the alert log from SQL, we would create an external table to read it. From 11g onward we have the X$DBGALERTEXT
table.
The X$DBGALERTEXT
table presents the XML version of the alert log from the Automatic Diagnostic Repository (ADR) as a table, which we can query from SQL. The table structure looks like this.
SQL> desc x$dbgalertext Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER CON_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(128) INSTANCE_ID VARCHAR2(64) DETAILED_LOCATION VARCHAR2(160) PROBLEM_KEY VARCHAR2(550) 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(512) SUPPLEMENTAL_ATTRIBUTES VARCHAR2(512) SUPPLEMENTAL_DETAILS VARCHAR2(4000) PARTITION NUMBER RECORD_ID NUMBER CON_UID NUMBER CONTAINER_NAME VARCHAR2(64) ATTENTION_ID NUMBER ID_SUFFIX VARCHAR2(64) OPERATION_ID VARCHAR2(64) CAUSE_TEXT VARCHAR2(2048) ACTION_TEXT VARCHAR2(2048) ORACLE_PROCESS_ID NUMBER DATABASE_ID VARCHAR2(30) SQL_ID VARCHAR2(13) SESSION_ID VARCHAR2(16) IMPACT_ID VARCHAR2(32) IMPACT_SCOPE VARCHAR2(32) TARGET_USER VARCHAR2(32) CALL_STACK VARCHAR2(1024) FLAGS NUMBER URGENCY VARCHAR2(16) START_VERSION VARCHAR2(16) SQL>
We can query it like any other table. The following query displays critical events.
select message_text from x$dbgalertext where message_level = 1;
Of course, you can filter based on any of the column to looks for specific messages that you are interested it.
The V$DIAG_ALERT_EXT View (12.2 Update)
From Oracle 12.2 onward we can use the V$DIAG_ALERT_EXT
view to access the XML formatted alert log from the Automatic Diagnostic Repository (ADR). It presents most of the columns from the X$DBGALERTEXT
table.
SQL> desc v$diag_alert_ext Name Null? Type ----------------------------------------- -------- ---------------------------- 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(131) 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(515) SUPPLEMENTAL_ATTRIBUTES VARCHAR2(515) SUPPLEMENTAL_DETAILS VARCHAR2(515) PARTITION NUMBER RECORD_ID NUMBER FILENAME VARCHAR2(515) LOG_NAME VARCHAR2(67) PROBLEM_KEY VARCHAR2(553) VERSION NUMBER CON_UID NUMBER CONTAINER_ID NUMBER CONTAINER_NAME VARCHAR2(67) CON_ID NUMBER SQL>
We can query the information like any other view. The following example returns the messages of a critical nature.
-- MESSAGE_LEVEL - 1: CRITICAL select message_text from v$diag_alert_ext where message_level = 1; -- MESSAGE_TYPE - 2: INCIDENT_ERROR, 3: ERROR select message_type, message_level, message_text from v$diag_alert_ext where message_type in (2, 3);
The V$ALERT_LOG View
Prior to the introduction of the V$DIAG_ALERT_EXT
view in Oracle 12.2, people would often use create their own version of a similar view like this.
create or replace view sys.v_$alert_log as select * from x$dbgalertext; create public synonym v$alert_log for sys.v_$alert_log;
We can now grant access to this view.
grant select on v$alert_log to testuser1;
Creating your own objects in the SYS schema is considered bad practice.
For more information see:
Hope this helps. Regards Tim...