Effectively query your Oracle alert log via External Tables – Oracle 9i‏

There is nothing new about the concept of querying text files as “tables” using the Oracle External Tables functionality. Going further, a lot of you are probably already querying your Oracle alert log this way. If you did a quick search on Google, you will find tons of results on this subject but what you will not find (atleast I did not) is a good way of getting at the data.

  

If you are already on Oracle 11g, you are better off querying x$dbgalertext table. However, if you are still on 9i, I have quickly outlined the steps to query the Oracle alert log as a table. The interesting querying part will come later.

  

Query the v$Parameter table to get the background dump destination where the alert log is located

  

SELECT *
FROM v$parameter
WHERE NAME LIKE ‘%background_dump_dest%’;

  

Take the “value” and create a “Directory” object named BDUMP pointing to the location

  

CREATE DIRECTORY BDUMP as ‘<<value from above>>’;

  

The user does need permissions to create/drop directory objects

  

GRANT CREATE, DROP ANY DIRECTORY TO “Your_User_Id” ;

 

Now it is time to create the external table itself to point to the alert log (substitute your Oracle SID)

 

CREATE TABLE alert_log ( msg varchar2(4000) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location(‘alert_<<SID>>.log’)
)
reject limit 1000;

 

You might want to create a public synonym and grant rights to other users but in the intereste of brevity, lets keep this simple since you need other permissions to the “Directory” objects and so on that you can solve yourself.

 

SELECT * FROM alert_log;

 

image001 

 

 You can see a pattern above: A date/timestamp on its own line followed by one or more lines of log text.

This is where most blogs stop.  Sure, I can query it using WHERE clauses containing ORA-% messages but I crave for “more and easy”.

 

Create a view that identifies the rows with the timestamp information, we will use this later.

 

CREATE OR REPLACE VIEW alert_log_raw_view
AS
SELECT
rownum AS line_number,
CASE WHEN
  LENGTH(msg) = LENGTH(‘Mon Feb 14 06:12:27 2011’)
  AND SUBSTR(msg, -4, 4) IN (TO_CHAR(SYSDATE, ‘YYYY’),
  TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, ‘YYYY’)) – 1)
  )
THEN ‘Y’
ELSE ‘N’
END AS is_TimeStampRow,
a.*
FROM alert_log a
ORDER BY rownum;

 

Our ultimate goal is to have a final form of

 

NAME

TYPE

DESCRIPTION

ALERT_LOG_LINE_NUMBER

NUMBER

The line number from alert log itself

GROUP_NUMBER

NUMBER

In the above image, each red dot on the timestamp followed by the line is a single group

GROUP_LINE_NUMBER

NUMBER

The line numbering within the group

TIME_STAMP

DATE

Date typed column holding the timestamp value so that we can do date range queries

TIME_STAMP_STRING

VARCHAR2(4000)

Timestamp as string

MSG

VARCHAR2(4000)

The message

The following view builds upon the information we built before

 

CREATE OR REPLACE VIEW alert_log_view
AS
SELECT
   raws.line_number alert_log_line_number,
   groups.group_number,
   RANK() OVER (PARTITION BY groups.group_number ORDER BY raws.line_number) group_line_number,
   TO_DATE(groups.time_stamp, ‘DY MON DD HH24:MI:SS YYYY’) time_stamp,
   time_stamp AS time_stamp_string,
   raws.msg
FROM
(
   SELECT
      msg AS time_stamp,
      rownum AS group_number,
      line_number,
      line_number AS from_line_number,
      (LEAD(line_number, 1, line_number+1) OVER (ORDER BY line_number)) – 1 AS to_line_number
   FROM alert_log_raw_view
   WHERE is_TimeStampRow = ‘Y’
) groups,
alert_log_raw_view raws
WHERE
   raws.line_number BETWEEN groups.from_line_number AND groups.to_line_number
   AND raws.is_TimeStampRow = ‘N’ ;

 

The two views could be combined into one with the “WITH” clause but I ran into an Oracle 9i bug relating to materializing WITH clauses and had to separate them into two views.

 

The inline view “groups” identifies the line number ranges – from the timestamp row to the row before the next timestamp.  This information is then used in the main query to join back to the raw alert log data using the BETWEEN clause to identify the log lines for each group.  If this is confusing, please read the documentation on LEAD and RANK() analytic functions.

 

Now, we can do fancier queries like this one which gives us the most recent log information first and within that each group is ordered properly

 

SELECT *
FROM alert_log_view
ORDER BY group_number DESC, group_line_number ASC;

…or specific search like this one

 

SELECT * FROM alert_log_view
WHERE msg LIKE ‘ORA-%’
AND TRUNC(time_stamp) = TRUNC(SYSDATE)

 

or

 

SELECT * FROM alert_log_view
WHERE msg LIKE ‘ORA-%’
AND TRUNC(time_stamp) = TRUNC(SYSDATE)
AND group_number = 29;

 

Now, only your imagination and your knowledge of date functions is the limit to querying the alert log..

BTW, how would one query the SQL Server error log? Run xp_readerrorlog.

Advertisements

2 thoughts on “Effectively query your Oracle alert log via External Tables – Oracle 9i‏

  1. Hello

    This is indeed a very tidy and nice way to access alert log.
    But I am facing another challenge accessing alert logs in a RAC environment.
    This is because you are connected to a specific node in the RAC and can only read the alert log of this instance. How can you remotely access the alert log of other nodes in the RAC ? Is it possible at all ?

    Thanks

    Guy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s