How to delete one row from MGMT_Severity table

[aking]

Hi,
I'm trying to get rid of a stale alert from Enterprise Manager from August this year. The procedure is to find the row containing the alert in the Sysman.MGMT_Severity table and delete that row. Then the alert disappears. This has worked previously.
This time i deleted the row using SQL Manager for Oracle but the alert still remains in Enterprise Manager.
Now the situation is that i can no longer see the row in question in SQL Manager but if i do the following select statement:
SQL> select * from sysman.mgmt_severity where message='81% of archive area C:\Oracle\Multiplex\ArchiveLogs\ is used.';
then it returns 3 rows.
I have tried a fresh installation of SQL Manager on a new server just to make sure i wasn't looking at cached results or something. I have tried bouncing the database.
Since the last word in Oracle is the command line i assume the row is still there, would there be any reason why i can no longer see it in SQL Manager? I am about to try Toad instead....

 

[karluk]

Did Oracle support tell you to run ad-hoc deletes from sysman.mgmt_severity? It's darn rare for Oracle support to give its blessing to running update/delete sql against a repository. Normally they provide a package or other utility for performing this functionality.

Checking on support.oracle.com, I found note 806052.1, "Troubleshooting guide to remove old warning and critical alerts from grid console". I would try the suggestions in that article before deleting rows from one of the management repository tables. Glancing through the article, it looks as if you may need to download the Emdiag kit and run a "repvfy verify metrics -level 9 -details" command followed by "exec mgmt_diag.VerifyMetrics(702,0,TRUE);" as sysman.

 

[aking]

Thankyou karluk, i will go get this note and check it out.
No, oracle support did not tell me to run this 'ad-hoc delete'. But if i waited for answers for them i'd be out of a job by now.
I got the tip from here:

http://www.databasejournal.com/feat...541/Clearing-Alerts-in-Enterprise-Manager.htm

As i said above, i've used the technique before and it always worked - and getting stale records in my enterprise manager is quite common.
Anyway i downloaded Toad, went to look at the sysman.mgmt_severity table and i found the row. I went back to SQL Manager and i still couldn't find it. There must be some kind of view option on SQL Manager screening it, cos that row was definitely definitely still there. So I deleted the row (again) but this time with Toad and this time the alert vanished. Have switched to Toad in preference over SQL Manager now. My best guess was that i didn't commit the ad-hoc delete with SQL Manager, but it never gave me the option, and i even installed another copy of sql manager on another server, and i bounced the database - still couldn't see that row. So i don't know what else i could have done.
Anyway thanks for the tip, maybe the official way is better and i'll switch - although up to now finding the stale alert and deleting the row has been a 10 minute job.

 

[carp]

if i waited for answers for them i'd be out of a job by now" - interesting. Every shop I've ever worked at would have fired you for what you are doing, since it potentially voids your support agreement. I would recommend (a) go with TOAD and (b) either find a document in Oracle support that says deleting rows like this is OK or open an SR and explicitly ask them for an approved method to resolve the problem - and be sure to cite the source of your current solution. This should cover you in the event of a mishap.

 

[karluk]

I have read through the article you cite. I am quite concerned that the cleanup the author expects to happen may not, in fact, be happening. He says

...there is a better way, and that way is to delete directly from the MGMT_SEVERITY table and let the SEVERITY_DELETE trigger do all the work for you.

In other words, he is hoping that a delete from MGMT_SEVERITY will cascade down to related rows in other tables because of a trigger named "SEVERITY_DELETE" that supposedly exists in the Grid Control repository.

But I have logged into my Grid Control repository database, and there is no such trigger. I ran the following sql.

SQL> select trigger_name from dba_triggers where trigger_name like '%SEV%DEL%';

TRIGGER_NAME
------------------------------
SEV_ANNOTATION_DELETE_TR

So the trigger that Mr. Callan is expecting to be there is nowhere to be found. It's very possible that your delete has gotten rid of enough of the alert to prevent it from being displayed in Grid Control, but left a bunch of garbage behind in other tables that never got cleaned up. If that's the case, the long term effect on Grid Control is, I fear, quite unpredictable.

 

[karluk]

I should also say that I have a larger concern about Mr. Callan's method of deleting old alerts that goes beyond whether a specific trigger exists. It certainly doesn't appear that Mr. Callan consulted with Oracle Corp. to determine whether this hypothetical SEVERITY_DELETE trigger did exactly the right cleanup that was needed or, even if it did, that Oracle was committed to maintaining this exact functionality of the SEVERITY_DELETE trigger during future upgrades. In fact it's very possible that the SEVERITY_DELETE trigger actually existed two years ago when Mr. Callan wrote his article, but that it was eliminated in a more recent release of Grid Control. So, I consider Mr. Callan's suggestion to have been highly suspect back in 2007 and even more dubious now, at the end of 2009.

 

[Turkbear]

Hi,
at this site:

http://www.orafaq.com/forum/t/98185/0/

There is a query that seems to show that the trigger is still in place in 2008 in Oracle 10g.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[aking]

Hi guys, clearly i will have to check this out more, and maybe even give up on ad-hoc deletes.
But thanks for looking into it, i must admit i took this article on trust, i will go look for those triggers on monday.
V suprised to hear that someone thinks this is a sacking offense, as for voiding oracle support - are you being serious? I generally get paid for making things work and keeping them going. I've had battles with numerous companies trying to get out of support contracts. I heard about nightmares with oracle support - and i've had my own share too - hence my less than positive view. But have never heard of an oracle support being voided for stuff like this. I hope you were just trying to scare me Carp....