Unable to monitor ORA-errors in alertlog for databases

There is a problem monitoring Oracle databases in OEM. Alerts for ORA- errors that appear in the alertlog of databases won’t get generated and you will not be able to view the alertlog (time range) for databases of this specific version.

The problem is introduced with database version and is caused by the format of the timestamp that is used in the alertlog as of For some reason Oracle decided to change the format of the timestamp to include the timezone for this version only. This format is only used in database version, even in 11g the original format is used.

Non timestamp:
Thu Nov 18 19:15:05 2010 timestamp:
Thu Nov 18 19:15:05 MEST 2010

This post will give you the solution to fix this problem so this bug won’t be a show stopper for upgrading you 10g database to the latest patchset as required for extended support – August 2011).
Some time ago, Oracle released a patch on My Oracle Support for this problem (patch: 10170020) which is available for (PSU 3 applied) agents and (PSU 2 applied) agents. It look like this patch is working (on the 11g agent), but we haven’t tested it on different combinations of agent versions and databases.

Issue #1 ORA- errors not showing up in OEM

To solve this issue, the regular expression the OEM agent uses to go through the alertlog of an database instance needs to be changed. This regular expression used is stored in the following xml file:

$<agent home>/sysman/admin/metadata/instance.xmlp

Change the Property “log_timestamp_format”


<Property NAME="log_timestamp_format" SCOPE="GLOBAL"><![CDATA[(\w+) (\w+)( | )31 023:059:059 02037]]></Property>


<Property NAME="log_timestamp_format" SCOPE="GLOBAL"><![CDATA[(\w+) (\w+)( | )31 023:059:059 (\w+\s|)02037]]></Property>

This change will let alerts for ORA-errors show up in you Grid Control, but on the Database Instance page under Diagnostic Summary it will only show “No ORA- errors” for the Alert Log field, while it should show the date/time for the last ORA-error. This problem is caused by the agent sending ORA-error with the data format to the OMS, while the OMS expects the “normal” timestamp (without the timezone).

To solve this, you have to edit the file:

$<agent home>/sysman/admin/scripts/alertlog.pl

In this file find the subroutine writeErrStack (look for the line sub writeErrStack { ). Put the following lines after the comment lines describing the functionality of the writeErrStack subroutine:

my $fixedTimestamp="";
if ($_[0] =~ m/$log_timestamp_format/) {
$fixedTimestamp = "$1 $2 $4 $5:$6:$7 $9";

Then the final step is change the line (a couple of lines after the inserted code):

my $outputLine = "$_[0]/$_[1]|"; # errStackTimestamp/# lineNumber


my $outputLine = "$fixedTimestamp/$_[1]|"; # fixed errStackTimestamp/# lineNumber


Issue #2 showing alert log contents in OEM (specifying a search criteria (begin- and enddate)

To solve this problem you have to update the following perl script:

$<agent home>/sysman/admin/scripts/alertlogViewer.pl

At line 141 I’ve inserted the following 3 lines of code to generate a date format which the str2time Perl
function can handle:

if ($record =~ /(\w+) (\w+)( | )(\d+) (\d+):(\d+):(\d+) (\w+\s|)(\d+)/) {
  $record = "$1 $2 $4 $5:$6:$7 $9\n";