Rem drv: Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/rac/rac_pkgdefs.sql /st_emdbsa_11.2/18 2010/05/25 00:54:03 rsamaved Exp $ Rem Rem rac_pkgdefs.sql Rem Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem rac_pkgdefs.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rsamaved 05/22/10 - add procedure to get db type and config type Rem prjaiswa 04/14/10 - def for get_is_rac_listener Rem pardutta 11/08/09 - RAC One Node: Add get_database_type Rem shasingh 04/17/09 - added host add callback for clearing add node Rem alert Rem ysun 03/09/09 - add cell related alerts Rem manaraya 03/05/09 - Bug 7931482 Rem ajdsouza 02/17/09 - added subs to get resourcename and is has managed Rem ajdsouza 02/17/09 - added proc mgmt_rac.handle_cluster_tgttype_add, bug fix#7160826 Rem yemeng 12/24/08 - add key Sql Profile attributes. Rem shasingh 11/10/08 - modified proc for support of error handling of Rem reconfig activities Rem shasingh 09/24/08 - removed redundant procedure def Rem ajdsouza 11/01/08 - add functions for resource status Rem sadattaw 05/16/08 - merge from 11gc Rem shasingh 05/14/08 - added large string datatype Rem dchakumk 03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from Rem st_emdbsa_11.1 Rem shasingh 12/16/07 - added procedure for logging info Rem sadattaw 11/26/07 - adding get_services_for_cluster query Rem shasingh 08/09/07 - XbranchMerge Rem shasingh_11g_19509a_targets_discovery from main Rem ajdsouza 02/08/07 - bug fix 5647975,5848019 Rem rsamaved 01/30/07 - change alert rec to list of values in problem Rem service result set Rem kramarat 01/25/07 - XbranchMerge kramarat_cluster_db_services_fix2 Rem from main Rem kramarat 01/12/07 - XbranchMerge kramarat_cluster_db_services_fix Rem from main Rem sadattaw 01/04/07 - adding cluster services queries Rem sadattaw 01/04/07 - adding cluster services queries Rem shasingh 10/11/06 - Rem ajdsouza 08/23/06 - fixed bug 5087180 Rem ysun 08/08/05 - add new API Rem chyu 06/28/05 - New repmgr header impl Rem ysun 02/25/05 - add getStorageInfo Rem ysun 01/06/05 - add all alerts api Rem ysun 11/23/04 - add topology support Rem ysun 10/28/04 - procedures for rollup metrics Rem ysun 11/14/03 - add timezone info Rem ysun 10/15/03 - add call back Rem ysun 09/29/03 - add credentials support Rem streddy 09/25/03 - Always add r/s metric Rem ysun 09/25/03 - pass out by date Rem ysun 09/18/03 - Rem ysun 08/27/03 - updateRacTargets Rem ysun 08/06/03 - add meta version Rem ysun 06/18/03 - add dyn property Rem xuliu 05/12/03 - all targets in cluster Rem ysun 04/14/03 - availability Rem xuliu 04/09/03 - listener alerts Rem ysun 10/30/02 - clean up Rem rpinnama 09/25/02 - Remove hardcoded references of mgmt_rep Rem ysun 09/05/02 - add cluster db list Rem ysun 08/28/02 - update rac Packages Rem ysun 06/26/02 - ysun_rac_support_2 Rem ysun 06/24/02 - Created Rem CREATE OR REPLACE TYPE METRIC_COLUMN_ARRAY AS VARRAY(20) OF VARCHAR2(64); / CREATE OR REPLACE PACKAGE EMD_RAC as --Package level Type Definition TYPE CURSOR_TYPE IS REF CURSOR; TYPE CHAR_ARR_TYPE IS TABLE OF VARCHAR2(64); TYPE long_string_array IS TABLE OF VARCHAR2(4000); -- Constants for RAC Package UPDATE_RAC_JOB_TYPE CONSTANT VARCHAR2(16) := 'UpdateRacTargets'; UPDATE_RAC_JOB_DESCRIPTION CONSTANT VARCHAR2(100) := 'Update Rac Targets Info on the agent'; PROCEDURE get_overview_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name OUT VARCHAR2, last_updated_time OUT DATE, current_status OUT INTEGER, avail_pct OUT NUMBER, time_zone OUT VARCHAR2, target_guid OUT VARCHAR2); PROCEDURE get_db_overview_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name OUT VARCHAR2, last_updated_time OUT DATE, current_status OUT INTEGER, avail_pct OUT NUMBER, db_home OUT VARCHAR2, cluster_name OUT VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, service_name OUT VARCHAR2, archive_mode OUT VARCHAR2, tbsp_severity OUT NUMBER, tbsp_count OUT NUMBER, time_zone OUT VARCHAR2, target_guid OUT VARCHAR2, auto_impl_enable OUT VARCHAR2, key_profile_enable OUT VARCHAR2, key_profile_count OUT NUMBER); procedure get_database_type( target_name in varchar2, db_type out varchar2); procedure get_db_config_type( p_target_name in varchar2, p_db_config_type out varchar2); procedure getStorageInfo(targetName IN VARCHAR2, targetType IN VARCHAR2, tbspCount OUT NUMBER, tbspSeverity OUT NUMBER, stgPerfCount OUT NUMBER, stgPerfSeverity OUT NUMBER); procedure get_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE); PROCEDURE get_all_alerts_in_cluster( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE); PROCEDURE get_listener_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE); PROCEDURE get_cell_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE); PROCEDURE get_cluster_db_list( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, list_type_in IN VARCHAR2, target_summary_out OUT CURSOR_TYPE); PROCEDURE get_cluster_topo_targets( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_summary_out OUT CURSOR_TYPE); procedure get_db_dyn_properties( target_guid_in IN VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, archive_mode OUT VARCHAR2); procedure get_db_dyn_properties( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, archive_mode OUT VARCHAR2); procedure get_db_properties( target_guid_in IN VARCHAR2, oracle_home OUT VARCHAR2, cluster_name OUT VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, service_name OUT VARCHAR2, archive_mode OUT VARCHAR2); procedure get_dbname( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, db_name OUT VARCHAR2, db_domain OUT VARCHAR2); procedure get_tbsp_severity( targetGuid IN VARCHAR2, tbspSeverity OUT NUMBER, tbspCount OUT NUMBER); procedure get_key_profile( targetGuid IN VARCHAR2, autoImplEnable OUT VARCHAR2, keyProfileEnable OUT VARCHAR2, keyProfileCount OUT NUMBER); procedure rollup_or_eval_proc(v_target_guid IN RAW, v_metric_guid IN RAW, v_dependency_list IN MGMT_METRIC_DETAILS_ARRAY, v_sev_code OUT NUMBER, v_message OUT VARCHAR2, v_message_nlsid OUT VARCHAR2, v_message_params OUT VARCHAR2); procedure register_availability ( targetType IN VARCHAR2, metricName IN VARCHAR2, metricColumn IN VARCHAR2, memTargetType IN VARCHAR2, metaVer IN VARCHAR2, endMetaVersion IN VARCHAR2 DEFAULT NULL); PROCEDURE getAllTargetsInCluster( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, target_summary_out OUT CURSOR_TYPE); -- update the target properties in the repository -- the passed in the property list will be updated in the repository for target p_target_name with type -- p_target_type PROCEDURE pre_update_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN MGMT_TARGET_PROPERTY_LIST, p_credentials IN MGMT_TARGET_CRED_ARRAY ); -- Update only on one agent -- p_target_name: the member target name -- p_target_type: the member target type -- p_update_instance: if the member info needs to be updated PROCEDURE update_one_rac_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN SMP_EMD_NVPAIR_ARRAY, p_update_instance IN INTEGER DEFAULT 0 ); -- PROCEDURE: update_rac_target -- -- PURPOSE: -- update the specified target to the Agents. Since RAC_DATABASE is a multiple agent target, -- the properties will be retrieved from the repoistory and updated on multiple agents. -- -- IN Parameters: -- p_target_name: The rac target name -- p_target_type: The rac target type -- p_update_intance: decide if we want to update instance members -- OUT Parameters: -- NONE -- PROCEDURE update_rac_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN SMP_EMD_NVPAIR_ARRAY, p_update_instance IN INTEGER DEFAULT 0 ); -- Procedure ADD_RAC_CALLBACK -- -- PURPOSE: The proc called when the rac_database target is added. -- IN parameters: -- target_name_in IN The target name -- target_type_in IN The target type -- OUT parameters: PROCEDURE add_rac_callback ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW ); -- Procedure update_master_properties -- -- PURPOSE: The proc called when the master EMD switches -- IN parameters: -- target_name_in IN The target name -- target_type_in IN The target type -- OUT parameters: PROCEDURE update_master_properties ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, master_agent_url_in IN VARCHAR2 ); -- Function racdb_interconnects -- -- PURPOSE: this function is called for populating the view -- MGMT$RACDB_INTERCONNECTS -- IN parameters: -- cluster target_name IN The p_name -- OUT parameters: -- rac interconnects as mgmt_rac_ic_table -- FUNCTION racdb_interconnects ( p_name IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_racdb_icstat_table; -- Function cls_interconnects -- -- PURPOSE: this function is called for populating view -- MGMT$CLUSTER_INTERCONNECTS -- -- IN parameters: -- cluster_name IN The p_cname -- OUT parameters: -- cluster interconnects as mgmt_cls_ic_table -- FUNCTION cls_interconnects ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_cls_ic_table; -- Procedure get_service_performance_info -- -- PURPOSE: this function is called for getting -- Cluster managed services Performance information -- This queries mgmt_current_snapshots table to get latest -- metric information for service_performance metric fo the -- given rac_database -- -- IN parameters: -- target_name_in IN rac_database_name -- OUT parameters: -- service_info_csr as cursor of -- following (target_name, service_name, collection_time, -- avg_response_time, % cpuload, service_status, running_instance_list, -- min_response_time, min_response_inst, max_response_time, max_response_inst) -- PROCEDURE get_service_performance_info( target_name_in IN VARCHAR2, service_info_csr OUT CURSOR_TYPE); -- Procedure get_services_for_cluster -- -- PURPOSE: this function is called for getting -- Cluster managed services Config information -- This queries mgmt_rac_services table to get latest -- metric information for mgmt_rac_services metric for all the -- rac_database in the given cluster -- -- IN parameters: -- cluster_name_in IN for the rac_database_name -- OUT parameters: -- services_csr as cursor of -- following (database_unique_name, service_name, service_type, -- enabled, tafpolicy, preferred_instance_list, -- available_instance_list, running_instance_list, -- collection_time ) -- PROCEDURE get_services_for_cluster( cluster_name_in IN VARCHAR2, services_csr OUT CURSOR_TYPE); -- Procedure get_services_for_db -- -- PURPOSE: this function is called for getting -- Cluster managed services Config information -- This queries mgmt_rac_services table to get latest -- metric information for mgmt_rac_services metric for the -- given rac_database -- -- IN parameters: -- target_name_in IN rac_database_name -- cluster_name_in IN for the rac_database_name -- OUT parameters: -- services_csr as cursor of -- following (service_name, service_type, -- enabled, tafpolicy, preferred_instance_list, -- available_instance_list, running_instance_list, -- collection_time ) -- PROCEDURE get_services_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, services_csr OUT CURSOR_TYPE); -- Procedure string_tokenizer -- -- PURPOSE: this function parses the input string into -- tokens using the pattern as a separator -- for e.g. inst1,inst2,inst3,.... -- will be parsed into inst1 inst2 inst3 etc and array -- containing the tokens is returned -- -- IN parameters: -- sPattern IN VARCHAR2 IN the separator -- sBuffer IN VARCHAR2 IN the input string -- OUT parameters: -- sResult OUT CHAR_ARR_TYPE array of tokens -- PROCEDURE string_tokenizer ( sPattern in VARCHAR2, sBuffer in VARCHAR2, sResult OUT CHAR_ARR_TYPE); -- Procedure get_problem_services_for_db -- -- PURPOSE: this function is called for getting -- Problem Cluster managed services information -- for given rac_database -- A service is defined as problem service if either of following -- 1. Service status is Down -- 2. running instance list does not match the preferred instance list -- 3. Service has Critical Alerts defined against Thresholds for that service -- -- IN parameters: -- target_name_in IN rac_database_name -- OUT parameters: -- l_problem_srv_array OUT MGMT_PROBLEM_SERV_ARRAY_TYPE) as list of -- MGMT_PROBLEM_SERV_INFO_TYPE records -- PROCEDURE get_problem_services_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, l_problem_srv_csr OUT CURSOR_TYPE); PROCEDURE get_problemsrv_srvperf_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, l_problem_srv_csr OUT CURSOR_TYPE, l_srv_perf_info OUT CURSOR_TYPE); PROCEDURE get_alerts_info_for_service( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, service_name_in IN VARCHAR2, db_name_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE); -- Procedure get_ha_top5_services_keys -- -- PURPOSE: this procedure will get service name, dbname for -- top 5 services based on the metric column given -- (either avg_response_time or cpu_load ) for -- service_performance metrics -- -- IN parameters: -- target_name_in IN VARCHAR2 IN cluster databse name -- metric_column_in IN VARCHAR2 IN metric column name -- PROCEDURE get_ha_top5_services_keys( target_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, service_info_csr OUT CURSOR_TYPE); -- Function interface_stats -- -- PURPOSE: this function is called for populating the view -- MGMT$INTERFACE_STATS -- IN parameters: -- cluster target_name IN The p_cname -- OUT parameters: -- rac interconnects as mgmt_racdb_iface_stats_table -- FUNCTION interface_stats ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_racdb_iface_stats_table; -- Function cluterware_status -- -- PURPOSE: this function is called for getting the status of cluster target -- , status of member host targets from the crs status metric -- -- IN parameters: -- cluster target_name IN The p_cname -- OUT parameters: -- cluster status as mgmt_cluster_status_table -- FUNCTION clusterware_status ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE ) RETURN mgmt_cluster_status_table; -- used for logging info procedure log_info ( p_message varchar2, p_module varchar2 default 'em.rac' ); /*This callback proc is called when severity against 'Status' column of 'crs_event' metric of 'cluster' target type is raised. It's is entry point for all crs event based callback handling. */ procedure rac_crs_callback_proc ( violation in mgmt_violation_summary_rec, saveRow out number ); procedure update_config_activities_table ( p_txn_id in mgmt_config_activities.txn_id%type, p_name in mgmt_targets.target_name%type, p_type in mgmt_targets.target_type%type, p_status_code in mgmt_config_activities.status_code%type, p_config_type in mgmt_config_activities.config_type%type, p_time in varchar2 default to_char(sysdate), p_cluster_name in mgmt_config_activities.cluster_name%type, p_host_name in mgmt_config_activities.host_name%type, p_src_host_name in mgmt_config_activities.host_name%type default '', p_param_nv in mgmt_namevalue_array default null ) ; procedure host_add_callback ( p_target_name in mgmt_targets.target_name%type, p_target_type in mgmt_targets.target_type%type, p_target_guid in mgmt_targets.target_guid%type ) ; -- Procedure get_cluster_resource_summary -- -- PURPOSE: this function is called for getting -- Cluster managed resource summary -- This queries mgmt_current_metrics table to get latest -- metric information from resource_status and resource_instamce metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_count, -- online, -- offline -- unknown -- intermediate -- critical -- warning PROCEDURE get_cluster_resources_summary ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE); -- Procedure get_cluster_resources -- -- PURPOSE: this function is called for getting -- Cluster managed resource information -- This queries mgmt_current_metrics table to get latest -- metric information from resource_status and resource_instamce metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_name, -- resource_type, -- status, -- cardinality, -- degree, -- online, -- offline -- unknown -- intermediate -- critical -- warning PROCEDURE get_cluster_resources ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE); -- Procedure get_cluster_resource_instances -- -- PURPOSE: this function is called for getting -- Cluster managed resource instance information -- This queries mgmt_current_metrics table to get latest -- metric information from resource_instance metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_name, -- resource_type, -- resource_instance_id, -- status, -- host PROCEDURE get_cluster_resource_instances ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE); -- Function get_resource_name_for_target -- -- PURPOSE: -- return resource name for em oracle internal em target -- database, listener, asm -- -- IN parameters: -- v_target_name IN target name -- v_target_type IN target type -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_resource_name_for_target ( v_target_name IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2; -- Function get_is_has_managed -- -- PURPOSE: -- -- IN parameters: -- v_target_name IN target name -- v_target_type IN target type -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_is_has_managed ( v_target_name IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2; -- Function get_is_listener_scan -- -- PURPOSE: -- -- IN parameters: -- v_target_name IN target name -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_is_listener_scan ( v_target_name IN VARCHAR2 ) RETURN VARCHAR2; -- Function get_is_rac_listener -- -- PURPOSE: -- Determines whether a target listener is RAC enabled or not -- -- IN parameters: -- v_target_name IN target name -- -- OUT -- 'true' if listener target is RAC -- 'false' if not RAC FUNCTION get_is_rac_listener ( v_target_name IN VARCHAR2 ) RETURN VARCHAR2; -- Function get_target_guid_from_resource -- -- PURPOSE: -- return array of target guid for a resource name -- -- IN parameters: -- v_target_type IN target type -- v_resource_name IN resource name -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_target_guid_from_resource ( v_target_type IN VARCHAR2, v_resource_name IN VARCHAR2 ) RETURN MGMT_TARGET_GUID_ARRAY; end emd_rac; / show errors; CREATE OR REPLACE PACKAGE mgmt_rac as -- Procedure handle_cluster_tgttype_add -- -- PURPOSE: -- This is a call back function registered to be called when -- a new target type meta ver is added -- Its a NOOP for non cluster target types -- For cluster target types if the type_meta_ver is > 4.0 it -- will register default collection for that meta ver for -- metric priv_intrcon -- -- IN parameters: -- target_type IN -- target type_meta_ver IN -- -- OUT parameters: PROCEDURE handle_cluster_tgttype_add( p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2); -- Procedure cluster_10204_upgr_post_proc -- -- PURPOSE: -- This is the post upgrade meta data change for cluster target -- when upgrading from 10.2.0.4 -- It starts the collection for metric priv_intrcon for -- already existing cluster targets with ver > 4.0 -- -- IN parameters: -- PROCEDURE cluster_10204_upgr_post_proc; END mgmt_rac; / show errors;