Golden Gate Configuration: configure a custom heartbeat table

From Robs_Wiki
Jump to: navigation, search

Introduction

A heartbeat table is a table that is part of a Golden Gate configuration for monitoring purposes. It shows when records are applied in the source and in the target and if there is a lag in the replication. Oracle classical and integrated replicat come with their own heartbeat table, which is explained in another post on this website. But a custom heartbeat table can also be beneficial and in this post I explain how to configure a custom hearbeat table:

  • It ships records across just like any other table that is part of the Golden Gate replication
  • Every minute a new record is inserted and the insertion and replication time are recorded in the custom table
  • Every 5 minutes one (random) records gets updated and the update and replication time are recorded in the custom hearbeat table.

Enough said, let's start to configure it !! Note that I will be using the demo replication of the HR schema, which I set up in another post of this website:
http://rob.lasonder.org/index.php?title=Golden_Gate_Recipe:_configure_Oracle12c_integrated_replicat

Step 1 On the source database: create the custom heartbeat table

We will create the custom heartbeat table in the source database. Because Golden Gate (the current version, 12.2) does not support Identity columns yet, we will create an auto-updated Primary Key the old fashioned way, using a sequence and a trigger. Run the following SQL in the source database:

create table hr.gg_tbl_custom_heartbeat (
id NUMBER PRIMARY KEY,
colour varchar2(10),
insert_date_source date,
insert_date_target date,
update_date_source date,
update_date_target date
);

CREATE SEQUENCE hr.gg_seq_custom_heartbeat
START WITH     1
INCREMENT BY   1
CACHE 100
NOCYCLE;

CREATE OR REPLACE TRIGGER hr.gg_trg_custom_heartbeat
  BEFORE INSERT ON hr.gg_tbl_custom_heartbeat
  FOR EACH ROW
BEGIN
  SELECT hr.gg_seq_custom_heartbeat.nextval
    INTO :new.id
    FROM dual;
END;
/ 

Step 2 On the source database: create jobs to insert and update the custom heartbeat table

Run the following SQL:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name             => 'hr.gg_insert_custom_heartbeat',
      job_type             => 'PLSQL_BLOCK',
      job_action           => 'BEGIN insert into hr.gg_tbl_custom_heartbeat (colour,insert_date_source) values (''GREEN'',sysdate); commit; END;',
      repeat_interval      => 'freq=MINUTELY;interval=1',
      enabled              =>  TRUE,
      comments             => 'inserts records in manual heartbeat table');
END;
/

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name             => 'hr.gg_update_custom_heartbeat',
      job_type             => 'PLSQL_BLOCK',
      job_action           => 'BEGIN update hr.gg_tbl_custom_heartbeat set colour =''BLUE'', update_date_source=sysdate where id in (select id from ( select id from hr.gg_tbl_custom_heartbeat order by dbms_random.value ) where rownum=1); commit; END;',
      repeat_interval      => 'freq=MINUTELY;interval=5',
      enabled              =>  TRUE,
      comments             => 'updates 1 random record in manual heartbeat table');
END;
/

Step 3 On the source database: check the job execution

Wait 10 or 15 minutes or so and check the execution of the jobs as follows:

col job_name format a30
set linesize 200
col next_run_date format a20

select job_name, to_char(start_date,'DD-MON-YYYY HH24:MI:SS') start_date, to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') last_start_date, to_char(next_run_date,'DD-MON-YYYY HH24:MI:SS') next_run_date, state, enabled from dba_scheduler_jobs where job_name like '%_HEARTBEAT%';

JOB_NAME                       START_DATE                    LAST_START_DATE               NEXT_RUN_DATE                 STATE           ENABL
------------------------------ ----------------------------- ----------------------------- ----------------------------- --------------- -----
GG_INSERT_CUSTOM_HEARTBEAT     21-JUL-2017 11:22:29          21-JUL-2017 11:32:30          21-JUL-2017 11:33:29          SCHEDULED       TRUE
GG_UPDATE_CUSTOM_HEARTBEAT     21-JUL-2017 11:25:32          21-JUL-2017 11:30:34          21-JUL-2017 11:35:32          SCHEDULED       TRUE

You can also issue the statement below in the source database and verify that the records are being inserted and updated at regular intervals:

SQL> select id, to_char(insert_date_source,'DD-MON-YYYY HH24:MI:SS') insert_source, to_char(update_date_source,'DD-MON-YYYY HH24:MI:SS') update_source, colour 
from hr.gg_tbl_custom_heartbeat order by 1;

        ID INSERT_SOURCE        UPDATE_SOURCE        COLOUR
