Unable to monitor ORA-errors in alertlog for 10.2.0.5 databases

There is a problem monitoring Oracle 10.2.0.5 databases in OEM. Alerts for ORA- errors that appear in the alertlog of 10.2.0.5 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 10.2.0.5 and is caused by the format of the timestamp that is used in the alertlog as of 10.2.0.5. 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 10.2.0.5, even in 11g the original format is used.

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

10.2.0.5 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 10.2.0.5 (PSU 3 applied) agents and 11.1.0.1 (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”

From:

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

To:

<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 10.2.0.5 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

To:

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";
}
This entry was posted in Enterprise Manager and tagged , , , , , , , , , , . Bookmark the permalink.

5 Responses to Unable to monitor ORA-errors in alertlog for 10.2.0.5 databases

Leave a Reply

Your email address will not be published.

Blue Captcha Image
Refresh

*