Rem drv: <create type="pkgbodys" pos="esm/esm_pkgbodys.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/failover/failover_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2008/09/26 14:26:37 jsadras Exp $
Rem
Rem failover_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2008, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      failover_pkgbody.sql - Manages failover of middle-tier components
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    jsadras     09/23/08 - Bug:7426001, add dbms_assert calls
Rem    sradhakr    05/19/07 - Backport sradhakr_rfi_backport_5873907_10.2.4.0.0
Rem                           from st_emcore_10.2.0.1.0
Rem    sradhakr    03/09/07 - incorporating review comments for Bug#5873907. 
Rem    sradhakr    03/07/07 - Bug#5873907 - OMS FAILOVER CODE DOES NOT HANDLE
Rem                           DST CHANGES.
Rem    sradhakr    04/05/07 - Backport sradhakr_dummy2 from main
Rem    neearora    08/25/05 - Fetch heartbeat_interval_factor from mgmt_parameters 
Rem    rmaggarw    07/29/05 - handle callback errors
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    skini       06/23/05 - Do not keep failvoer entry on error 
Rem    skini       06/23/05 - Catch errors if callbacks fail 
Rem    aholser     06/09/05 - add host name to register proc 
Rem    rmaggarw    01/27/05 - introduce sticky omsid 
Rem    gan         08/19/04 - add get_active_mt_count 
Rem    gan         11/21/03 - increase the time interval for failover 
Rem    skini       06/06/03 - Prevent unnecessary locking
Rem    skini       04/23/03 - Add column for hb interval
Rem    rpinnama    09/16/02 - Use Ping API
Rem    mbhoopat    07/30/02 - Add support for OMS_URL parameter
Rem    aholser     05/29/02 - insert mgmt_oms_parameters entry.
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    aholser     03/28/02 - monitoring the monitor.
Rem    skini       01/30/02 - Increase failover interval
Rem    rpinnama    01/21/02 - Lock during inserting.
Rem    rpinnama    01/18/02 - Add a method to get the count of entries..
Rem    skini       11/21/01 - Merged skini_js_stability
Rem    skini       11/19/01 - fix typo
Rem    skini       11/18/01 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_FAILOVER AS

--Forward declaration for private function
FUNCTION get_oms_id(p_host_url IN VARCHAR2) RETURN INTEGER;

FUNCTION register(p_host_url IN VARCHAR2, 
                  p_oms_url IN VARCHAR2,
                  p_host_name IN VARCHAR2,
                  p_heartbeat_interval IN NUMBER) RETURN INTEGER IS 
l_mt_cnt INTEGER := 0;
l_new_id INTEGER;
l_old_hosturl VARCHAR2(256);
l_value VARCHAR2(256);
l_oms_id INTEGER;

