Read the alert log with SQL
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=BR19 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
/
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
/
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
/
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
/
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')
/
The next step, that I’ll leave to you, is to write the SQL interface to read these trace files…
Happy coding!
Posts
Wonderful article. I was searching the web for something like that, thanks for sharing such a good piece of code.
ReplyDelete