ADD HEARTBEATTABLE

Valid for all supported databases. See the Certification Matrix for details on supported databases.

This command requires a database login using DBLOGIN.

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 and GG_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 Azure SQL Database, PostgreSQL, Teradata, and TimesTen.
Populates the seed table.
For Oracle multitenant databases:
- The heartbeat objects and jobs are created in the user’s schema that is connected to the database using the DBLOGIN command. Oracle GoldenGate Extract and Replicat look for the heartbeat objects in the USERID or USERIDALIAS schema. When making the connection using DBLOGIN, make sure that it is set to the appropriate USERID or USERIDALIAS schema that your Extract and Replicat processes will use.
- Extract: Use the schema name in each PDB. Each PDB with which the Extract is registered should have its own heartbeat table.
- Replicat: Each Replicat must have its own heartbeat table for its PDB.
- For bidirectional, active/active replication, the heartbeat table should be in the same schema for the outgoing Extracts and incoming Replicats at each site. For example, see the following use case:
  
  Site A Site B
  
  EAB —————> ` RAB`
  
  RBA —————> ` EBA`
  
  In this example, EAB and RBA heartbeat tables must use the same schema. However, EAB and RAB can use different schemas.

Oracle GoldenGate for Oracle heartbeat table administration has been simplified by eliminating the need for GGSCHEMA (or HEARTBEATTABLE parameter) except for limited circumstances. Heartbeat table administration operations are only done in the schema of the DBLOGIN user. Except for Oracle CDB root Extract, the Extract and Replicat processes look in the schema of the ER connected user for heartbeat tables. The following table shows the Extract and Replicat behavior for Oracle AI Database:

Extract/Replicat Processes	Behavior
Non-root Extract (non-CDB, and PDB)	If it is the first Extract user, then `GGSCHEMA` is used or in case of Autonomous AI Database, user must be `ggadmin`. If heartbeat is created after Extract starts, then look only in Extract user.
CDB Root Extract	`GGSCHEMA` is used.
Replicat	If it is the first Replicat user, then `GGSCHEMA` is used. For ADB, user must be `ggadmin`. If heartbeat is created after Replicat starts, then look only in the Replicat user.
`DELETE EXTRACT/REPLICAT`	If it is the first Extract or Replicat user, then `GGSCHEMA` is used. For ADB, user must be `ggadmin`.

This feature allows the usage of heartbeat tables in Oracle GoldenGate Hub deployments, where multiple databases are managed with differing Oracle GoldenGate administrator schemas.

Note: The heartbeat table objects should never be created in the root CDB of an Oracle Multitenant Database.

For heterogeneous or non-Oracle databases, the heartbeat objects and jobs are created in the GGSCHEMA value listed in the GLOBALS file.

The default seed, heartbeat, and history table names are GG_HEARTBEAT_SEED, GG_HEARTBEAT, and GG_HEARTBEAT_HISTORY respectively.

In Microservices Architecture, the schema is configured using Replication Settings.

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.

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

For Db2 for i, to handle upgrade or misconfiguration of heartbeat table functionality, you can run the ADD HEARTBEATTABLE command again, which will repair the functionality of an exisiting heartbeattable setup without deleting the existing heartbeat data.

For Db2 for i, the licensed program, 5770SS1 Option 39 International Components for Unicode, must be installed.

For Db2 for i, the heartbeat table must be journaled to the same journal as the objects that are being replicated in the Extract using the said heartbeat table. If not, the Extract will abend indicating that more than one journal is available. In addition, any other Extract in the specific Oracle GoldenGate installation that is not reading the same journal cannot have the heartbeat table enabled for it.

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.

For PostgreSQL, a system job must manually be created to periodically call the heartbeat record update and history record purge function, gg_hb_job_run. For example, a cron job could be created that runs every minute. The function will check the actual heartbeat record update and purge frequency settings of the heartbeat configuration and only process operations within those boundaries:

PGPASSWORD="$passwd" psql -U *gguser* -d *dbname* -h *dbhostname* -p *dbport#* -c "select <ggschema.gg_hb_job_run();" >/dev/null 2>&1

For Azure SQL Database, only ADD HEARTBEATTABLE TARGETONLY is supported and because there is no SQL Server Agent with Azure SQL Database, the purge routine cannot be created. So, the users must periodically run the GG_PURGE_HB_TAB stored procedure to remove old heartbeat records.

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 frequently the heartbeat records are generated. The default is 60 seconds.

Consider the following limits:

For Oracle AI Database, the minimum value is 0 and the maximum is 7999.
For Db2 for i Series, the minimum value is 0 and the maximum is 7999.
For Db2 LUW and Db2 z/OS, the minimum value is 60 and the maximum is 7999.
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.
For MySQL, the minimum value is 0 and the maximum is 7999.
For SQL Server, the minimum value is 10 and the maximum is 7999.
For PostgreSQL, the minimum value is 60 and the maximum is 7999.
For Sybase, the minimum value is 1 minute (60 seconds) and maximum is 133 minutes (7999 seconds).
Databases that support setting FREQUENCY to 0 will pause the heartbeat record scheduler.

RETENTION_TIME number_in_days

Specifies that heartbeat entries older than the retention time in the heartbeat history table are purged. The default is 30 days.

The minimum value for all databases is 1 and the maximum is 2147483646.

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 table. The default is 1 day.

For Db2 LUW and Db2 z/OS, the minimum value is 1 and the maximum is 31.

For all other supported databases, the minimum value is 1 and the maximum value is 199.

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 AI Database, Db2 i Series, Db2 LUW, Db2 z/OS, MySQL, PostgreSQL, and SQL Server.

Does not enable supplemental logging on both the heartbeat seed and heartbeat tables and 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