BEGIN

    -- Lock the table before getting the count..

    LOCK TABLE MGMT_FAILOVER_TABLE IN EXCLUSIVE MODE;

    l_mt_cnt := get_active_mt_count;

    -- If this is no active  middle tier system yet,
    --  start the ping sub system.
    IF (l_mt_cnt = 0) 
    THEN
      -- We have to implement a callback mechanism here..
      EM_PING.start_ping_system;

    END IF;

    INSERT INTO MGMT_FAILOVER_TABLE(failover_id, last_time_stamp, 
                                    host_url, heartbeat_interval,
				    last_time_stamp_utc)
        VALUES(MGMT_FAILOVER_SEQUENCE.NEXTVAL, SYSDATE, 
               p_host_url, p_heartbeat_interval, sys_extract_utc(SYSTIMESTAMP))
        RETURNING failover_id INTO l_new_id;

    BEGIN
      SELECT value INTO l_value FROM MGMT_OMS_PARAMETERS WHERE 
          host_url=p_host_url AND 
          name='TIMESTAMP';

      UPDATE MGMT_OMS_PARAMETERS SET
          value=TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') WHERE 
              host_url=p_host_url AND 
              name='TIMESTAMP';

      UPDATE MGMT_OMS_PARAMETERS SET value=p_oms_url WHERE 
          host_url=p_host_url AND 
          name='OMS_URL';

      EXCEPTION
        WHEN NO_DATA_FOUND 
        THEN
          INSERT INTO mgmt_oms_parameters(host_url, name, value) VALUES 
          (p_host_url, 'TIMESTAMP', TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS'));

          INSERT INTO mgmt_oms_parameters(host_url, name, value) VALUES 
          (p_host_url, 'OMS_URL', p_oms_url);

          INSERT INTO mgmt_oms_parameters(host_url, name, value) VALUES 
          (p_host_url, 'HOST_NAME', p_host_name);

          l_oms_id := get_oms_id(p_host_url);
	  INSERT INTO mgmt_oms_parameters(host_url, name, value) VALUES 
          (p_host_url, 'OMS_ID', TO_CHAR(l_oms_id));

    END; 

    RETURN l_new_id;
END;

FUNCTION get_middle_tier_count RETURN INTEGER IS
  l_cnt INTEGER;
BEGIN

  SELECT count(failover_id) INTO l_cnt FROM MGMT_FAILOVER_TABLE;

  return l_cnt;

END;

FUNCTION get_active_mt_count RETURN INTEGER IS
l_oms_grace NUMBER;
l_cnt INTEGER;
BEGIN
  BEGIN
    SELECT to_number(parameter_value) INTO l_oms_grace
    FROM mgmt_parameters
    WHERE parameter_name = G_ACTIVE_OMS_GRACE_PROP;
  EXCEPTION
    WHEN OTHERS THEN 
     l_oms_grace := G_DEFAULT_OMS_GRACE;
  END;
  IF l_oms_grace < G_MIN_OMS_GRACE THEN
     l_oms_grace := G_MIN_OMS_GRACE;
  END IF;

  SELECT count(failover_id) INTO l_cnt 
  FROM MGMT_FAILOVER_TABLE
  WHERE SYSDATE-last_time_stamp < l_oms_grace/(24*60*60);

  return l_cnt;
  
   
END;

PROCEDURE register_callback(p_callback_name VARCHAR2) IS
BEGIN
    INSERT INTO MGMT_FAILOVER_CALLBACKS(callback_name) 
           VALUES(p_callback_name);
END;

FUNCTION heartbeat(p_id IN NUMBER) RETURN INTEGER IS
BEGIN

    UPDATE MGMT_FAILOVER_TABLE SET last_time_stamp=SYSDATE, last_time_stamp_utc=sys_extract_utc(SYSTIMESTAMP) WHERE failover_id=p_id;
    RETURN SQL%ROWCOUNT;
END;

PROCEDURE check_failure IS
  l_host_url VARCHAR2(256);
  l_count NUMBER;
  l_skip BOOLEAN;
  l_heartbeat_interval_factor NUMBER;
BEGIN
  BEGIN
    SELECT to_number(parameter_value) INTO l_heartbeat_interval_factor
    FROM mgmt_parameters
    WHERE parameter_name = G_HEARTBEAT_INTERVAL_FACTOR;
  EXCEPTION
    WHEN OTHERS THEN
     l_heartbeat_interval_factor := G_DEFAULT_HB_INTERVAL_FACTOR;
  END;

    SELECT COUNT(*) INTO l_count FROM MGMT_FAILOVER_TABLE WHERE
         sys_extract_utc(SYSTIMESTAMP)-last_time_stamp_utc > numtodsinterval(heartbeat_interval*l_heartbeat_interval_factor, 'SECOND');

    -- Prevent unnecesary locking
    IF l_count=0 THEN
        RETURN;
    END IF;

    -- If we're here, there is at least one dead oms
    LOCK TABLE MGMT_FAILOVER_TABLE IN EXCLUSIVE MODE;

    FOR crec IN (SELECT failover_id, last_time_stamp 
                  FROM MGMT_FAILOVER_TABLE WHERE 
                  sys_extract_utc(SYSTIMESTAMP)-last_time_stamp_utc  > numtodsinterval(heartbeat_interval*l_heartbeat_interval_factor, 'SECOND')) LOOP

        l_skip := FALSE;
        SAVEPOINT oms_iteration;
        -- This middle-tier is considered to have died. Call
        -- all the callbacks for this middle-tier
        FOR C in (SELECT callback_name FROM MGMT_FAILOVER_CALLBACKS 
                  ORDER BY callback_name) LOOP

        SAVEPOINT callback_iteration;
        BEGIN
            EXECUTE IMMEDIATE 
                'BEGIN ' || DBMS_ASSERT.SQL_OBJECT_NAME(C.callback_name) || '(:1, :2); END;' 
                USING crec.failover_id, crec.last_time_stamp;
        EXCEPTION
        WHEN OTHERS THEN
            IF (em_check.is_recoverable_error(SQLCODE)) THEN
                MGMT_LOG.log_error(G_MODULE_NAME, SQLCODE, 
                   'OMS failover: Recoverable error encountered when running callback ' ||
                   C.callback_name || ' for OMS: '|| crec.failover_id || 
                   '. Will try again: ' || SQLERRM);
                -- Rollback all callbacks for this OMS and continue with next oms
                ROLLBACK TO oms_iteration;
                l_skip := TRUE;
                EXIT;
            ELSE
                MGMT_LOG.log_error(G_MODULE_NAME, SQLCODE, 
                   'OMS failover: error encountered when running callback ' ||
                   C.callback_name || ' for OMS: '|| crec.failover_id || 
                   '. Skipping callback: ' || SQLERRM);
                -- Rollback current callback and continue with next callback
                ROLLBACK TO callback_iteration;
            END IF;
        END;
        END LOOP;

        IF (l_skip = FALSE) THEN
            -- Update the timestamp for the 'down since'.  If the entry doesn't exist,
            -- it is considered removed
            BEGIN
              SELECT host_url INTO l_host_url FROM mgmt_failover_table WHERE failover_id=crec.failover_id;
              UPDATE mgmt_oms_parameters SET value=TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') WHERE host_url=l_host_url AND name='TIMESTAMP';
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                NULL; 
            END;

            -- Delete the entry for this middle-tier
            DELETE FROM MGMT_FAILOVER_TABLE WHERE failover_id=crec.failover_id;
        END IF;
    END LOOP;    
END;

FUNCTION get_oms_id(p_host_url IN VARCHAR2) RETURN INTEGER IS
  oms_id INTEGER;
BEGIN
    --return existing
    SELECT to_number(value)
    INTO oms_id
    FROM MGMT_OMS_PARAMETERS
    WHERE NAME = 'OMS_ID'
    AND HOST_URL = p_host_url;

    RETURN oms_id;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      --generate new
      SELECT nvl(max(to_number(value)),0)+1 
      INTO oms_id 
      FROM MGMT_OMS_PARAMETERS
      WHERE NAME = 'OMS_ID';
  
      RETURN oms_id;
END;


END MGMT_FAILOVER;
/
show errors;