在前一篇文章已经简单讲述了ogg12.2之前版本的心跳表配置，下面我们演示下12.2版本之后的配置方法。
12.2 之后的版本配置
12.2版本之后，在GGSCI中ADD HEARTBEATTABLE即可，会自动创建心跳配置表和视图，所有OGG进程默认每60秒更新一次数据。 该命令执行以下任务：
创建心跳种子表，心跳表和心跳历史记录表。
创建GG_LAG和 GG_LAG_HISTORY视图。
创建由调度程序作业调用的GG_UPDATE_HB_TAB和 GG_PURGE_HB_TAB过程。
创建调度程序作业，这些作业会定期更新心跳和种子表，并清除历史记录表。
填充种子表。
默认的心跳种子表，心跳表和心跳历史记录表名字是GG_HEARTBEAT_SEED, GG_HEARTBEAT, 和 GG_HEARTBEAT_HISTORY。表、存储过程和作业是在GLOBALS文件中提到的GGSCHEMA中创建的。当然也可以通过在GLOBALS文件中指定HEARTBEATTABLE hbschemaname.hbtablename来覆盖默认名称。在本例中，表、存储过程和作业是在用户hbschemaname下创建的。通过向表hbtablename添加_SEED和_HISTORY来创建种子表和历史表。 其中一个视图是GG_LAG，里面有一个字段Incoming_LAG，可以看到本地DB提交时间和远端DB投递时的延时；同时，也支持双向复制，里面有一个outGoing_Lag字段，可以看到源DB产生心跳时间及目标DB接收到心跳时间的时间差。GG_HEARTBEAT是主要的配置表，其它视图基本上根据此表创建，里面有记录各组件的延迟信息，包括抽取、传输和投递进程。因此，很容易通过分析此表的信息就可以知道性能瓶颈在哪。历史延迟和心跳信息保存在GG_LAG_HISTORY和GG_HEARTBEAT_HISTORY表中。
1.ADD HEARTBEATTABLE
语法：
ADD HEARTBEATTABLE [, FREQUENCY number in seconds] [, RETENTION_TIME number in days] | [, PURGE_FREQUENCY number in days]
FREQUENCY 指定心跳种子表和心跳表的更新频率。例如，心跳记录的生成频率。默认值是60秒。 RETENTION_TIME 指定何时清除历史表中超过保留时间的心跳表项。默认为30天。 PURGE_FREQUENCY 指定执行清除调度器以从心跳历史记录中删除比保留时间更早的表项的频率。默认值是1天。 本案例我们执行如下（每120s更新一次心跳表，保留10天，每2天清理一次） 源端执行
GGSCI (rac1 as ogg123@test1) 55> ADD HEARTBEATTABLE, frequency 120, retention_time 10, purge_frequency 2 2024-11-21 15:02:16 INFO OGG-14001 Successfully created heartbeat seed table ""ogg123"."GG_HEARTBEAT_SEED"". 2024-11-21 15:02:17 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table ""ogg123"."GG_HEARTBEAT_SEED"". 2024-11-21 15:02:17 INFO OGG-14000 Successfully created heartbeat table ""ogg123"."GG_HEARTBEAT"". 2024-11-21 15:02:17 INFO OGG-14033 Successfully added supplemental logging for heartbeat table ""ogg123"."GG_HEARTBEAT"". 2024-11-21 15:02:17 INFO OGG-14016 Successfully created heartbeat history table ""ogg123"."GG_HEARTBEAT_HISTORY"". 2024-11-21 15:02:17 INFO OGG-14086 Successfully disabled partitioning for heartbeat history table ogg123.gg_heartbeat_history. 2024-11-21 15:02:17 INFO OGG-14023 Successfully created heartbeat lag view ""ogg123"."GG_LAG"". 2024-11-21 15:02:17 INFO OGG-14024 Successfully created heartbeat lag history view ""ogg123"."GG_LAG_HISTORY"". 2024-11-21 15:02:17 INFO OGG-14003 Successfully populated heartbeat seed table with "TEST". 2024-11-21 15:02:17 INFO OGG-14004 Successfully created procedure ""ogg123"."GG_UPDATE_HB_TAB"" to update the heartbeat tables. 2024-11-21 15:02:17 INFO OGG-14017 Successfully created procedure ""ogg123"."GG_PURGE_HB_TAB"" to purge the heartbeat history table. 2024-11-21 15:02:18 INFO OGG-14005 Successfully created scheduler job ""ogg123"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables. 2024-11-21 15:02:18 INFO OGG-14018 Successfully created scheduler job ""ogg123"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.
目标端执行
先登录pdb GGSCI (rm-bp1d69973r39876n9ko.mysql.rds.aliyuncs.com) 1>dblogin userid c##ogg123@testpdb password ogg123 添加心跳 GGSCI (rm-bp1d69973r39876n9ko.mysql.rds.aliyuncs.com as c##ogg123@orcl/TESTPDB) 16> ADD HEARTBEATTABLE, frequency 120, retention_time 10, purge_frequency 2 2024-11-21 15:05:24 INFO OGG-14001 Successfully created heartbeat seed table ""c##ogg123"."GG_HEARTBEAT_SEED"". 2024-11-21 15:05:24 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table ""c##ogg123"."GG_HEARTBEAT_SEED"". 2024-11-21 15:05:24 INFO OGG-14000 Successfully created heartbeat table ""c##ogg123"."GG_HEARTBEAT"". 2024-11-21 15:05:25 INFO OGG-14033 Successfully added supplemental logging for heartbeat table ""c##ogg123"."GG_HEARTBEAT"". 2024-11-21 15:05:25 INFO OGG-14016 Successfully created heartbeat history table ""c##ogg123"."GG_HEARTBEAT_HISTORY"". 2024-11-21 15:05:25 INFO OGG-14086 Successfully disabled partitioning for heartbeat history table c##ogg123.gg_heartbeat_history. 2024-11-21 15:05:25 INFO OGG-14023 Successfully created heartbeat lag view ""c##ogg123"."GG_LAG"". 2024-11-21 15:05:25 INFO OGG-14024 Successfully created heartbeat lag history view ""c##ogg123"."GG_LAG_HISTORY"". 2024-11-21 15:05:25 INFO OGG-14003 Successfully populated heartbeat seed table with "ORCL:TESTPDB". 2024-11-21 15:05:26 INFO OGG-14004 Successfully created procedure ""c##ogg123"."GG_UPDATE_HB_TAB"" to update the heartbeat tables. 2024-11-21 15:05:26 INFO OGG-14017 Successfully created procedure ""c##ogg123"."GG_PURGE_HB_TAB"" to purge the heartbeat history table. 2024-11-21 15:05:26 INFO OGG-14005 Successfully created scheduler job ""c##ogg123"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables. 2024-11-21 15:05:26 INFO OGG-14018 Successfully created scheduler job ""c##ogg123"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.
在源端和目标端执行后，可以看到前面我们介绍的3张表，2个视图，以及存储过程和job都创建了。
检查心跳配置好后的结果我们可以看到如下，
源端：
实际只有GG_HEARTBEAT_SEED表有数据，GG_HEARTBEAT和GG_HEARTBEAT_HISTORY表都是无数据的。（和12.2之前手工配置类似，源端只用到一张表）
目标端：
GG_HEARTBEAT_SEED表有本地环境的一条记录，基本不关注
GG_HEARTBEAT表有且只有最新一条心跳记录
GG_HEARTBEAT_HISTORY有所有的心跳历史记录（可以看到是上面设置的2分钟一次）
检查延迟，目标端查看
12.2之后的心跳表配置就简单完成，只需要源端和目标端执行ADD HEARTBEATTABLE名令即可，非常简单。有兴趣的可以进一步查看创建的那些存储过程和job的内容。
GG_UPDATE_HB_TAB存储过程
PROCEDURE GG_UPDATE_HB_TAB IS BEGIN LOCK TABLE c##ogg123.gg_heartbeat IN EXCLUSIVE MODE; UPDATE c##ogg123.gg_heartbeat SET HEARTBEAT_TIMESTAMP = SYS_EXTRACT_UTC(SYSTIMESTAMP); UPDATE c##ogg123.gg_heartbeat_seed SET HEARTBEAT_TIMESTAMP = SYS_EXTRACT_UTC(SYSTIMESTAMP); DELETE FROM c##ogg123.gg_heartbeat C WHERE C.OUTGOING_EXTRACT IS NULL AND EXISTS (SELECT 1 FROM c##ogg123.gg_heartbeat A WHERE A.REMOTE_DATABASE = C.REMOTE_DATABASE AND A.INCOMING_EXTRACT = C.INCOMING_EXTRACT AND ((A.INCOMING_ROUTING_PATH = C.INCOMING_ROUTING_PATH) OR (A.INCOMING_ROUTING_PATH IS NULL AND C.INCOMING_ROUTING_PATH IS NULL)) AND A.INCOMING_REPLICAT = C.INCOMING_REPLICAT AND A.OUTGOING_EXTRACT IS NOT NULL); COMMIT WRITE IMMEDIATE NOWAIT; END;
GG_PURGE_HB_TAB存储过程
PROCEDURE GG_PURGE_HB_TAB IS BEGIN DELETE FROM c##ogg123.gg_heartbeat_history WHERE INCOMING_REPLICAT_TS <= TRUNC(SYSDATE - 10); COMMIT; END;
GG_UPDATE_HEARTBEATS调度任务
select owner,job_name,job_type,job_action,repeat_interval,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'),to_char(next_run_date,'yyyy-mm-dd hh24:mi:ss') from dba_scheduler_jobs WHERE job_NAME='GG_UPDATE_HEARTBEATS' AND owner='C##OGG123';
GG_PURGE_HEARTBEATS调度任务
select owner,job_name,job_type,job_action,repeat_interval,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'),to_char(next_run_date,'yyyy-mm-dd hh24:mi:ss') from dba_scheduler_jobs WHERE job_NAME='GG_PURGE_HEARTBEATS' AND owner='C##OGG123';
2.ALTER HEARTBEATTABLE
使用ALTER heartbeatable来修改现有的种子、心跳和历史表选项，这些选项是用ADD heartbeatable设置的。 该命令需要先DBLOGIN。对于CDB数据库，需要PDB登录。 语法：
ALTER HEARTBEATTABLE [, FREQUENCY number in seconds] [, RETENTION_TIME number in days] | [, PURGE_FREQUENCY number in days]
可以修改心跳表的更新频率，保留时间以及清理策略。
3.DELETE HEARTBEATTABLE
使用DELETE heartbeatable删除表、过程、调度程序和视图。该命令需要DBLOGIN。对于CDB数据库，需要PDB登录。
DELETE HEARTBEATTABLE group_name
group_name为要清理的进程的名称。 ogg12.2之后的版本配置心跳表还是相当简单的，只需熟悉上面哪些命令即可配置成功。
