Golden Gate Configuration: configure a heartbeat table

From Robs_Wiki
Jump to: navigation, search

References

Oracle GoldenGate Best Practices: Heartbeat Table for Monitoring Lag times Document ID1299679.1

Introduction

With GoldenGate 12.2, the Oracle GoldenGate heartbeat table is now integrated. This heartbeat table is easily configured and provides automatic heartbeat transactions each minute and includes an auto purge feature. The heartbeat works by replicating a heartbeat transaction across each Extract -> Pump -> Replicat that is configured in GoldenGate. These heartbeat tables are not present in the extract, pump and replicat parameter files. In order to view the results of the heartbeats Oracle has provided a heartbeat view and a heartbeat history view.

Step 1 Update the GLOBALS parameter file

Perform this operation on both the source and the target

[oracle@rob01db02 ~]$ cd $GG_HOME
[oracle@rob01db02 gghome_1]$ ./ggsci

GGSCI (rob01db01.localdomain) 2> edit param ./GLOBALS

Add the following entry:

GGSCHEMA GGADMIN
ENABLE_HEARBEAT_TABLE

Now stop and start all the goldengate processes for the parameter change to take effect:

GGSCI (rob01db01.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     PPRD12C     00:00:00      unknown
EXTRACT     RUNNING     XPRD12C     00:00:10      unknown


GGSCI (rob01db01.localdomain) 2> stop extract xprd12c
GGSCI (rob01db01.localdomain) 2> stop extract pprd12c
GGSCI (rob01db01.localdomain) 2> stop mgr
GGSCI (rob01db01.localdomain) 2> stop mgr
GGSCI (rob01db01.localdomain) 2> stop extract pprd12c
GGSCI (rob01db01.localdomain) 2> stop extract xprd12c

Now leave the ggsci prompt and start a new ggsci session.

Step 2 Add the heartbeat table

Perform this operation on both the source and the target.

  • The heartbeat works by the scheduler updating a record in the GGADMIN.GG_HEARTBEAT_SEED table which is replicated to the target system. You can see these by running stats against the extract and replicat processes.
  • By default, the heartbeat data is kept for 30 days and has an interval of 60 seconds. These values can be modified with the ALTER HEARTBEATTABLE command. In order to see the current values, use the INFO HEARTBEATTABLE command.
GGSCI (rob01db02.localdomain) 1> dblogin userid ggadmin password ggadmin
Successfully logged into database.

GGSCI (rob01db02.localdomain as ggadmin@trg12c) 2> add heartbeattable

2017-07-22 16:25:59  INFO    OGG-14001  Successfully created heartbeat seed table ["GG_HEARTBEAT_SEED"].

2017-07-22 16:25:59  INFO    OGG-14032  Successfully added supplemental logging for heartbeat seed table ["GG_HEARTBEAT_SEED"].

2017-07-22 16:25:59  INFO    OGG-14000  Successfully created heartbeat table ["GG_HEARTBEAT"].

2017-07-22 16:25:59  INFO    OGG-14033  Successfully added supplemental logging for heartbeat table ["GG_HEARTBEAT"].

2017-07-22 16:25:59  INFO    OGG-14016  Successfully created heartbeat history table ["GG_HEARTBEAT_HISTORY"].

2017-07-22 16:25:59  INFO    OGG-14023  Successfully created heartbeat lag view ["GG_LAG"].

2017-07-22 16:25:59  INFO    OGG-14024  Successfully created heartbeat lag history view ["GG_LAG_HISTORY"].

2017-07-22 16:25:59  INFO    OGG-14003  Successfully populated heartbeat seed table with [TRG12C].

2017-07-22 16:25:59  INFO    OGG-14004  Successfully created procedure ["GG_UPDATE_HB_TAB"] to update the heartbeat tables.

2017-07-22 16:25:59  INFO    OGG-14017  Successfully created procedure ["GG_PURGE_HB_TAB"] to purge the heartbeat history table.

2017-07-22 16:25:59  INFO    OGG-14005  Successfully created scheduler job ["GG_UPDATE_HEARTBEATS"] to update the heartbeat tables.

2017-07-22 16:26:00  INFO    OGG-14018  Successfully created scheduler job ["GG_PURGE_HEARTBEATS"] to purge the heartbeat history table.

The ADD HEARTBEAT command created the following database objects:

  • Tables:
    • <heartbeat_table>_SEED (default GG_HEARTBEAT_SEED)
    • <heartbeat_table> (default GG_HEARTBEAT)
    • <heartbeat_table>_HISTORY (default GG_HEARTBEAT_HISTORY)
  • Views:
    • GG_LAG
    • GG_LAG_HISTORY
  • Stored Procedures:
    • GG_UPDATE_HB_TAB
    • GG_PURGE_HB_TAB
  • Scheduler Jobs:
    • GG_UPDATE_HEARTBEATS
    • GG_PURGE_HEARTBEATS

Remark: If you are reconfiguring the heartbeattable, make sure all the related objects are removed first. Otherwise the following error will be displayed and all the heartbeattables will be dropped subsequentially:

2017-07-22 16:41:51  ERROR   OGG-14041  ["GG_UPDATE_HB_TAB"] exists already.
-- this is a stored procedure

You can check that the scheduler jobs are enabled with the following:

SQL> 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 '%HEARTBEATS%';

JOB_NAME                       START_DATE           LAST_START_DATE      NEXT_RUN_DATE        STATE           ENABL
------------------------------ -------------------- -------------------- -------------------- --------------- -----
GG_UPDATE_HEARTBEATS           22-JUL-2017 16:46:04 22-JUL-2017 19:26:05 22-JUL-2017 19:27:04 SCHEDULED       TRUE
GG_PURGE_HEARTBEATS            22-JUL-2017 16:46:04 22-JUL-2017 16:46:05 23-JUL-2017 16:46:04 SCHEDULED       TRUE

Step 3 View information about the heartbeattable

GGSCI (rob01db02.localdomain as ggadmin@trg12c) 3> info heartbeattable

HEARTBEAT table GGADMIN.gg_heartbeat exists.

HEARTBEAT table GGADMIN.gg_heartbeat_seed exists.

HEARTBEAT table GGADMIN.gg_heartbeat_history exists.

Frequency interval: 60 seconds.

Purge frequency interval: 1 days.

Retention time: 30 days.

Step 3 On source and target: check that the scheduler job is running

SYS>set linesize 200
SYS1>col job_name format a20
SYS@>select job_name, to_char(start_date,'DD-MON-HH24:MI:SS') start_date, to_char(last_start_date,'DD-MON-HH24:MI:SS') last_start_date,
to_char(next_run_date,'DD-MON-YYYY HH24:MI:SS') next_run_date from dba_scheduler_jobs where job_name ='GG_UPDATE_HEARTBEATS';
 
JOB_NAME             START_DATE               LAST_START_DATE          NEXT_RUN_DATE
-------------------- ------------------------ ------------------------ -----------------------------
GG_UPDATE_HEARTBEATS 20-JUL-15:17:12          26-JUL-10:21:14          26-JUL-2017 10:22:12

Step 4 Use the heartbeat tables to view lag information

This procedure can be done at the target database. You can either use the ggsci interface or the sqlplus interfac. I like the sqlplus interface better, but hey.. I am a DBA ...

Using the ggsci interface:

GGSCI> dblogin useridalias alias_ggadmin 
Successfully logged into database.
GGSCI (gfd-dm1dbadm01.atradiusnet.com as ggadmin@dggt01_1) 2> lag
Lag Information From Heartbeat Table
LAG                 AGE                 FROM       TO         PATH
8.27s               1m 7.70s            RAC01      DGGT01     XROB ==> PROB ==> RROBv

Using the sqlplus interface

col Lag(secs) format 999.9
col "Seconds since heartbeat" format 999.9
col "GG Path" format a32
col TARGET format a12
col SOURCE format a12
set lines 140

SYS@TRG12C>SELECT remote_database "SOURCE", local_database "TARGET", incoming_path "GG Path", incoming_lag "Lag(secs)", incoming_heartbeat_age "Seconds since heartbeat" FROM ggadmin.gg_lag;

SOURCE       TARGET       GG Path                          Lag(secs) Seconds since heartbeat
------------ ------------ -------------------------------- --------- -----------------------
PRD12C        TRG12C        XROB ==> PROB ==> RROB                9.2                    51.0
 
SYS@TRG12C>select remote_database "SOURCE", local_database "TARGET", to_char(heartbeat_received_ts,'DD-MON-YYYY HH24:MI:SS') heartbeat_received_ts, incoming_path "GG Path", incoming_lag "Lag(secs)", incoming_heartbeat_age "Seconds since heartbeat" 
from ggadmin.gg_lag_history order by heartbeat_received_ts;

SOURCE       TARGET       HEARTBEAT_RECEIVED_TS         GG Path                          Lag(secs) Seconds since heartbeat
------------ ------------ ----------------------------- -------------------------------- --------- -----------------------
PRD12C        TRG12C       26-JUL-2017 08:39:54           XROB ==> PROB ==> RROB               10.2                    13.3
PRD12C        TRG12C       26-JUL-2017 08:40:49           XROB ==> PROB ==> RROB                6.2                     7.9
PRD12C        TRG12C       26-JUL-2017 08:42:00           XROB ==> PROB ==> RROB                7.3                    19.1
PRD12C        TRG12C       26-JUL-2017 08:43:03           XROB ==> PROB ==> RROB                9.3                    21.4
PRD12C        TRG12C       26-JUL-2017 08:44:01           XROB ==> PROB ==> RROB                7.4                    19.3
PRD12C        TRG12C       26-JUL-2017 08:44:58           XROB ==> PROB ==> RROB                5.5                    17.2
PRD12C        TRG12C       26-JUL-2017 08:45:48           XROB ==> PROB ==> RROB                5.5                     7.2
PRD12C        TRG12C       26-JUL-2017 08:46:58           XROB ==> PROB ==> RROB                4.6                    16.3
PRD12C        TRG12C       26-JUL-2017 08:48:00           XROB ==> PROB ==> RROB                6.7                    18.6