Query the Oracle Alert Log using SQL commands

There are often times when you want to know what has been recorded in the database’s alert log, but may not want to or be able to log into the database server and view the file. An SQL interface to the alert log can offer several advantages – you don’t need an account on the database server and you can read the alert log from SQL and PLSQL programs (which can be incorporated into other programs). This posting will show you how to create a usable SQL interface to the alert log. Then using this interface you can write simple SQL to show the alert log entries for the past hour or all the alert log entries that resulted from errors so far this month.

 

External tables, introduced in database release 9.0 give us the ability to access the alert log using SQL. The IGNORE NULLS option to the LAST analytic function, introduced in database release 10g, allows us to really make use of the dates we find in the alert log.

 

In this example, we'll create all the schema objects in the SYSTEM schema and run the commands as user SYS (we can’t grant ourselves privileges, so we can’t run all of the statements as SYSTEM)

 

The first block of code below does three things, first is to create a directory for the bdump_dest – the directory location of the alert log. We call it appropriately BDumpDir. Next, we need to grant the read privilege to SYSTEM, so this directory can be used for the external table. Finally, we create the external table on the file alert_sid.log:

 

DECLARE
 BDumpDir  VARCHAR2(200);
 SID       VARCHAR2(16);
 ObjectExists EXCEPTION;
 PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
 -- get the bdump dir
 SELECT value
 INTO BDumpDir
 FROM v$parameter
 WHERE name='background_dump_dest';
 -- create the directory for the bdump dir
 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
   BDumpDir||'''';
 -- grant the necessary privileges
 EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
 -- get the SID
 SELECT instance_name INTO SID FROM v$instance;
 -- create the external table
 EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL
   (TEXT VARCHAR2(255)
   ) ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY BDUMP_DIR
    ACCESS PARAMETERS
    (records delimited by newline
     nobadfile
     nologfile
    )
    LOCATION (''alert_'||SID||'.log'')
   )
   REJECT LIMIT UNLIMITED'
 ;
-- ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/

Now we can query the external table to read the alert log.

 

SELECT * FROM system.alert_log_external
WHERE ROWNUM < 20;
TEXT
----------------------------------------------------------------------
Mon Aug 29 18:00:10 2011
Starting background process EMN0
EMN0 started with pid=20
Mon Aug 29 18:00:10 2011
Shutting down instance: further logons disabled
Mon Aug 29 18:00:10 2011
Stopping background process QMNC
Mon Aug 29 18:00:10 2011
Stopping background process CJQ0
Mon Aug 29 18:00:12 2011
Stopping background process MMNL

TEXT
----------------------------------------------------------------------
Mon Aug 29 18:00:13 2011
Stopping background process MMON
Mon Aug 29 18:00:14 2011
Shutting down instance (immediate)
License high water mark = 6
Mon Aug 29 18:00:14 2011
Stopping Job queue slave processes
Mon Aug 29 18:00:14 2011

19 rows selected.

This is a first step, but the log file isn’t really very usable - the entries are all timestamped, but the timestamp appears inline with the entries. We need to associate the timestamp with each line in the file, so we can query the alert log based on timestamp. We know the timestamp appears in a consistent format, so we can look for these timestamps and convert them to a date datatype. We create a function to do this for us, so we can trap and ignore any errors related to invalid dates. The alert_log_date function looks for strings in the expected timestamp format and converts them to a date. If the line in the alert log does not appear in a timestamp format (ORA-1846 is raised), we ignore it.

 

CREATE OR REPLACE FUNCTION system.alert_log_date( text IN VARCHAR2 )
 RETURN DATE
IS
 InvalidDate  EXCEPTION;
 PRAGMA EXCEPTION_INIT(InvalidDate, -1846);
BEGIN
 RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY'
   ,'NLS_DATE_LANGUAGE=AMERICAN');
EXCEPTION
 WHEN InvalidDate THEN RETURN NULL;
END;
/

So now, we can get the alert log entry and the timestamp, if present:

SELECT ROWNUM row_num ,system.alert_log_date(text) alert_date, text
FROM system.alert_log_external
WHERE ROWNUM < 20
/

TEXT
----------------------------------------------------------------------
Mon Aug 29 18:00:10 2011
Starting background process EMN0
EMN0 started with pid=20
Mon Aug 29 18:00:10 2011
Shutting down instance: further logons disabled
Mon Aug 29 18:00:10 2011
Stopping background process QMNC
Mon Aug 29 18:00:10 2011
Stopping background process CJQ0
Mon Aug 29 18:00:12 2011
Stopping background process MMNL

TEXT
----------------------------------------------------------------------
Mon Aug 29 18:00:13 2011
Stopping background process MMON
Mon Aug 29 18:00:14 2011
Shutting down instance (immediate)
License high water mark = 6
Mon Aug 29 18:00:14 2011
Stopping Job queue slave processes
Mon Aug 29 18:00:14 2011

19 rows selected.

This is progress, but we really want that datestamp carried down so each line of text is marked with the datestamp. This is where the IGNORE NULLS option to the LAST function comes into play. We use our working query, but then wrap another query around it.

 

SELECT row_num
     ,LAST_VALUE(alert_date IGNORE NULLS) OVER(ORDER BY row_num
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) alert_date
     ,alert_text
FROM (SELECT ROWNUM row_num
           ,system.alert_log_date(text) alert_date
           ,text alert_text
     FROM system.alert_log_external
    )
WHERE ROWNUM < 20
/

   ROW_NUM ALERT_DAT ALERT_TEXT
---------- --------- ------------------------------------------------------------
         1 29-AUG-11 Mon Aug 29 18:00:10 2011
         2 29-AUG-11 Starting background process EMN0
         3 29-AUG-11 EMN0 started with pid=20
         4 29-AUG-11 Mon Aug 29 18:00:10 2011
         5 29-AUG-11 Shutting down instance: further logons disabled
         6 29-AUG-11 Mon Aug 29 18:00:10 2011
         7 29-AUG-11 Stopping background process QMNC
         8 29-AUG-11 Mon Aug 29 18:00:10 2011
         9 29-AUG-11 Stopping background process CJQ0
        10 29-AUG-11 Mon Aug 29 18:00:12 2011
        11 29-AUG-11 Stopping background process MMNL

   ROW_NUM ALERT_DAT ALERT_TEXT
---------- --------- ------------------------------------------------------------
        12 29-AUG-11 Mon Aug 29 18:00:13 2011
        13 29-AUG-11 Stopping background process MMON
        14 29-AUG-11 Mon Aug 29 18:00:14 2011
        15 29-AUG-11 Shutting down instance (immediate)
        16 29-AUG-11 License high water mark = 6
        17 29-AUG-11 Mon Aug 29 18:00:14 2011
        18 29-AUG-11 Stopping Job queue slave processes
        19 29-AUG-11 Mon Aug 29 18:00:14 2011

19 rows selected.

So, now we get the line number, date stamp and text for each alert log entry. Hm.. It’s starting to look more like a good old table. Let’s add the starting line number for each alert log entry, so we can extract these entries easier. Again, we’ll build on the query we have by wrapping it with another. We get the starting line number based on the presence of a timestamp in the base table.

 

SELECT row_num
     ,LAST_VALUE(low_row_num IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) start_row
     ,LAST_VALUE(alert_date  IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) alert_date
     ,alert_text
FROM (SELECT ROWNUM row_num
           ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num
           ,system.alert_log_date(text) alert_date
           ,text alert_text
     FROM system.alert_log_external
    )
WHERE ROWNUM < 20
/

   ROW_NUM  START_ROW ALERT_DAT ALERT_TEXT
---------- ---------- --------- ------------------------------------------------------------
         1          1 29-AUG-11 Mon Aug 29 18:00:10 2011
         2          1 29-AUG-11 Starting background process EMN0
         3          1 29-AUG-11 EMN0 started with pid=20
         4          4 29-AUG-11 Mon Aug 29 18:00:10 2011
         5          4 29-AUG-11 Shutting down instance: further logons disabled
         6          6 29-AUG-11 Mon Aug 29 18:00:10 2011
         7          6 29-AUG-11 Stopping background process QMNC
         8          8 29-AUG-11 Mon Aug 29 18:00:10 2011
         9          8 29-AUG-11 Stopping background process CJQ0
        10         10 29-AUG-11 Mon Aug 29 18:00:12 2011
        11         10 29-AUG-11 Stopping background process MMNL

   ROW_NUM  START_ROW ALERT_DAT ALERT_TEXT
---------- ---------- --------- ------------------------------------------------------------
        12         12 29-AUG-11 Mon Aug 29 18:00:13 2011
        13         12 29-AUG-11 Stopping background process MMON
        14         14 29-AUG-11 Mon Aug 29 18:00:14 2011
        15         14 29-AUG-11 Shutting down instance (immediate)
        16         14 29-AUG-11 License high water mark = 6
        17         17 29-AUG-11 Mon Aug 29 18:00:14 2011
        18         17 29-AUG-11 Stopping Job queue slave processes
        19         19 29-AUG-11 Mon Aug 29 18:00:14 2011

19 rows selected.

Finally, we put this nested query into a view to hide its complexity and create a public synonym on it. You can decide who you allow to read the alert log by granting them SELECT on the alert_log view.

 

CREATE OR REPLACE FORCE VIEW system.alert_log as
SELECT row_num
     ,LAST_VALUE(low_row_num IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) start_row
     ,LAST_VALUE(alert_date  IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) alert_date
     ,alert_text
FROM (SELECT ROWNUM row_num
           ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num
           ,system.alert_log_date(text) alert_date
           ,text alert_text
     FROM system.alert_log_external
    )
;
DECLARE
 ObjectExists EXCEPTION;
 PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
 EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';
-- If the synonym exists, drop and recreate it
EXCEPTION WHEN ObjectExists THEN
 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM alert_log';
 EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';
END;
/

Now with our usable view built, we can look for alert log entries with SQL. For example; let’s see the alert log entries for the past hour:

 

SELECT row_num, alert_text
FROM alert_log
WHERE alert_date > SYSDATE - 1/24

no rows selected

Let’s see all the alert log entries for the past month that resulted from errors (contain the text ORA-).

 

SELECT row_num, alert_text
FROM alert_log
WHERE start_row IN (SELECT start_row
                   FROM alert_log
                   WHERE REGEXP_LIKE(alert_text,'ORA-')
                  )
AND alert_date > TRUNC(SYSDATE,'MON')
/

no rows selected