Oracle | DBMS_GSM

Oracle DBMS_GSM_ALERTS Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

Security Model Owned by GSMADIN_INTERNAL with EXECUTE granted to GDS_CATALOG_SELECT, GSMADMIN_ROLE, GSM_POOLADMIN_ROLE and GSMUSER_ROLE

Source {ORACLE_HOME}/rdbms/admin/dbmsgwmalt.sql

Subprograms

GET_CURRENT_CARDINALITY	GET_REGION_NAME	POST_CATALOG_DOWN
GET_DATABASE_NAME	GET_RESOURCE_NAME	POST_DATABASE_DOWN
GET_EXPECTED_CARDINALITY	GET_SERVICE_NAME	POST_DATABASE_LAGGING
GET_GDSPOOL_NAME	GET_THRESH_LEVEL	POST_GSM_DOWN
GET_GSM_NAME	POST_ALERT	POST_INSTANCE_DOWN
GET_INSTANCE_NAME	POST_CARD_OFF	POST_THRESHOLD_HIT

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);