We’ve been asked to generate automatic database incidents in our service desk (CA Unicenter).
And through the Grid Control it’s a piece of cake.
The example below is generic. If you need the exact syntax to send to your CA unicenter server, send me an email at pierre.roussin@gmail.com.
For more flexibility, we created 2 procedures, one to get the notification from the console mechanism and one to build the message and email it to the recipient (service desk in our case).
Creating the incident
This procedure can be customized to send an email to the dba accounts
create or replace procedure sysman.create_incident(s1 in varchar2,s2 in varchar2, s3 in varchar2)
IS
mailhost VARCHAR2(30) := ’smtp server’;
mail_conn utl_smtp.connection;
msg1 varchar2(2000);
sender varchar2(30):=’address of sender‘;
recipient varchar2(30):=’destination of email‘;
subject varchar2(40):=‘customized subject’;
finalstr varchar2(8000):=null; /* Body of the email */
/* Wrapper function to write data to the mail server */
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(mail_conn, name || ‘: ‘ || header || utl_tcp.CRLF);
END;
BEGIN
/* in the variable finalstr you can customized your notification email */
finalstr:= ‘customized text‘ || utl_tcp.CRLF;
…
mail_conn := utl_smtp.open_connection(mailhost);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn,sender); — sender
utl_smtp.rcpt(mail_conn,recipient); — recipient
utl_smtp.open_data(mail_conn);
send_header(‘From’, sender || ‘<’ || sender || ‘>’);
send_header(‘To’, ‘”Recipient” <’ || recipient || ‘>’);
send_header(‘Subject’, subject);
utl_smtp.write_data(mail_conn, finalstr);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Send Mail Error: ‘ || substr(sqlerrm,1,100));
END;
Getting the console notification
Notice that we only want to be notified by critical alerts. consult the Oracle documentation for other status.
create or replace procedure sysman.get_notification(severity IN MGMT_NOTIFY_SEVERITY)
IS
BEGIN
IF severity.severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL
THEN
BEGIN
/* severity object attributes are passed to the procedure */
create_incident(severity.target_name,severity.message,severity.collection_timestamp);
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000, ‘Procedure create_incident ended in error’);
END;
COMMIT;
END IF;
END;
Enabling the procedure in the grid control
- Navigate to setup - Notification Methods - Scripts and snmp traps – Add pl/sql procedure – Go
- Enter the name, description and the fully qualified name of the procedure (ex:sysman.get_notification)
- Navigate to Preferences – Notifications – Rules – Assign Methods to Multiple rules
- Choose a rule name (database availability and critical states in this case), check the box of your custom method
Pierre