---------- -------------------- -------------------- ----------
         1 21-JUL-2017 21:42:41 21-JUL-2017 21:57:45 BLUE
         2 21-JUL-2017 21:43:44 21-JUL-2017 21:47:44 BLUE
         3 21-JUL-2017 21:44:44                      GREEN
         4 21-JUL-2017 21:45:44                      GREEN
         5 21-JUL-2017 21:46:45                      GREEN
         6 21-JUL-2017 21:47:44                      GREEN
         7 21-JUL-2017 21:48:44                      GREEN
         8 21-JUL-2017 21:49:44                      GREEN
         9 21-JUL-2017 21:50:44 21-JUL-2017 21:52:44 BLUE
        10 21-JUL-2017 21:51:44                      GREEN
        11 21-JUL-2017 21:52:44                      GREEN
        12 21-JUL-2017 21:53:44                      GREEN
        13 21-JUL-2017 21:54:45                      GREEN
        14 21-JUL-2017 21:55:44                      GREEN
        15 21-JUL-2017 21:56:44 21-JUL-2017 22:02:41 BLUE
        16 21-JUL-2017 21:57:45                      GREEN
        17 21-JUL-2017 21:58:45                      GREEN
        18 21-JUL-2017 21:59:45                      GREEN
        19 21-JUL-2017 22:00:45                      GREEN
        20 21-JUL-2017 22:01:41                      GREEN
        21 21-JUL-2017 22:02:41                      GREEN
        22 21-JUL-2017 22:03:45                      GREEN
        23 21-JUL-2017 22:04:41                      GREEN
        24 21-JUL-2017 22:05:41                      GREEN
        25 21-JUL-2017 22:06:41                      GREEN

25 rows selected.

SQL> select distinct colour, count(*) from hr.gg_tbl_custom_heartbeat group by colour;

COLOUR       COUNT(*)
---------- ----------
GREEN             259
BLUE               56

Step 4 On the target: create and activate additional triggers

The triggers on the target will update the relevant target date columns upon inserts / deletes

create or replace trigger hr.gg_trg_action_ins BEFORE INSERT ON hr.GG_TBL_CUSTOM_HEARTBEAT FOR EACH ROW
BEGIN
  :NEW.insert_date_target:=sysdate;
END;
/


create or replace trigger hr.gg_trg_action_upd BEFORE UPDATE on hr.GG_TBL_CUSTOM_HEARTBEAT FOR EACH ROW
BEGIN
  :NEW.update_date_target :=sysdate;
END;
/

Triggers are de-activated by Golden Gate by default. Override these default settings as follows:

SYS@dggt01_1>EXEC dbms_ddl.set_trigger_firing_property('HR','GG_TRG_ACTION_INS', FALSE);

PL/SQL procedure successfully completed.

SYS@dggt01_1>EXEC dbms_ddl.set_trigger_firing_property('HR','GG_TRG_ACTION_UPD', FALSE);

PL/SQL procedure successfully completed.

Step 5 View data processing

By running the query below we can see how the data is processed and the difference in lag:

SYS@dggt01_1>select id, colour, to_char(insert_date_source,'DD-MON-YYYY HH24:MI:SS') insert_date_source, to_char(insert_date_target,'DD-MON-YYYY HH24:MI:SS') insert_date_target from hr.GG_TBL_CUSTOM_HEARTBEAT where id >= 1565;

        ID COLOUR     INSERT_DATE_SOURCE            INSERT_DATE_TARGET
---------- ---------- ----------------------------- -----------------------------
      1565 GREEN      25-JUL-2017 09:41:22          25-JUL-2017 09:41:40
      1566 GREEN      25-JUL-2017 09:42:22          25-JUL-2017 09:42:32
      1567 GREEN      25-JUL-2017 09:43:22          25-JUL-2017 09:43:43
      1568 GREEN      25-JUL-2017 09:44:22          25-JUL-2017 09:44:44
      1569 GREEN      25-JUL-2017 09:45:22          25-JUL-2017 09:45:42
      1570 GREEN      25-JUL-2017 09:46:22          25-JUL-2017 09:46:43
      1571 GREEN      25-JUL-2017 09:47:22          25-JUL-2017 09:47:32
      1572 GREEN      25-JUL-2017 09:48:22          25-JUL-2017 09:48:42

8 rows selected.

You can now build queries of the stats and export them to Excel for example. A similar approach can be used for the updates. In this example I change the colour from GREEN to BLUE during updates, so you can query for any BLUE records to view the update lags.

SQL> select id, colour, to_char(update_date_source,'DD-MON-YYYY HH24:MI:SS') update_date_source, to_char(update_date_target,'DD-MON-YYYY HH24:MI:SS') update_date_target
from hr.GG_TBL_CUSTOM_HEARTBEAT where colour='BLUE' order by 1;

        ID COLOUR     UPDATE_DATE_SOURCE            UPDATE_DATE_TARGET
---------- ---------- ----------------------------- -----------------------------
          112 BLUE       25-JUL-2017 12:30:41          25-JUL-2017 12:30:59
          119 BLUE       25-JUL-2017 12:18:27          25-JUL-2017 12:18:38
          167 BLUE       25-JUL-2017 11:23:27          25-JUL-2017 11:23:33
          176 BLUE       25-JUL-2017 10:47:22          25-JUL-2017 10:47:34