Monitoring Alert Log

Monitoring Alert Log has become lot easier with Oracle 11g . In Oracle 11g , the contents of the alert log are exposed via fixed table . 

Also , in  Oracle 11g , the alert log is also stored in xml format . You can ADRCI to view / purge the contents . For ADRCI , please see one of my post here ( http://mfzahirdba.blogspot.com/2010/05/health-check.html)

Prior to this release , you can construct an external table poiniting to alert log . For this approach , please refer to chapter 3 of Tom Kyte's Expert Oracle book .

Let us returned to using fixed table ( it does not seems to be documented ) . Alert log is exposed as "X$DBGALERTEXT".

Let us create a view , so that we can grant access to non sys users.

SQL> CREATE OR REPLACE VIEW SYS.ALERT_LOG_v
2 AS
3 SELECT * FROM X$DBGALERTEXT;


View created.

SQL> grant select on SYS.ALERT_LOG_v to scott ;

Grant succeeded.

SQL> conn scott/tiger



Connected.


SQL> desc sys.alert_log_v


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)

....
.....
 
I can issue the following query  ( in a recurring job ) to see if there are lot of "ORA-" errors in the last 15 minutes .
 
SQL> SELECT message_text ,

2 client_id ,
3 originating_timestamp
4 FROM sys.alert_log_v
5 WHERE originating_timestamp >= sysdate-(1/24/60)*15
6 AND message_text LIKE '%%ORA-%%';


no rows selected


Monitoring Alert Log is made easier with this approach .