Cloud Control SMS alerts

-
DB Version - Cloud control 12c Release 5

Setting up alerts is a must for any monitoring tool and when it comes to Oracle cloud control email is the only straight forward configuration. There is a possibility to configure sms by using mail to sms service but for most shops this is bit unpractical. Mainly due to security reasons to use a public service (e.g Google) to convert email to sms.

But most shops does have their own sms gateways where they get data from a database or files and sent it via sms. In our case we did have a sms gateway so the challenge was to forward sms alerts to sms gateway source database tables.

Below is the basic configuration setup.



All the alerts generated by cloud control are stored in the cloud control database. What we need to do is transfer those alert data from cloud control db to sms gateway db.

Some digging in the cloud control documentation and googling helped us to come up with below steps to accomplish this.

In summary there will be six steps.

Step 1,2 -- Extract data from cloud control repository and store in new table (sysman.event_log)
Step 3,4 -- Convert extracted data to sms ready format and store in new table (sysman.sms_table)
Step 5,6 -- Send data to sms gateway database.

Step 1 - Create table sysman.event_log to store extracted alert events.
  CREATE TABLE "SYSMAN"."EVENT_LOG"  
   (  "NOTIFICATION_TYPE" VARCHAR2(32),  
     "REPEAT_COUNT" NUMBER,  
     "RULESET_NAME" VARCHAR2(256),  
     "RULE_OWNER" VARCHAR2(256),  
     "RULE_NAME" VARCHAR2(256),  
     "MESSAGE" VARCHAR2(4000),  
     "MESSAGE_URL" VARCHAR2(4000),  
     "EVENT_INSTANCE_GUID" RAW(16),  
     "EVENT_TYPE" VARCHAR2(20),  
     "EVENT_NAME" VARCHAR2(512),  
     "EVENT_MSG" VARCHAR2(4000),  
     "CATEGORIES" VARCHAR2(4000),  
     "SOURCE_OBJ_TYPE" VARCHAR2(120),  
     "SOURCE_OBJ_NAME" VARCHAR2(256),  
     "SOURCE_OBJ_URL" VARCHAR2(4000),  
     "SEVERITY" VARCHAR2(128),  
     "SEVERITY_CODE" VARCHAR2(32),  
     "TARGET_NAME" VARCHAR2(256),  
     "TARGET_TYPE" VARCHAR2(128),  
     "TARGET_URL" VARCHAR2(4000),  
     "HOST_NAME" VARCHAR2(256),  
     "TIMEZONE" VARCHAR2(64),  
     "OCCURED" DATE,  
     "CA_GUID" RAW(16),  
     "CA_NAME" VARCHAR2(128),  
     "CA_OWNER" VARCHAR2(256),  
     "CA_TYPE" VARCHAR2(256),  
     "CA_STATUS" VARCHAR2(64),  
     "CA_STATUS_CODE" NUMBER,  
     "CA_JOB_STEP_OUTPUT" VARCHAR2(4000),  
     "CA_EXECUTION_GUID" RAW(16),  
     "CA_STAGE_CHANGE_GUID" RAW(16)  
   ) SEGMENT CREATION IMMEDIATE  
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  
  NOCOMPRESS LOGGING  
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  
  TABLESPACE "MGMT_TABLESPACE"

Step 2 - Create procedure sysman.log_events to insert new alert event data on event_log table. Get alert event data using gc$notif_event_msg. (If you want collect data on incidents use gc$notif_incident_msg)
 PROCEDURE log_event(s IN GC$NOTIF_EVENT_MSG)  
 IS  
   l_categories gc$category_string_array;  
   l_ca_obj gc$notif_corrective_action_job;  
   l_categories_new VARCHAR2(1000);  
 BEGIN  
   -- save event categories  
   l_categories := s.event_payload.categories;  
     IF l_categories IS NOT NULL  
   THEN  
    FOR c IN 1..l_categories.COUNT  
    LOOP  
     l_categories_new := (l_categories_new|| c || ' - ' || l_categories(c)||',');  
    END LOOP;  
   END IF;  
   -- save event message  
   IF s.msg_info.notification_type = 'NOTIF_CA' AND s.event_payload.corrective_action IS NOT NULL  
   THEN  
    l_ca_obj := s.event_payload.corrective_action;  
    INSERT INTO event_log (notification_type, repeat_count, ruleset_name, rule_name, rule_owner, message, message_url, event_instance_guid, event_type, event_name, event_msg, categories, source_obj_type, source_obj_name, source_obj_url, severity, severity_code, target_name, target_type, target_url, host_name, timezone, occured, ca_guid, ca_name, ca_owner, ca_type, ca_status, ca_status_code, ca_j  
 ob_step_output, ca_execution_guid, ca_stage_change_guid)  
    VALUES (s.msg_info.notification_type, s.msg_info.repeat_count, s.msg_info.ruleset_name, s.msg_info.rule_name,s.msg_info.rule_owner, s.msg_info.message, s.msg_info.message_url, s.event_payload.event_instance_guid, s.event_payload.event_type, s.event_payload.event_name, s.event_payload.event_msg, l_categories_new, s.event_payload.source.source_type, s.event_payload.source.source_name, s.event_  
 payload.source.source_url, s.event_payload.severity, s.event_payload.severity_code, s.event_payload.target.target_name, s.event_payload.target.target_type, s.event_payload.target.target_url, s.event_payload.target.host_name, s.event_payload.target.target_timezone, s.event_payload.occurrence_date, l_ca_obj.JOB_GUID, l_ca_obj.JOB_NAME, l_ca_obj.JOB_OWNER, l_ca_obj.JOB_TYPE, l_ca_obj.JOB_STATUS, l_ca  
 _obj.JOB_STATUS_CODE, l_ca_obj.JOB_STEP_OUTPUT, l_ca_obj.JOB_EXECUTION_GUID, l_ca_obj.JOB_STATE_CHANGE_GUID);  ELSE  
    INSERT INTO event_log (notification_type, repeat_count, ruleset_name, rule_name, rule_owner, message, message_url, event_instance_guid, event_type, event_name, event_msg, categories, source_obj_type, source_obj_name, source_obj_url, severity, severity_code, target_name, target_type, target_url, host_name, timezone, occured, ca_guid, ca_name, ca_owner, ca_type, ca_status, ca_status_code, ca_j  
 ob_step_output, ca_execution_guid, ca_stage_change_guid)  
    VALUES (s.msg_info.notification_type, s.msg_info.repeat_count, s.msg_info.ruleset_name, s.msg_info.rule_name, s.msg_info.rule_owner, s.msg_info.message, s.msg_info.message_url, s.event_payload.event_instance_guid, s.event_payload.event_type, s.event_payload.event_name, s.event_payload.event_msg, l_categories_new, s.event_payload.source.source_type, s.event_payload.source.source_name, s.event  
 _payload.source.source_url, s.event_payload.severity, s.event_payload.severity_code, s.event_payload.target.target_name, s.event_payload.target.target_type, s.event_payload.target.target_url, s.event_payload.target.host_name, s.event_payload.target.target_timezone, s.event_payload.occurrence_date, null,null,null,null,null,null,null,null,null);  
   END IF;  
   COMMIT;  
 END log_event;

