Oracle DB Development

Creating a feed with ORA-00600 errors

Instead of having to look at your different alert logs for ORA-00600 errors frequently, why not have them come to you? This post will, as promised, show the Perl code needed to publish a feed with ORA-00600 errors. This is building on the previous two posts that shows how to create an external table for the alert log and how to pull out all information for an ORA-00600 so each fetch retrieves one the data you're interested in.

The feed I'm using is a RSS 2.0 format. All feeds are fairly closely related so you can change it or provide different ones if you want to. You'll have to look up the different formats and then make the changes you want to this code.

The script I have here is a simplified version that removes all the usual dynamic nature and flexibility with parameters I usually have. This is done for brevity and to focus the script on the task at hand - delivering the ORA-00600 errors to your newsreader soon after they occur.

The script is not pretty and it could be written much nicer with use of modules such as XML::RSS. Unfortunately that module has a chain of dependencies that I didn't want to satisfy, so I'm creating the tags the old fashioned hard coded way. If you are to do a lot feeds with Perl, you will want to get that module installed.

!/usr/bin/perl
require v5.6;
use strict;
use CGI qw(:standard :html3 :netscape);
use CGI::Carp;
use DBI;
use DBD::Oracle qw(:ora_session_modes);

my ($ORACLE_HOME, $DBH);

if ( $ENV{TWO_TASK} ) { undef $ENV{TWO_TASK}; }

$ENV{"ORACLE_HOME"}='/path/to/oracle/install/dir';

my $statusRec;

print 'Content-Type: text/xml' . "\n\n";

print '<?xml version="1.0"?>' . "\n";

print '<rss version="2.0">' . "\n";

print '  <channel>' . "\n";

print '    <title>Oracle ORA-00600</title>' . "\n";

print '    <description>ORA 600 errors</description>' . "\n";

print '    <language>en-us</language>' . "\n";

print '    <copyright>Copyright 2007</copyright>' . "\n";

print '    <managingEditor>email@domain</managingEditor>' . "\n";

print '    <webMaster>email@domain</webMaster>' . "\n";

print '    <generator>script name</generator>';

# Create db connection

$DBH = DBI->connect("DBI:Oracle:host=zzz;SID=zzz;port=nnnn", "alertlog", "alertlog",

    { RaiseError => 1, AutoCommit => 0 })

    || die "Could not connect\n\tError: $DBI::errstr";

