Home » RDBMS Server » Server Administration » Extract Messages from??
Extract Messages from?? [message #257570] Wed, 08 August 2007 12:09 Go to next message
geeklol
Messages: 59
Registered: March 2005
Member
Setup 10g, Linux.

I am currently working on a project to send out alerts and our exisiting alert system has a message column embedded in it, which gets populated with messages like,

Target Name=INIP_INIP1
Target Type=Database Instance
Host=csinitiatedb1
Metric=Status
Metric Value=0
Timestamp=Aug 8, 2007 2:40:59 AM PDT
Severity=Critical
Message=Failed to connect to database instance: ORA-12528: TNS:listener: all appropriate instances are blocking new connections (DBD ERROR: OCIServerAttach).Notification Rule Name=Database Availability and Critical States
Notification Rule Owner=SYSMAN.

A couple of other examples of the messages are,
Message=CPU I/O Wait is 99.52%, crossed warning (40) or critical (80) threshold.

OR
The listener is down: TNS-12541: TNS:no listener .OR
Message=Agent is Unreachable (REASON = IOException in sending Request :: Connection refused) but the host is UP.

Does anyone know, WHICH TABLE IN ORACLE STORES THESE MESSAGES?

Thank you bunches in advance!!
Re: Extract Messages from?? [message #257572 is a reply to message #257570] Wed, 08 August 2007 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Error messages are not in tables but in files.
You can access them with sqlerrm function.
SQL> exec dbms_output.put_line(sqlerrm(-12528));
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

PL/SQL procedure successfully completed.

Regards
Michel
Re: Extract Messages from?? [message #257580 is a reply to message #257572] Wed, 08 August 2007 12:32 Go to previous messageGo to next message
geeklol
Messages: 59
Registered: March 2005
Member
If the error messages are in a function, how do i embed it in my trigger to cpature the message without the input paramter (i.e. the error msg number)

Here's my trigger, whre teh part in orange makes a call to one of our external ticket creating systems. ANd i am also attaching the alerts that are geenrated right now along with this post. Please help me!!

TRIGGER
BEFORE INSERT
ON SYSTEM.MYVW_SENDALARM
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) like 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,8));
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);

INSERT INTO SYSMAN.SENDALARM_DEBUG VALUES(SYSDATE,V_SENDALARM,'V_SENDALARM');
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
WHEN CASE_NOT_FOUND then
DBMS_OUTPUT.PUT_LINE('case not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER ERRORS');
END;
/

ALERT GENERATED CURRENTLY: BUT INSTEAD OF GETTING THE HARDCODED oracle_listener, I WANT TO EMBED A REAL MESSAGE.
Incident Id : R2:000006935672
Severity : Three
Acknowledged? : No
Status : Open
Notification Text : CSLXIMGT1:oracle_listener
Account Affected + : Cedars-Sinai Medical Center
Affected Item + : CSLXIMGT1:OEM_DB
Assignee Group + : LADC-Automation
Assignee Individual : Donald Montes
Assignee Phone :
Customer Name + : ESM User
Customer Phone : 972-577-5090
Customer Email Address : DL-ProcessManagement@ps.net
Location + : Plano Technology Center
Office :
Incident Description : oracle_listener
Resolution Summary :
Work Log : 7/2/2007 3:33:18 PM ESM User
ESM Event generated Incident Record
*** Remedy was unable to locate a CMDB:Configured Item record for Host Name =
CSLXIMGT1.
Re: Extract Messages from?? [message #257585 is a reply to message #257580] Wed, 08 August 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure (using preview button) that lines of code do not exceed 80 or 100 characters when you format.

If you don't have an error number which message do you want to display?

Regards
Michel

[Updated on: Wed, 08 August 2007 13:29]

Report message to a moderator

Re: Extract Messages from?? [message #257590 is a reply to message #257570] Wed, 08 August 2007 13:11 Go to previous messageGo to next message
geeklol
Messages: 59
Registered: March 2005
Member
**SORRY, HOPE THIS HELPS!! THANK YOU FOR ALL YOUR HELP**
If the error messages are in a function, how do i embed it in my trigger to cpature the message without the input paramter (i.e. the error msg number)

Here's my trigger, whre teh part in orange makes a call to one of our external ticket creating systems. ANd i am also attaching the alerts that are geenrated right now along with this post. Please help me!!

TRIGGER
CREATE OR REPLACE TRIGGER SYSTEM.TRG_SENDALARM
BEFORE INSERT
ON SYSTEM.MYVW_SENDALARM 
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) like 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,8));
            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); 
            INSERT INTO SYSMAN.SENDALARM_DEBUG 
               VALUES(SYSDATE,V_SENDALARM,'V_SENDALARM');
           END IF;
    END IF;
EXCEPTION
       WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('no_data_found');
       WHEN CASE_NOT_FOUND then
         DBMS_OUTPUT.PUT_LINE('case not found'); 
       WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('OTHER ERRORS');
END;
/


ALERT GENERATED CURRENTLY: BUT INSTEAD OF GETTING THE HARDCODED oracle_listener, I WANT TO EMBED A REAL MESSAGE.
Incident Id : R2:000006935672
Severity : Three
Acknowledged? : No
Status : Open
Notification Text : CSLXIMGT1:oracle_listener
Account Affected + : Cedars-Sinai Medical Center
Affected Item + : CSLXIMGT1:OEM_DB
Assignee Group + : LADC-Automation
Assignee Individual : Donald Montes
Assignee Phone :
Customer Name + : ESM User
Customer Phone : 972-577-5090
Customer Email Address : DL-ProcessManagement@ps.net
Location + : Plano Technology Center
Office :
Incident Description : oracle_listener
Resolution Summary :
Work Log : 7/2/2007 3:33:18 PM ESM User
ESM Event generated Incident Record
*** Remedy was unable to locate a CMDB:Configured Item record for Host Name =
CSLXIMGT1.

[Updated on: Wed, 08 August 2007 23:07] by Moderator

Report message to a moderator

Re: Extract Messages from?? [message #257599 is a reply to message #257590] Wed, 08 August 2007 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My answer is still the same:
Quote:
If you don't have an error number which message do you want to display?

Regards
Michel
Re: Extract Messages from?? [message #257638 is a reply to message #257599] Wed, 08 August 2007 19:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This sounds like a flawed solution to me. If the database housing the trigger is down, you won't get any messages. Additionally - there are too many "moving parts" your script can fail and messages will stop. It also looks like you have no queuing mechanism, so when you get 1000 events in a min - you call sysman.shell 1000 times and if that's synchronus then your 1000 inserts into TRG_SENDALARM are going to take an awfully long time. If sysman.shell is async, it's unlikely that you'll be able to spawn 1000 concurrent background processes to run your commands.

I'd do a lot of reading on asktom if I were you (especially since "Cedars-Sinai Medical Center" is involved) - starting here...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805

Also - you can never just trap for errors like this...
WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('OTHER ERRORS');
Previous Topic: OLAP Oracle 10G Parameters
Next Topic: slave TJ process wait
Goto Forum:
  


Current Time: Wed Dec 07 10:32:52 CST 2016

Total time taken to generate the page: 0.11312 seconds