Step 3 - Create new table sysman.sms_table to get data which is to be included in sms in correct format
 CREATE TABLE "SYSMAN"."SMS_TABLE"  
   (  "REFERENCE_TYPE" VARCHAR2(32),  
     "REFERENCE_TEXT" VARCHAR2(32),  
     "TXT_MESSAGE" VARCHAR2(256),  
     "SMS_STATUS" VARCHAR2(256),  
     "DATE_LST_UPDATED" DATE,  
     "MOBILE_NO" VARCHAR2(256),  
     "ALERT_ORIGIN_DATETIME" DATE  
   ) SEGMENT CREATION IMMEDIATE  
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  
  NOCOMPRESS LOGGING  
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  
  TABLESPACE "MGMT_TABLESPACE"

Step 4 - Create trigger sysman.insert_sms to populate sysman.event_log table data to sms_table in correct format to support the sms gateway destination table. (This will change depending on your sms source table format)
 TRIGGER insert_sms  
 AFTER INSERT  
   ON event_log  
   FOR EACH ROW  
 DECLARE  
   v_username varchar2(10);  
 BEGIN  
   SELECT user INTO v_username FROM dual;  
   INSERT INTO sms_table  
   ( reference_type,  
    reference_text,  
    txt_message,  
    sms_status,  
    date_lst_updated,  
    mobile_no,  
    alert_origin_datetime  
   )  
   VALUES  
   ( 'DBA',  
    'DBA-Alert',  
    :new.message,  
    'TXIN',  
    sysdate,  
    0779999999,  
    sysdate  
  );  
 END;
Now we have notification data which we can send to sms gateway source remote database.

Step 5 - Create procedure sysman.sms_proc to send data from sysman.sms_table to remote database.
 PROCEDURE sms_proc  
 is  
 BEGIN  
   FOR rec IN (SELECT txt_message FROM sms_table where txt_message not like '%distinct types of ORA- errors have been found in the alert log%')  
   LOOP  
 ---- Below insert is based on our SMS gateway source table format.  
      INSERT INTO mob_bank.mob_direct_alerts@alertdbank (srno,  
                          domainid,  
                          referance_type,  
                          referance_txt,  
                          txt_message,  
                          mobile_no,  
                          sms_status,  
                          date_lst_updated,  
                          alert_origin_datetime)  
     VALUES (mob_bank.get_nwua_seq@alertdbank,  
    'NWUA',  
    'DBA',  
    'DBA-Alert',  
    rec.txt_message,  
    '9477999999',  
    'TXIN',  
    sysdate,  
    sysdate  
  );

Step 6 - Schedule above procedure to send data using job sysman.sms_sender in our case it was every 3 minutes.
 BEGIN  
 dbms_scheduler.create_job('"SMS_SENDER"',  
 job_type=>'STORED_PROCEDURE',   
 job_action=>'sysman.sms_proc',   
 number_of_arguments=>0,  
 start_date=>TO_TIMESTAMP_TZ('25-AUG-2017 11.56.02.375828000 AM +05:30','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),   
 repeat_interval=>'FREQ=MINUTELY;INTERVAL=3',   
 end_date=>NULL,  
 job_class=>'"DEFAULT_JOB_CLASS"',   
 enabled=>FALSE,   
 auto_drop=>TRUE,  
 comments=>NULL  
 );  
 dbms_scheduler.enable('"SMS_SENDER"');  
 COMMIT;  
 END;
This is much secure and easy alternative where in-house sms sending capability is used for cloud control alerts.




Comments

Post a Comment

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

-
Image
ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...
Read more

Oracle Multitenant - Create new service for PDB using DBMS_SERVICE

-
Image
When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...
Read more

Wait for unread message on broadcast channel - Blocking Sessions

-
Image
What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...
Read more