Rem drv: <create type="pkgdefs" pos="preferences/preferences_pkgdefs.sql+"/>
Rem
Rem $Header: ping_pkgdefs.sql 10-aug-2007.02:59:46 pmodi Exp $
Rem
Rem ping_pkgdefs.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem ping_pkgdefs.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem pmodi 08/10/07 - Backport - Bug-6267173/5955813 - NLS Msg for
Rem OMS generated CLEAR for Agent
Rem rpinnama 08/28/05 -
Rem rkpandey 07/31/05 - Added more agent status msgs
Rem rkpandey 07/21/05 - Added Agent unreachable with errors
Rem scgrover 07/07/05 - add extended sql trace
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem pmodi 06/20/05 - Bug:4389284- Change for NLS Message
Rem neearora 03/06/05 - Removed entry for G_DEBUG
Rem rpinnama 02/17/05 -
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem ktlaw 01/11/05 - add repmgr header
Rem gan 08/19/04 - add constants
Rem rzazueta 10/30/03 - Add procedures for detailed logging
Rem rpinnama 10/15/03 - Remove special checks for host
Rem streddy 06/09/03 - Added insert_unrch_severities
Rem aholser 04/09/03 - move set_inactive_time
Rem rpinnama 01/13/03 - Add support for disabling ping system
Rem rpinnama 11/08/02 - Add add_agent API
Rem rpinnama 10/23/02 - Use constants
Rem rpinnama 10/18/02 - Use Agent Ping
Rem rpinnama 10/18/02 - Add constants for heartbeat recorder and status updater
Rem rpinnama 09/11/02 - Add constants for cleant and first heartbeat
Rem rpinnama 07/16/02 - Adding new error messages
Rem rpinnama 06/25/02 - Add constants for error messages
Rem rpinnama 06/04/02 - Use the new batch heartbeat definition.
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
CREATE OR REPLACE PACKAGE EM_PING AS
---------------------------------------------------------
-- Package Variables
---------------------------------------------------------
G_DETAILED_LOGGING BOOLEAN := NULL;
MODULE_NAME CONSTANT VARCHAR2(64) := 'EM_PING';
HEARTBEAT_RECORD_ACTION CONSTANT VARCHAR2(32) := 'HEARTBEAT_RECORDER';
HEARTBEAT_RECORD_LOGNAME CONSTANT VARCHAR2(32) := 'Agent Heartbeat Recorder';
-- The following names are used for logging heartbeat performance details
HEARTBEAT_CNT_NAME CONSTANT VARCHAR2(32) := 'HEARTBEATS';
CLEAN_HEARTBEAT_CNT_NAME CONSTANT VARCHAR2(32) := 'CLEAN_HEARTBEATS';
DIRTY_HEARTBEAT_CNT_NAME CONSTANT VARCHAR2(32) := 'DIRTY_HEARTBEATS';
HEARTBEAT_ERROR_CNT_NAME CONSTANT VARCHAR2(32) := 'HEARTBEAT_ERRORS';
MARK_NODE_STATUS_ACTION CONSTANT VARCHAR2(32) := 'AGENT_STATUS_MARKER';
MARK_NODE_STATUS_LOGNAME CONSTANT VARCHAR2(32) := 'Agent Ping';
-- The following names are used for logging agent status marker details
MARKED_AGENT_CNT_NAME CONSTANT VARCHAR2(32) := 'AGENTS_MARKED';
UPDATE_AGENT_ACTION CONSTANT VARCHAR2(32) := 'AGENT_STATUS_CONFIRM';
UPDATE_AGENT_LOGNAME CONSTANT VARCHAR2(32) := 'Agent Ping Status Update';
-- The following names are used for logging agent confirmation details
CONFIRMED_AGENT_CNT_NAME CONSTANT VARCHAR2(32) := 'AGENTS_CONFIRMED';
NODE_STATUS_UP constant NUMBER(1) := 1;
NODE_STATUS_DOWN constant NUMBER(1) := 2;
NODE_STATUS_POTENTIALLY_DOWN constant NUMBER(1) := 3;
HEARTBEAT_TIME_FORMAT CONSTANT VARCHAR2(32) := 'YYYY-MM-DD HH24:MI:SS';
CLEAN_HEARTBEAT CONSTANT VARCHAR2(8) := 'FALSE';
PINGCFM_JOBTYPE CONSTANT VARCHAR2(16) := 'ConfirmEMDDown';
PINGCFM_JOB_DESCRIPTION CONSTANT VARCHAR2(64) := 'This is a Confirm EMD Down test job';
PINGCFM_JOBNAME_PREFIX CONSTANT VARCHAR2(16) := 'PINGCFMJOB_';
PINGCFM_JOBPARAM_NAME CONSTANT VARCHAR2(16) := 'emd_url_list';
AGENT_UNRCH_START_MSG CONSTANT VARCHAR2(64) := 'Agent is Unreachable';
AGENT_UNRCH_CLEAR_MSG CONSTANT VARCHAR2(64) := 'Agent Unreachability is cleared.';
CODE_AGENT_UNRCH_HOST_UP CONSTANT NUMBER := -1;
CODE_AGENT_UNRCH_HOST_UNRCH CONSTANT NUMBER := -2;
CODE_AGENT_UNRCH_MISCFGD CONSTANT NUMBER := -3;
CODE_BROKEN_AGENT_OMS_COMM CONSTANT NUMBER := -4;
CODE_AGENT_UNRCH_WITH_ERR CONSTANT NUMBER := -10;
DISABLE_PING_SYSTEM_PROP CONSTANT VARCHAR2(32) := 'disable_ping_system';
DISABLE_PING_SYSTEM_PROP_CMT CONSTANT VARCHAR2(128) :=
'Property that defines whether the ping system should be disabled or not';
DETAILED_PING_LOGGING CONSTANT VARCHAR2(32) := 'detailed_ping_logging';
DETAILED_PING_LOGGING_CMT CONSTANT VARCHAR2(128) :=
'Property that defines whether the detailed ping logging should be enabled or not';
G_PING_START_GRACE_PROP CONSTANT VARCHAR2(50) := 'PING_START_GRACE_TIME';
G_DEFAULT_PING_START_GRACE CONSTANT INTEGER := 10;
AGENT_UNRCH_HOST_UP CONSTANT VARCHAR2(128) := 'Agent is Unreachable (REASON = {0}) but the host is UP.' ;
AGENT_UNRCH_HOST_UNRCH CONSTANT VARCHAR2(128) := 'Agent is Unreachable (REASON = {0}). Host is unreachable (REASON = {1}).' ;
AGENT_UNRCH_WITH_ERRORS CONSTANT VARCHAR2(128) := 'Agent has stopped monitoring. The following errors are reported : {0}.';
AGENT_UNRCH_MISCFGD CONSTANT VARCHAR2(128) := 'Agent Unreachable (REASON = Agent configured to upload to different repository).';
BROKEN_AGENT_OMS_COMM CONSTANT VARCHAR2(128) := 'Agent is unable to communicate with the OMS. (REASON = {0} ).';
AGENT_CLR_OMS_DETECTED CONSTANT VARCHAR2(128) := 'Agent is UP - Detected by Management Service' ;
NLSID_AGENT_UNRCH_CLEAR CONSTANT VARCHAR2(128) := 'EM__SYS__PING_AGENT_UNREACH_CLEAR' ;
NLSID_AGENT_UNRCH_HOST_UP CONSTANT VARCHAR2(128) := 'EM__SYS__PING_AGENT_UNREACH_HOST_UP' ;
NLSID_AGENT_UNRCH_HOST_UNRCH CONSTANT VARCHAR2(128) := 'EM__SYS__PING_AGENT_UNREACH_HOST_UNREACH' ;
NLSID_AGENT_UNRCH_MISCFGD CONSTANT VARCHAR2(128) := 'EM__SYS__PING_AGENT_UNRCH_MISCFGD' ;
NLSID_BROKEN_AGENT_OMS_COMM CONSTANT VARCHAR2(128) := 'EM__SYS__PING_BROKEN_AGENT_OMS_COMM' ;
NLSID_AGENT_UNRCH_WITH_ERRS CONSTANT VARCHAR2(128) := 'EM__SYS__AGENT_UNREACH_WITH_ERRORS' ;
NLSID_AGENT_CLR_OMS_DETECTED CONSTANT VARCHAR2(128) := 'EM__SYS__PING_AGENT_CLR_OMS_DETECTED' ;
EST_PING_NAME CONSTANT VARCHAR2(32) := 'EST_PING';
-- To speed up the HB recording process Agent R/S guid has been stored as constant
AGT_RS_METRIC_GUID CONSTANT RAW(16) := HEXTORAW('2DE9FFA8045EB0726854D4E0F1375B43');
PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN);
--
-- PURPOSE
-- Procedure(s) to enable/disable/verify detailed logging
--
PROCEDURE enable_detailed_logging;
PROCEDURE disable_detailed_logging;
FUNCTION is_detailed_logging_enabled RETURN INTEGER;
--
-- PURPOSE
-- Procedure(s) to enable/disable/verify ping subsystem
--
PROCEDURE enable_ping_system;
PROCEDURE disable_ping_system;
FUNCTION is_ping_system_enabled RETURN INTEGER;
--
-- PURPOSE
-- Procedure to add an agent to the ping subsystem
--
-- IN PARAMETERS
-- p_target_guid - target guid of the agent to be added
-- p_timezone_region - timezone region of the agent
--
-- NOTES
-- This procedure is called from the MGMT_TARGETS insert
-- trigger for agent target type.
--
PROCEDURE add_target(p_target_guid IN RAW,
p_timezone_region IN VARCHAR2);
--
-- PURPOSE
-- Procedure to bootstrap the ping sub system.
-- This procedure sets up the last_checked_ts for all EMDs to current time.
--
-- NOTES
-- This routine should be called only once when the ping System is
-- coming up.
--
PROCEDURE start_ping_system;
--
-- PURPOSE
-- Procedure to bulk update the heartbeats of the EMDs.
--
-- IN PARAMETERS
-- v_agent_hearbeat_arr_in - array of all Agent Heartbeats.
--
PROCEDURE record_batch_heartbeat(
v_agent_hb_arr_in IN EM_AGENT_HEARTBEAT_ARRAY,
v_error_cnt OUT NUMBER,
v_error_info OUT EM_AGENT_HEARTBEAT_RESP_ARRAY);
--
--
-- PURPOSE
-- Procedure to mark the nodes as 'potentially' DOWN based on
-- whether the EMD performed heartbeat in the last
-- max_inactive_time seconds.
--
-- NOTES
-- This is the main routine that is called by the Agent Ping dbms job
-- scheduled to run every 30 seconds.
--
PROCEDURE mark_node_status;
--
-- PURPOSE
-- Procedure to update the ping status and the failure details
--
-- IN PARAMETERS
-- p_emd_url - Agent EMD URL
-- p_ping_status - Ping status code. 0 indicates successful ping.
-- p_error_msg_1 - Error message on unsuccessful ping.
-- p_error_msg_2 - Error message on unsuccessful ping.
PROCEDURE update_emd_status
(
v_emd_url_in IN VARCHAR2,
v_ping_status_in IN NUMBER,
v_error_msg_1_in IN VARCHAR2 DEFAULT NULL,
v_error_msg_2_in IN VARCHAR2 DEFAULT NULL
);
--
-- PURPOSE
-- THIS SHOULD NEVER BE CALLED. THIS IS MADE PUBLIC FOR
-- TESTING PURPOSES ONLY.
FUNCTION insert_unrch_severities(log_action_name_in IN VARCHAR2,
emd_url_in IN VARCHAR2,
collect_time_in IN DATE,
severity_code_in IN NUMBER,
severity_msg_in IN VARCHAR2,
severity_msg_nlsid_in IN VARCHAR2 DEFAULT NULL,
severity_msg_params_in IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
---------------------------------------------------------
-- Package Types (internal)
---------------------------------------------------------
TYPE t_guid_list IS
TABLE OF MGMT_TARGETS.target_guid%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_target_type_list IS
TABLE OF MGMT_TARGETS.target_type%TYPE
INDEX BY BINARY_INTEGER;
---------------------------------------------------------
-- Package Error Codes
---------------------------------------------------------
e_unknown_heartbeat CONSTANT NUMBER(1) := 1;
em_unknown_heartbeat CONSTANT VARCHAR2(128) := 'Error while recording heartbeat for : ';
e_mark_node_status CONSTANT NUMBER(1) := 2;
em_mark_node_status CONSTANT VARCHAR2(128) := 'Error while updating timestamps for ping tables.';
e_pingjob_submission CONSTANT NUMBER(1) := 3;
em_pingjob_submission CONSTANT VARCHAR2(128) := 'Error submitting ping confirm job.';
e_lock_update_status CONSTANT NUMBER(1) := 4;
em_lock_update_status CONSTANT VARCHAR2(128) := 'Error locking ping record while updating the status for : ';
e_update_status CONSTANT NUMBER(1) := 5;
em_update_status CONSTANT VARCHAR2(128) := 'Error while updating the status for : ';
e_insert_severity CONSTANT NUMBER(1) := 6;
em_insert_severity CONSTANT VARCHAR2(128) := 'Error while inserting severity for : ';
e_process_emd_queue CONSTANT NUMBER(1) := 7;
em_process_emd_queue CONSTANT VARCHAR2(128) := 'Error while processing emd queue entries.';
END EM_PING;
/
show errors;