| trigger on MGMT_AVAILABILITY table puts OEM in a weird state. [message #248027] |
Wed, 27 June 2007 17:12  |
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, );
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 #248644 is a reply to message #248027] |
Fri, 29 June 2007 17:35  |
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!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
