Oracle DBMS_GSM_ALERTS
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Global Workload Management Alerts

This package is for definitions and FUNCTIONs shared by the dbms_gsm_pooladmin and dbms_gsm_cloudadmin packages on the GSM cloud catalog database, and for utility routines used by GSMCTL when administering the cloud.
AUTHID DEFINER
Constants
Name Data Type Value
yellow_level VARCHAR2(7) 'YELLOW'
red_level VARCHAR2(4) 'RED'
cpu_threshold VARCHAR2(4) 'CPU'
disk_threshold VARCHAR2(5) 'DISK'
Data Types -- formatted for readability
SQL> SELECT dbms_metadata.get_ddl('TYPE', 'ALERT_TYPE', 'SYS') FROM dual;

DBMS_METADATA.GET_DDL('TYPE','ALERT_TYPE','SYS')
----------------------------------------------------------------------
atype sys.alert_type;
atype.timestamp_originating := SYSTIMESTAMP;

CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."ALERT_TYPE" AS OBJECT (
timestamp_originating   IN TIMETAMP WITH TIME ZONE,
organization_id         IN VARCHAR2(10),
component_id            IN VARCHAR2(3),
message_id              IN NUMBER,
hosting_client_id       IN VARCHAR2(64),
message_type            IN VARCHAR2(12),
message_group           IN VARCHAR2(30),
message_level           IN NUMBER,
host_id                 IN VARCHAR2(256),
host_nw_addr            IN VARCHAR2(256),
module_id               IN VARCHAR2(64),
process_id              IN VARCHAR2(128),
user_id                 IN VARCHAR2(30),
upstream_component_id   IN VARCHAR2(30),
downstream_component_id IN VARCHAR2(4),
execution_context_id    IN VARCHAR2(128),
error_instance_id       IN VARCHAR2(142),
reason_argument_count   IN NUMBER,
reason_argument_1       IN VARCHAR2(513),
reason_argument_2       IN VARCHAR2(513),
reason_argument_3       IN VARCHAR2(513),
reason_argument_4       IN VARCHAR2(513),
reason_argument_5       IN VARCHAR2(513),
sequence_id             IN NUMBER,
reason_id               IN NUMBER,
object_owner            IN VARCHAR2(30),
object_name             IN VARCHAR2(513),
subobject_name          IN VARCHAR2(30),
object_type             IN VARCHAR2(30),
instance_name           IN VARCHAR2(16),
instance_number         IN NUMBER,
scope                   IN VARCHAR2(10),
advisor_name            IN VARCHAR2(30),
metric_value            IN NUMBER,
suggested_action_msg_id IN NUMBER,
action_argument_count   IN NUMBER,
action_argument_1       IN VARCHAR2(30),
action_argument_2       IN VARCHAR2(30),
action_argument_3       IN VARCHAR2(30),
action_argument_4       IN VARCHAR2(30),
action_argument_5       IN VARCHAR2(30),
pdb_name                IN VARCHAR2(128));

ALTER TYPE "SYS"."ALERT_TYPE" MODIFY ATTRIBUTE object_owner VARCHAR2(128) CASCADE;
Dependencies
ALERT_TYPE DBMS_GSM_CLOUDADMIN DBMS_GSM_POOLADMIN
DBMS_GSM_ALERT_LIB DBMS_GSM_COMMON DBMS_SERVER_ALERT
Documented No
First Available 12.1.0.2
Security Model Owned by GSMADIN_INTERNAL with EXECUTE granted to GDS_CATALOG_SELECT, GSMADMIN_ROLE, GSMUSER_ROLE, GSM_POOLADMIN_ROLE
Source {ORACLE_HOME}/rdbms/admin/dbmsgwmalt.sql
Subprograms
 
GET_CURRENT_CARDINALITY
Returns the current cardinality as mentioned in the alert if it is a cardinality miss GDS alert dbms_gsm_alerts.get_current_cardinality(my_alert IN ALERT_TYPE)  RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_DATABASE_NAME
Returns the database name associated with the alert only if a database is involved in the GDS alert dbms_gsm_alerts.get_database_name(my_alert IN ALERT_TYPE) RETURN VARCHAR2;
See POST_ALERT Demo Below
 
