ADD HEARTBEATTABLE
Valid for Oracle, Db2 z/OS, Db2 LUW, Db2 for i, MySQL, PostgreSQL, SQL Server, Teradata and TimesTen.
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
andGG_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 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 theUSERID
orUSERIDALIAS
schema. When making the connection usingDBLOGIN
, make sure that it is set to the appropriateUSERID
orUSERIDALIAS
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. To instruct Extract which schema to find the heartbeat tables in, use the syntax
GGSCHEMA pdb.schemaname
within the GLOBALS file. -
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
andRBA
heartbeat tables must use the same schema. However,EAB
andRAB
can use different schemas.
-
-
Oracle GoldenGate for Oracle heartbeat table administration has been simplified by eliminating the need for
GGSCHEMA
(orHEARTBEATTABLE
parameter) except for limited circumstances. Heartbeat table administration operations are only done in the schema of theDBLOGIN
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 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 Database (ADB), user must beggadmin
.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 beggadmin
.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 beggadmin
.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.
-
The default names can be overridden by specifying
HEARTBEATTABLE
hbschemaname.hbtablename
in theGLOBALS
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 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.
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
Admin Client 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 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, with default being 60 seconds.
-
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.
-
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 Database, Db2 i Series, Db2 LUW, Db2 z/OS, MySQL, PostgreSQL, and SQL Server.
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