Oracle DBMS_GSM_ALERTS
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.
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