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

  1. Michel says:

    Thanks for providing the solutions. I just applied workaround #1 and now I can see the ORA- alert on the database main page on the GC console. But in the “Diagnostic Summary” section (just above the Alerts), it still shows “Alert Log: No ORA- errors”. Do you have any idea how to solve this?

    Also I did not get any email notification for my ORA error. Can you provide any help regarding this?

    Thanks!

  2. Michel says:

    I just applied applied the workaround for issue #2 and it worked like a charm.

    Appreciate if you can provide input for my questions on issue #1…

    Thanks so much!

    • marcel says:

      I’ve just added some lines of code for the alertlog.pl script, to make the timestamp of the last ORA-error also appear under the “Diagnostic Summary” section in the GC console. I’m also testing the My Oracle Support patch (patch 10170020) that was released to fix this 10.2.0.5 problem. After installing this patch on an 11g agent, it looks promising but we haven’t checked all combinations of agent versions and database version.

      • Michel says:

        I applied PSU3 (10.2.0.5.3) (patch 9282414 on the Management Agent (ver. 10.2.0.5.0) then applied patch 10170020 on top of it. The Alert Log under Diagnostic Summary is now showing the timestamp of the last ORA- error instead of “No ORA- errors”. This seems to solve the issue for a 10.2.0.5.0 Agent running on a 10.2.0.5.0 database.

        (My Oracle Support Note: 1260414.1)

  3. wat ik zocht, bedankt

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*