ADD HEARTBEATTABLE

Use ADD HEARTBEATTABLE to create the objects necessary to use the automatic heartbeat functionality. This command performs the following tasks

Creates a heartbeat seed table, heartbeat table, and heartbeat history table.
Creates the GG_LAG and GG_LAG_HISTORY views.
Creates the GG_UPDATE_HB_TAB andGG_PURGE_HB_TAB procedures that are called by the scheduler jobs.
Creates the scheduler jobs that periodically update the heartbeat and seed table, and purge the history table. However, it does not create these jobs for Teradata and TimesTen.
Populates the seed table.
(PostgreSQL) Creates the job table, job procedure along with heartbeat table or views to get the lag information. If TARGETONLY is specified, then no job table or procedure is created. NOTARGETONLY is not supported.

Note:

For Teradata, the NOTARGETONLY option is not supported. The options FREQUENCY, PURGE_FREQUENCY, and RETENTION_TIME are not supported for Teradata and PostgreSQL.

The default seed, heartbeat, and history table names are GG_HEARTBEAT_SEED, GG_HEARTBEAT, and GG_HEARTBEAT_HISTORY respectively. The tables, procedures and scheduler jobs are created in the GGSCHEMA mentioned in GLOBALS file.

In Classic Architecture:

The default names can be overridden by specifying HEARTBEATTABLE hbschemaname.hbtablename in the GLOBALS file.
The tables, procedures, and jobs are created in the schema, hbschemaname.
The seed and history table are created by appending a _SEED and _HISTORY to the table, hbtablename.

In Microservices Architecture, the schema is configured using step 12 provided in the How to Create Deployments section of the Step by Step Data Replication Using Oracle GoldenGate Microservices guide.

For Oracle, the ADD HEARTBEATTABLE has to run in every PDB where you want to generate heartbeats in CDB mode. On a database, the heartbeat tables are created inside each PDB. Use the DBLOGIN command to log into the PDB where you need to create the heartbeat table.

For DB2 LUW, you must set the DB2_ATS_ENABLE property with the db2set DB2_ATS_ENABLE=yes command.

For Amazon Aurora MySQL, the global variable event_scheduler must be enabled in the parameter group because Amazon RDS doesn't allow setting global variables. When the database is restarted, the event_scheduler returns to being disabled. To avoid this, you need to enable the event_scheduler in the my.cnf/ini file.

Admin Client Syntax

ADD HEARTBEATTABLE
[, FREQUENCY number_in_seconds]
[, RETENTION_TIME number_in_days] |
[, PURGE_FREQUENCY number_in_days]
[, PARTITIONED]
[, TARGETONLY]

GGSCI Syntax

ADD HEARTBEATTABLE
[, FREQUENCY number_in_seconds]
[, RETENTION_TIME number_in_days] |
[, PURGE_FREQUENCY number_in_days]
[, PARTITIONED]
[, TARGETONLY]

FREQUENCY number_in_seconds

Specifies how often the heartbeat seed table and heartbeat table are updated. For example, how frequently heartbeat records are generated. The default is 60 seconds.

Consider the following limits, when using DB/400, DB2 z/OS, and DB2 LUW:

The number_in_seconds must be between 0 and 7999. The special value of zero to pause heartbeat will be valid.
The frequency for DB2 /zOS and DB2 LUW must be a multiple of 60 for values less than 3600 and multiples for 3600 for values greater or equal to 3600.
Minimum frequency is 1 for DB2400 and 60 for DB2 /zOS and DB2 LUW.

For MySQL, the maximum frequency value is 7999. Minimum value is 0 and default value is 60 seconds.

For SQL Server, the maximum frequency value is 3600. Minimum value is 0 seconds and default value is 60 seconds.

For PostgreSQL, the maximum frequency value is 7999. Minimum value is 60 seconds and default value is 60 seconds. You need to create cron job to populate the heartbeat frequency. Here is an example of a cron job to run in 60 seconds:

PGPASSWORD="$passwd" psql -U postgres -d postgres -h localhost -p 5433 -c "select public.gg_hb_job_run();" >/dev/null 2>&1

Alternatively, you can run the batch script (ogg_hb_run.sh) available in the Oracle GoldenGate installation directory by providing database name, user, password, port, and server name, as shown in the following example:

./ ogg_hb_run.sh start user1 pword1 dbname1 serverName1 port1 oggschema

In this example, the values used are:

start: The command to start the job

user1: User ID to connect to the database

pword1: Password of the user

dbname1: The database name where the job will be running

serverName1: PostgreSQL server name

port1: Port where the database is listening

oggschema: Schema used in global file for creating the heartbeat objects

RETENTION_TIME number_in_days

Specifies when heartbeat entries older than the retention time in the history table are purged. The default is 30 days. For DB2/400, DB2 z/OS and DB2 LUW, the maximum retentition time is 31 days and values must be between 1 and 2147483646.

For MySQL, SQL Server, PostgreSQL, the maximum retenion time value is 2147483646 days. Minimum value is 1 day and the default value is 30 days.

PURGE_FREQUENCY number_in_days

Specifies how often the purge scheduler is run to delete table entries that are older than the retention time from the heartbeat history. The default is 1 day.

For GGSCI, this parameter must specify that the number_in_days must be in the interval 1 to 31 for DB2 LUW and DB2 for z/OS.

For MySQL, SQL Server, and PostgreSQL, the maximum purge frequency value is 199 days. Minimum and default value is 1 day.

PARTITIONED

Valid for Oracle.

Enables partitioning on the heartbeat history table. The column for the heartbeat time stamp received is used to partition the table with an interval of one day. By default the heartbeat history table is not partitioned.

TARGETONLY

Valid for Oracle Database, PostgreSQL, and SQL Server. Does not enable supplemental logging on both the heartbeat seed and heartbeat tables. It does not create a scheduler job for updating the heartbeat table.

Examples

The following command creates default heartbeat tables, procedures and jobs.

ADD HEARTBEATTABLE

The following command creates the heartbeat tables, procedures and jobs with custom frequency, retention time, and purge frequency.

ADD HEARTBEATTABLE, frequency 120, retention_time 10, purge_frequency 2

The following command creates the heartbeat tables, procedures and jobs with partitioning enabled in the heartbeat history table, and supplemental logging is not enabled in the heartbeat and heartbeat seed tables.

ADD HEARTBEATTABLE, partitioned, TARGETONLY

The following example shows adding a heartbeattable in PostgreSQL:

GGSCI> ADD HEARTBEATTABLE, FREQUENCY 10, PURGE_FREQUENCY 10, RETENTION_TIME 10