Home » RDBMS Server » Server Administration » trigger on MGMT_AVAILABILITY table puts OEM in a weird state.
trigger on MGMT_AVAILABILITY table puts OEM in a weird state. [message #248027] Wed, 27 June 2007 17:12 Go to next message
geeklol
Messages: 59
Registered: March 2005
Member
Hi!
I am trying to capture the listener and database up-down events by having this trigger on the MGMT_AVAILABILITY.

I have copied and pasted the trigger below.

I have a smiliar trigger on table MGMT_CURRENT_METRICS to measure the Diskactivity etc and it works fine.

But for some reason, when i fire this trigger by bringin a listener down-up there are 2 things that happens,
When i stop and start the listener on the host, it doensn;'t communicate to the OEM, so in my OEM the status of the listener is still showing UP while it actually is down.

And it puts my OEM In a weird state, and when i check dba_objects a lot of synonyms are in an INVALID status.

Is there something special that anyone knows about this table taht i should know. WHat else can i do? Create a materialized view that refreshes on every commit on this table but that would be expensive.

I tried creating this trigger in the system schema and giving it select etc privileges as needed - DIDN"T WORK.
It is a simple trigger, that is supposed to fire and generate a command via a shell.

I am on 10.2 version.

CREATE OR REPLACE TRIGGER sysman.OEMALERT_TRG_SENDALARM
BEFORE INSERT
ON SYSMAN.MGMT_AVAILABILITY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_NODE VARCHAR2(50); -- node name
V_NAME VARCHAR2(50); -- database/host/listener name
V_TYPE VARCHAR2(20); -- type of failure
V_SEV VARCHAR2(10); -- severity level
V_STATUS VARCHAR2(30); -- event UpDown
UpDowntime VARCHAR2(30); -- timestamp long format
v_SendAlarm VARCHAR2(2000);
V_CODE VARCHAR2(20); -- output, databae, rac or listener
Blackout VARCHAR2(30); -- blackout period 9pm - 7am
V_OPAS_LEVEL NUMBER;
V_ACTIVE_NODE VARCHAR2(1);
BEGIN
-- Don't have to check if databases are in the monitored list because they
-- have been filtered out in the view SYSMAN.OEMALERT_VIEW
V_STATUS := :NEW.CURRENT_STATUS;

UpDowntime := TO_CHAR(SYSDATE,'MON-DD-YYYY HH12:MI:SS AM');
Blackout := TO_CHAR(SYSDATE,'HH24:MI:SS');

IF :NEW.CURRENT_STATUS=0 -- db/listener/host down
THEN
SELECT DISTINCT TARGET_NAME INTO V_NAME FROM SYSMAN.OEMALERT_VIEW
WHERE TARGET_GUID=:NEW.TARGET_GUID;
SELECT DISTINCT TARGET_TYPE INTO V_TYPE FROM SYSMAN.OEMALERT_VIEW
WHERE TARGET_GUID=:NEW.TARGET_GUID;
SELECT DISTINCT HOST_NAME INTO V_NODE FROM SYSMAN.OEMALERT_VIEW
WHERE TARGET_GUID=:NEW.TARGET_GUID;

V_STATUS:='Target Down';

SELECT OPAS_LEVEL , ALERT_ACTIVE INTO v_OPAS_LEVEL, V_ACTIVE_NODE FROM SYSMAN.SENDALARM_NODES
WHERE UPPER(NODE_NAME) = UPPER(V_NODE);

-- Decide type of output, databae, rac, listener or host
CASE
WHEN V_TYPE = 'host' THEN V_CODE:=V_NAME;
WHEN V_TYPE = 'oracle_database' THEN V_CODE:=UPPER(V_NAME);
WHEN V_TYPE = 'rac_database' THEN V_CODE:=UPPER(V_NAME);
WHEN V_TYPE = 'oracle_listener' THEN V_CODE:=UPPER(SUBSTR(V_NAME,1,Cool);
END CASE;
-- blackout for SEV3 between 9PM-7AM
DBMS_OUTPUT.PUT_LINE(BLACKOUT);
IF v_OPAS_LEVEL = 2 AND v_ACTIVE_NODE = 'Y' -- Blackout BETWEEN '07:00:00' AND '21:00:00'
THEN

V_SENDALARM := '/usr/local/bin/sendalarm.bsh -s "MINOR" -p "OEM_DB" -m "'|| V_TYPE || '" -N "'|| V_NODE || '"' ;
INSERT INTO sysman.SEND_ALARM_LOG(target_name,metrics_entry,entry_date,entry_text) VALUES (V_NODE,V_STATUS,SYSDATE, V_SENDALARM);
sysman.shell(V_SENDALARM);
END IF;
END IF;

END;
/
Re: trigger on MGMT_AVAILABILITY table puts OEM in a weird state. [message #248078 is a reply to message #248027] Thu, 28 June 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have copied and pasted the trigger below.

But you should first read and follow How to format your posts
Please always post your Oracle version (4 decimals).

Quote:
DIDN"T WORK

This is not an Oracle error.

Regards
Michel
Re: trigger on MGMT_AVAILABILITY table puts OEM in a weird state. [message #248644 is a reply to message #248027] Fri, 29 June 2007 17:35 Go to previous messageGo to next message
geeklol
Messages: 59
Registered: March 2005
Member
Hi all,

I am really struggling here with this trigger. I copied and pasted teh trigger and it compiles fine. it is just weird how it puts the OEM in a weird state, where it quits communicating witht he host where the listener resiedes.

As soon as i enable the trigger it quits working.

Instead of putting the trigger directly on the table sysman.mgmt_availability, i have created a materialized view on this table in the system schema and put the trigger on this Materialized view, it still does the same thing. Any idea what i can do next or why the trigger freaks out the OEM? Is there anything strange that i am doing with the trigger? any known bugs? Please help!

Thank you bunches!
Re: trigger on MGMT_AVAILABILITY table puts OEM in a weird state. [message #248662 is a reply to message #248644] Sat, 30 June 2007 01:28 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there anything strange that i am doing with the trigger?

Yes, it is unformatted and so unreadable for me.
Moreover bugs depend on version.

You don't answer our questions and requisites
You're On Your Own (YOYO).

Regards
Michel
Previous Topic: DBCA says The current version of CRS is null
Next Topic: track queries executed by the user/applictions
Goto Forum:
  


Current Time: Tue Dec 06 06:44:26 CST 2016

Total time taken to generate the page: 0.06597 seconds