Oracle 18cR3 DBMS_GSM

Oracle DBMS_GSM_ALERTS Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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