2.6 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
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 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, theNOTARGETONLY
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.
-
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
.
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 jobuser1
: User ID to connect to the databasepword1
: Password of the userdbname1
: The database name where the job will be runningserverName1
: PostgreSQL server nameport1
: Port where the database is listeningoggschema
: 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
Parent topic: Common Command Line Interface Commands