-- Heartbeat Update Job
--   Based on Oracle supplied mechanism V11
-- To be implemented in all GoldenGate deployments
-- Job is required for any 'source' database
-- There should be no issue with creating on any 'target' in addition
-- Update History
-- 04-MAR-13 - Oracle Corporation SGEORGE - V11 - Updated script to prompt for schema name.
-- 05-APR-2013 - MKruse - FIL - Updated to convert all timestamps to UTC
-- connect / as sysdba

grant select on v_$instance to GGADMIN;
grant select on v_$database to GGADMIN;

BEGIN
    SYS.DBMS_SCHEDULER.DROP_JOB(job_name => 'GGADMIN.OGG_HB', defer => false, force => false);
    SYS.DBMS_SCHEDULER.DROP_JOB(job_name => 'GGADMIN.OGG_HB_PURGE', defer => false, force => false);
END;
/

CREATE OR REPLACE PROCEDURE GGADMIN.gg_update_hb_tab IS
v_thread_num     NUMBER;
v_db_name VARCHAR2 (128);
BEGIN
SELECT name
  INTO v_db_name
  FROM v$database;

UPDATE GGADMIN.heartbeat
   SET update_timestamp = SYSTIMESTAMP at time zone 'UTC',
       src_db = v_db_name;
COMMIT;
END;
/

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (job_name => 'GGADMIN.OGG_HB',
                               job_type => 'STORED_PROCEDURE',
                               job_action => 'GGADMIN.GG_UPDATE_HB_TAB',
                               number_of_arguments => 0,
                               start_date => NULL,
                               repeat_interval => 'FREQ=MINUTELY',
                               end_date => NULL,
                               job_class => '"SYS"."DEFAULT_JOB_CLASS"',
                               enabled => FALSE,
                               comments => 'GoldenGate');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'GGADMIN.OGG_HB', attribute => 'restartable', value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'GGADMIN.OGG_HB', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.enable(name => 'GGADMIN.OGG_HB');
END;
/

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (job_name => 'GGADMIN.OGG_HB_PURGE',
                               job_type => 'PLSQL_BLOCK',
                               job_action => 'delete GGADMIN.GGS_HEARTBEAT_HISTORY where TARGET_COMMIT < sysdate-90;',
                               start_date => trunc(sysdate)+1.1,
                               repeat_interval => 'FREQ=DAILY',
                               end_date => NULL,
                               job_class => '"SYS"."DEFAULT_JOB_CLASS"',
                               enabled => FALSE,
                               comments => 'GoldenGate');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'GGADMIN.OGG_HB_PURGE', attribute => 'restartable', value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'GGADMIN.OGG_HB_PURGE', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.enable(name => 'GGADMIN.OGG_HB_PURGE');
END;
/
