Thursday, January 1, 2009

Query the Oracle Alert Log using SQL commands

Read the alert log with SQL

Published in Database Tips by cdawes Tuesday February 27, 2007

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

------------------------------------------------------------------------------

Thu Jan 25 14:58:36 2007

Thread 1 advanced to log sequence 227

Current log# 1 seq# 227 mem# 0: C:\INFO\ORACLE\ORADATA\ORA102\REDO01.LOG

Dump file c:\info\oracle\product\10.2.0\admin\ora102\bdump\alert_ora102.log

Fri Jan 26 09:18:36 2007

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Windows XP Version V5.1 Service Pack 2

CPU : 2 - type 586

Process Affinity : 0×00000000

Memory (Avail/Total): Ph:671M/2046M

Fri Jan 26 09:18:36 2007

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

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


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

/

Error Log Query, Image 1

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

/

Alert Log, Image 2

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

/

image_03.jpg

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

/

image_04.jpg

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')

/

image_05.jpg

The next step, that I’ll leave to you, is to write the SQL interface to read these trace files…
Happy coding!


Curtsey - Database Specialist Vol.9 No.12 - Script of the Month - Article by Chip Dawes