OEM 10g ORA-00001 mystery

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Mon, 31 Mar 2008 19:41:22 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3604677C76@CWYMSX04.Corp.Acxiom.net>

I've got a mystery with OEM 10g that I'm hoping someone can help with. For most databases I support, I enable an ON SERVERERROR trigger and capture all possible ORA-* errors into a table for later analysis. After doing so on our OEM 10g repository, I noticed that we get anywhere between 500k and 800k ORA-00001 errors per day from the agent that loads data.

Here's a little more detail. The repository is on RHEL 4. The OMS is local. The agent process has its CLIENT_INFO as <server>: 4889_Management_Service, which is the one generating the errors. All agents are as well.

The errors come when loading into MGMT_METRICS_RAW. The duplicates are the ones always with KEY_VALUE as a single space. Here's the SQL statement that generates the errors:

INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE, METRIC_GUID, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, ' ' ), :3, :4, :5) For the life of me I can't explain why this is. It doesn't happen for all servers, just 10g and Linux servers and of those, just 4 out of 5 of them have data that behaves this way. All 5 of those servers are running on RedHat 4.

I wrote a simple script to get all instances (dups included) of bind values for every issue of the statement above, then performed lookups into SYSMAN tables to get name values for TARGET_GUID and METRIC_GUID. What comes back is the METRIC_NAME = 'wait_bottlenecks' for TARGET_TYPE of 'oracle_database', only for 4 of 5 Linux-based databases.

Maybe generating all these ORA-00001 errors is how OEM's code is suppose to behave, but it sure seems odd to me, especially at the volume I'm seeing AND that some databases are fine.

Anyone ever see this or have any grand ideas on how to get to the bottom of it?


Dave Herring, DBA |   A c x i o m  M I C S / C S O 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

Received on Mon Mar 31 2008 - 19:41:22 CDT

Original text of this message