GET_EXPECTED_CARDINALITY
Returns the expected cardinality if it is a cardinality miss GDS alert dbms_gsm_alerts.get_expected_cardinality(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_GDSPOOL_NAME
Returns the GDS pool name associated with the alert only if a GDS pool is involved in the GDS alert dbms_gsm_alerts.get_gdsPool_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_GSM_NAME
Returns the GSM name associated with the alert only if a GSM is involved in the GDS alert dbms_gsm_alerts.get_gsm_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_INSTANCE_NAME
Returns the instance name associated with the alert only if the instance is involved in a GDS alert dbms_gsm_alerts.get_instance_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_REGION_NAME
Returns the region name associated with the alert only if a region is named in the GDS alert dbms_gsm_alerts.get_region_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_RESOURCE_NAME
Returns the resource name (CPU or DISK) associated with the alert only if it is a threshold limit GDS alert dbms_gsm_alerts.get_resource_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_SERVICE_NAME
Returns the service name associated with the alert only if a service is involved in the GDS alert dbms_gsm_alerts.get_service_name(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
GET_THRESH_LEVEL
Returns the thresh level (Yellow, Red) associated with the alert. This will return the threshold level if it is a threshold GDS alert dbms_gsm_alerts.get_thresh_level(my_alert IN ALERT_TYPE) RETURN BINARY_INTEGER;
See POST_ALERT Demo Below
 
POST_ALERT
Posts a GDS alert dbms_gsm_alerts.post_alert(
reason_id     IN dbms_server_alert.reason_id_t,
severity      IN dbms_server_alert.severity_level_t,
object_name   IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL,
database_name IN VARCHAR2 DEFAULT NULL,
pool_name     IN VARCHAR2 DEFAULT NULL,
region_name   IN VARCHAR2 DEFAULT NULL,
resource_name IN VARCHAR2 DEFAULT NULL,
thresh_level  IN VARCHAR2 DEFAULT NULL,
expect_card   IN VARCHAR2 DEFAULT NULL,
current_card  IN VARCHAR2 DEFAULT NULL);
-- This demo is intended to show valid syntax for using the package's objects but does not return a value

DECLARE
 rid     dbms_server_alert.reason_id_t := dbms_server_alert.rsn_slte;
 sev     dbms_server_alert.severity_level_t := dbms_server_alert.level_critical;
 obj     VARCHAR2(30) := 'ZZYZX';
 iname   VARCHAR2(30) := 'PERRITO5';
 dbname  VARCHAR2(30) := 'PERRITODB';
 poolin  VARCHAR2(30) := 'MLIBPOOL';
 regname VARCHAR2(30) := 'TAHITI';
 resname VARCHAR2(4)  := gsmadmin_internal.dbms_gsm_alerts.cpu_threshold;
 tlevel  VARCHAR2(6)  := gsmadmin_internal.dbms_gsm_alerts.red_level;
 excard  VARCHAR2(2)  := '2';
 cucard  VARCHAR2(2)  := '1';
BEGIN
  gsmadmin_internal.dbms_gsm_alerts.post_alert(rid, sev, obj, iname, dbname, poolin,
                                               regname, resname, tlevel, excard, cucard);
END;
/

DECLARE
 atype sys.alert_type := sys.alert_type(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
 retVal VARCHAR2(30);
BEGIN
  atype.reason_id := dbms_server_alert.rsn_slte;
  retVal := gsmadmin_internal.dbms_gsm_alerts.get_region_name(atype);
  dbms_output.put_line(retVal);
END;
/
 
POST_CARD_OFF
Post a GDS alert : Service cardinality is off

Overload 1
dbms_gsm_alerts.post_card_off(
service_name IN VARCHAR2,
pool_name    IN VARCHAR2,
exp_card     IN NUMBER,
curr_card    IN NUMBER);
See POST_INSTANCE_DOWN Demo Below
Overload 2 dbms_gsm_alerts.post_card_off(
service_name IN VARCHAR2,
pool_name    IN VARCHAR2,
region_name  IN VARCHAR2 DEFAULT NULL);
See POST_INSTANCE_DOWN Demo Below
 
POST_CATALOG_DOWN
Post a GDS alert : The catalog is down or not reachable dbms_gsm_alerts.post_catalog_down(database_name IN VARCHAR2);
See POST_INSTANCE_DOWN Demo Below
 
POST_DATABASE_DOWN
Posts a GDS alert : database down or not reachable dbms_gsm_alerts.post_database_down(
database_name IN VARCHAR2,
pool_name     IN VARCHAR2 DEFAULT NULL,
region_name   IN VARCHAR2 DEFAULT NULL);
exec gsmadmin_internal.dbms_gsm_alerts.post_database_down('PERRITO42');
 
POST_DATABASE_LAGGING
Post a GDS alert : The replcated database is lagging for a given service dbms_gsm_alerts.post_database_lagging(
service_name  IN VARCHAR2,
pool_name     IN VARCHAR2,
database_name IN VARCHAR2,
region_name   IN VARCHAR2);
See POST_INSTANCE_DOWN Demo Below
 
POST_GSM_DOWN
Post a GDS alert : gsm down dbms_gsm_alerts.post_gsm_down(
gname IN VARCHAR2,                -- gsm name
rname IN VARCHAR2 DEFAULT NULL);  -- region name
See POST_INSTANCE_DOWN Demo Below
 
POST_INSTANCE_DOWN
Post a GDS alert : instance down or not reachable dbms_gsm_alerts.post_instance_down(
instance_name IN VARCHAR2,
pool_name     IN VARCHAR2 DEFAULT NULL,
region_name   IN VARCHAR2 DEFAULT NULL);
exec gsmadmin_internal.dbms_gsm_alerts.post_instance_down('PERRITO42');
 
POST_THRESHOLD_HIT
Posts a CPU or a DISK threshold has been hit dbms_gsm_alerts.post_threshold_hit(
instance_name IN VARCHAR2,
pool_name     IN VARCHAR2,
region_name   IN VARCHAR2,
resource_name IN VARCHAR2,
thresh_level  IN VARCHAR2);
See POST_INSTANCE_DOWN Demo Above

Related Topics
Built-in Functions
Built-in Packages
DBMS_GSM_CLOUDADMIN
DBMS_GSM_COMMON
DBMS_GSM_DBADMIN
DBMS_GSM_FIX
DBMS_GSM_FIXED
DBMS_GSM_POOLADMIN
DBMS_GSM_UTILITY
DBMS_GSM_XDB
DBMS_SERVER_ALERT
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved