Alert Log monitoring on Unix/Linux OS

Before Oracle Database 11g it was possible to access the alert log using SQL with an external table, or using a pipelined function which in turn uses the UTL_FILE package.
Now, in Oracle 11g there is the fixed table X$DBGALERTEXT: when we query this table, Oracle reads the alert in XML format, parses it and returns its content. We can find the X$DBGALERTEXT fixed table under the v$fixed_table view:

SQL> SELECT * FROM v$fixed_table WHERE name = ‘X$DBGALERTEXT’;

NAME OBJECT_ID TYPE TABLE_NUM
—————————— ———- —– ———
X$DBGALERTEXT 4294952492 TABLE 751

The columns of this table are the following:

SQL> 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(16)
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

If we want to create a custom system to monitor our alert log, it’s possibile to use this fixed table as starting point. In this article, the type of monitoring proposed was written on Unix and Linux system, but we can use it also on Windows system with few changes. The alert log and our monitoring system are on two different servers (source and target) with a database created on each one. The monitoring system is composed of the following elements:
– a shell script (run.ksh)
– an SQL script (alert_monitoring.sql)
– an entry in crontab file

The content of the shell script is the following:
#!/bin/ksh
#
#This shell script it’s used to monitor the alert log of database with sid
#The content of the alert log is read from the fixed table x$dbgalertext through database link
#Valid for Oracle 11g

SID=${1}

export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/9.2.0.6
export ORACLE_SID=SOURCEDB
export PATH=/usr/bin::/usr/local/bin:/opt/SUNWvxva/bin:/usr/bin/nsr:/usr/sbin/nsr:$ORACLE_HOME/bin:$ORACLE_HOME/lib:/opt/bin:/usr/ccs/bin

export WORK_PATH=/app/oracle/scripts/SOURCEDB/monitoring/11g
export HOST=myhost

cd $WORK_PATH
sqlplus -s ‘/ AS SYSDBA’ @monitoring_alert $SID
grep “no rows selected” ./monitoring_alert_$SID.log

if [[ $? -eq 1 ]]; then
cat monitoring_alert_$SID.log | mailx -s “Error on database “$SID “marco.tamassia@wordpress.com”
fi

The shell script uses the mailx command to send the e-mail with the ORA- errors detected, but we can use also other similar commands. It’s necessary that we configure mailx before.
The content of the SQL script monitoring_alert.sql is the following:

/******************************
SCRIPT NAME: monitoring_alert.sql
AUTHOR: Marco Tamassia
VERSION: 1.0

INFO:
For run this script it’s necessary the creation of the following objects on the db target:
– The view SYS.v_x$dbgalertext (on target database)
– The grant of SELECT on SYS.v_x$dbgalertext to SYSTEM user (on target database)
– The database link to target database (on source database)
******************************/

SET lines 120
SET pages 1000
SET feedback ON
SET verify OFF
COL message_text FOR a100

DEFINE SID=&1

SPOOL ./monitoring_alert_&1..log

SELECT ROWNUM “Line”, message_text “Error”
FROM SYS.v_x$dbgalertext@VD&1
WHERE originating_timestamp > (SYSDATE – 5/1440) AND message_text LIKE ‘%ORA-%’
ORDER BY originating_timestamp;

SPOOL OFF
EXIT

In the crontab file we have to insert the following line:
# crontab -l
0,5,10,15,20,25,30,35,40,45,50,55 8,9,10,11,12,13,14,15,16,17,18,19 * * * /app/oracle/scripts/SOURCEDB/monitoring/11g/run.ksh TARGETSID >/app/oracle/scripts/SOURCEDB/monitoring/11g/run.ksh.nohup 2>&1&

The monitoring system works in this way:
1) Every x minutes the shell script run.ksh is run with the sid of the target database as input parameter
2) The shell script run.ksh call the monitoring_alert.sql with the sid of the target database as input parameter
3) The alert log of the target database is read through database link, querying the fixed table X$DBGALERTEXT
4) The query extract all the ORA- errors detected in the last five minutes
5) The content of the spool file monitoring_alert.log is sent to us with mailx command
6) If no ORA- errors are found, no e-mail is sent

Requirements:
– Creation of a database link (on the source database):
CREATE PUBLIC DATABASE LINK VDTARGETSID CONNECT TO “USER1” IDENTIFIED BY “password” USING ‘VDTARGETSID’;
– Creation of a view to use the fixed table X$DBGALERTEXT, the view it’s necessary because it’s not possible to grant a privilege on a fixed table (on the target database):
CREATE VIEW v_x$dbgalertext AS SELECT * FROM x$dbgalertext;
– Grant SELECT privilege on the view created (on the target database):
GRANT select ON SYS.v_x$dbgalertext TO “USER1”;

This is only an example of how it’s possible to use the fixed table X$DBGALERTEXT to monitor the alert log of an Oracle Database 11g. Many different changes can be done, and the monitoring system can be implemented in a different architectures.