Rem drv: <create type="tables"/>
Rem
Rem $Header: ping_tables.sql 29-jun-2005.01:47:33 gsbhatia Exp $
Rem
Rem ping_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ping_tables.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    gsbhatia    06/26/05 - New repmgr header impl 
Rem    rpinnama    06/06/05 - Move last_update_utc to a separate table to 
Rem                           avoid contention on mgmt_emd_ping (bug 4397891) 
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    rpinnama    12/16/04 - Fix 4069628: Store the reverse ping job name 
Rem    rpinnama    02/06/03 - Add unrch_start_time
Rem    rpinnama    09/11/02 - Add emd uptime
Rem    rpinnama    06/04/02 - Add clean heartbeat timestamp.
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

rem
rem PURPOSE
rem
rem   The MGMT_EMD_PING table holds the list of EMD hosts and their heartbeat details
rem
rem COLUMNS
rem
rem   target_guid        - target guid of the agent.
rem 
rem   status             - status of the Agent. 1 => UP, 2=> DOWN.
rem
rem   last_heartbeat_ts  - the date/time (in Agent TZ) when the Agent last performed 
rem                        the heartbeat.
rem
rem   last_heartbeat_utc - the date/time (in GMT) when the repository recorded
rem                        the heartbeat.
rem
rem   clean_heartbeat_utc - the date/time (in GMT) when the Agent last performed 
rem                        the clean heartbeat
rem 
rem   status_sync_utc    - the date/time (in GMT) upto when the Agent has sent the 
rem                        severities/blackout/error information for all the 
rem                        targets that it monitors.
rem 
rem   emd_utptime_utc    - the date/time (in GMT) when the Agent started.
rem
rem   last_checked_utc   - the date/time (in GMT) when the repository last checked the
rem                        ping status of the Agent.
rem
rem   max_inactive_time  - The duration (in sec) for which this Agent can be
rem                        inactive (without heartbeats) before being marking as DOWN.
rem 
rem   down_reason_code   - the numeric value describing the reason why the Agent
rem                        is down. 0 if the Agent is not down.
rem
rem   down_reason_msg    - the text message describing the reason why the Agent
rem                        is down. empty if the Agent is not down.
rem
rem   heartbeat_recorder_url - the middle-tier URL that recorded the last heartbeat.
rem
rem  NOTES
rem      Both down_reason_code and down_reason_msg are valid only if the
rem      status is DOWN.
rem
rem

CREATE TABLE MGMT_EMD_PING
(
    target_guid            RAW(16)        NOT NULL,
    status                 NUMBER         DEFAULT 1,
    last_heartbeat_ts      DATE           DEFAULT SYSDATE,
    last_heartbeat_utc     DATE           DEFAULT SYSDATE,
    clean_heartbeat_utc    DATE           NOT NULL,
    status_sync_utc        DATE           NOT NULL,
    emd_uptime_utc         DATE           NOT NULL,
    unrch_start_ts         DATE           DEFAULT NULL,
    max_inactive_time      NUMBER         DEFAULT 120,
    down_reason_code       NUMBER         DEFAULT 0,
    down_reason_msg        VARCHAR2(1024) DEFAULT ' ',
    heartbeat_recorder_url VARCHAR2(256)  DEFAULT ' ',
    ping_job_name          VARCHAR2(64)   DEFAULT NULL,
    job_submit_time        DATE           DEFAULT NULL
)
MONITORING;

rem
rem PURPOSE
rem
rem   The MGMT_EMD_PING_CHECK table holds the list of agents and when the repository
rem   checked their ping status.
rem
rem COLUMNS
rem
rem   target_guid        - target guid of the agent.
rem
rem   last_checked_utc   - the date/time (in GMT) when the repository last checked the
rem                        ping status of the Agent.
rem
CREATE TABLE MGMT_EMD_PING_CHECK
(
    target_guid            RAW(16)        NOT NULL,
    last_checked_utc       DATE           DEFAULT SYSDATE,
    CONSTRAINT mgmt_emd_ping_check_pk
      PRIMARY KEY (target_guid)
)
ORGANIZATION INDEX
MONITORING;