my $STH = $DBH->prepare("with alert as

               (select rownum rn

                      ,text_line

                  from alert_log)

            select a.text_line date_msg

                  ,b.text_line file_msg

                  ,c.text_line err_msg

              from alert a inner join alert b

                on a.rn = b.rn - 1

                           inner join alert c

                on b.rn = c.rn - 1

             where c.text_line like '%ORA-00600%'");

$STH->execute() || die "Couldn't execute statement: $STH->errstr";

while($statusRec = $STH->fetchrow_hashref())

{

  print "     <item>\n";

  print "       <title>ORA-600 Occured</title>\n";

  print "       <description>An ORA-00600 has been detected.\n";

  print "<p>\n";

  print " \n";

  print "The following information was logged about the event:\n</p><p>";

  print $statusRec->{"DATE_MSG"}   . "</p>\n<p>";

  print $statusRec->{"FILE_MSG"} . "</p>\n<p>";

  print $statusRec->{"ERR_MSG"}  . "</p>\n" . "</description>\n";

  print "       <author>your name</author>\n";

  print "     </item>\n";

}

$STH->finish();

$DBH->disconnect();

print "  </channel>\n";

print "</rss>";

That is all it takes. This is obviously a CGI script that would run in your web server. If you have one with CGI capabilities configured, you'd place the script there and call it with an url like "http://your.domain/cgi-bin/script_name.cgi". Ask your web admin if you don' know where and how to place this script. If you have your own web server and don't know how to do this, leave a comment and I'll try to give you a few quick hints about how to get it working.

I'm afraid the code above will be truncated by google's new blog interface. The code will still be there so just cut and paste it into your favorite code editor and you'll have all the needed code.

You will probably want to change these lines to something different:

$ENV{"ORACLE_HOME"}='/path/to/oracle/install/dir';

$DBH = DBI->connect("DBI:Oracle:host=...snip...,

...and the following nodes...

copyright

managingEditor

webMaster

generator

author

The first one should point to where you have Oracle installed and the second will be your connection string. If you don't know the port ask your DBA or try the default of 1521. It is the port you have your listener configured to listen on.

There is no guarantee that the code works as is. Every environment is different and the code is clearly not environment proof. If you have problems with it, check your error log for the web server and leave a message if you want my input on what the problem might be.

Enough alert log stuff? Probably. Sorry if you feel I've overdone it and got stuck on this subject for too long. Next post will not even mention the alert log.

If you have APEX or want to wait for 11G where it will be a supported option, you will want to use APEX and pure SQL for this rather than Perl. A good intro to how to do it can be found on asktom.oracle.com. It is written by Sean Dillon and looks well written and easy to follow, it can be found here. I've not tried it myself, but it sounds straight forward and anything from the people behind asktom and APEX at Oracle is usually both easy to understand and easy to implement (relative to the subject's complexity).

Querying the alert log for ORA-00600

Time to expand on the last post. As promised this will be about how to get the ORA-00600 information out of an external table for the alert log. We will use the same user and setup as we had in that post.

Lets first review how an ORA-00600 is represented in the alert log.

   Tue Feb  1 02:09:44 2005
   Errors in file /path/to/trace/file:
   ORA-00600: internal error code, arguments: [1100],
     [0x388F0D990], [0x382253888], [], [], [], [], []

The actual arguments does not matter, I took them from a forum post to just have an example. What we care about for what we're doing here is that the format is one line with a date, one line with a reference to a trace file, and one line specifying the error code (ORA-00600) and the needed arguments (for Oracle support).

Thus, we'll need to find the row with the ORA-00600 and match it up with the two previous rows. To do this we'll return to my favorite SQL key word, the with clause. It lets us get all rows, get a generated unique id per row, and know that the rows we look at will be identical as we're now using the same source. In theory, we could end up with the rows in different order if we joined the actual table with itself. Oracle does not guarantee the order you retrieve data in unless you order it, end we have nothing to order on here.

    with alert as (select rownum rn
                       ,text_line
                   from alert_log)
 select a.text_line date_msg
       ,b.text_line file_msg
       ,c.text_line err_msg
   from alert a inner join alert b
     on a.rn = b.rn - 1
                inner join alert c
     on b.rn = c.rn - 1
  where c.text_line like '%ORA-00600%';

This gives us the alert log message in three columns. We can now process it any way we want. It could be used to create a report or generate an email with your ORA-00600 messages.

However, the actual ORA-00600 shows that we made a mistake when we created the table. It cuts off the text_line after a comma. That is not what we want, we want all data up to the end of the line. So we need to change the definition of the table to make sure we get all of that.

   conn alertlog/alertlog
   drop table alert_log;
   create table alert_log
     (text_line varchar2(255))
     organization external
     (type oracle_loader
     default directory bdump
     access parameters(
     fields terminated by x'10')
     location ('alert_ORCL.log'));

That's better, the default delimiter of comma has been replaced with linefeed (ASCII 10) for UNIX platforms. Replace it with what you need. As long as it is a charcter that will never occur in a text line in your alert log, this will work fine.

Now that we have a select that gets all data for an ORA-00600 in each row it returns, it is easy to operate on that data. This model can of course be used to list other pieces of data from the alert log. One such use could be to report how long time it takes between log switches. That is left as an exercise for the reader.

My next post will show how to use this to create a RSS feed for your ORA-00600. That allows you to within minutes know when you have had an ORA-00600 occur and you get it delivered as a separate feed to your news/blog reader.

SQL Access to the alert log

I'm going to discuss how to get SQL access to your alert log in this entry. I will then build on that to make it even more useful in future posts.

Browsing the alert log can be convenient if you're already on the server. However, it is often not possible or at least not overly convenient. You may find that as the developer DBA you do not have access to the production server, or you have to log in to many different servers to check all your alert logs. Since SQL is the main tool for DBAs and one of the most critical for developers of database driven applications, it would make sense to access the key log for database operations and issues via the same interface and be able to use your SQL tool of choice.

The way to be able to access the alert log with SQL is to define an external table for it. So I'll use this need to write a little about how to create an external table for text file. It's pretty easy and can often be very useful.

create directory bdump as '/path/to/bdump';
create user alertlog identified by alertlog;
alter user alertlog quota unlimited on users;
grant create session, resource to alertlog;
grant read,write on directory bdump to alertlog;

The directory path needs to be what you have "background_dump_dest" set to. You can look the current value up in v$parameter. To be able to do this with one script across databases, you'll want to make this more dynamic with pl*sql such that the directory is retrieved from the database and used to build a dynamic DDL statement.

Now that we have a user with access to our bdump directory, we'll create our alert_log table.

create table alert_log
 (text_line varchar2(255))
 organization external
 (type oracle_loader
  default directory bdump
  location ('alert_ORCL.log'));

This is a minimal create external table statement. There are many more options to use, but we'll leave those alone in this blog post.

The create table statement is pretty straight forward, here's what it will do:

• Creates a table with data that is located external to the database
• Take all data in each line into the column text_line
• Use the normal oracle_loader (SQL*Loader) driver to access the data
• It uses the bdump directory object to specify where the alert log file is located.
• Get data from the file alert_ORCL.log.

That is all it takes. We can now access this table with SQL and use our normal techniques for filtering and aggregating information.

To look at the snapshot too old messages we generated in a previous blog post, we'd use this statement:

select text_line
 from alert_log
where text_line like '%ORA-01555%';

TEXT_LINE