Implementing Streams Heartbeat Table

Heartbeat table in a Streams replication is especially useful for databases that have a low activity rate because you can make sure the replication environment is working properly even if there are not many replicated changes. It ensures that changes are being replicated in a Streams replication environment.

Prerequisites:

Create a table at the source site that includes a date or timestamp column and the global name of the database.

Add a rule to capture changes to this table and propagate the changes to each target destination.

Make sure that the target destination will apply changes to this table as well.

Set up an automated job to update this table at the source site periodically, for example every minute.

Following is an example configuration we could use as a heartbeat table.

--Create a table at the source database.
--Enable Supplemental logging for the table.
--Instantiate the table.
--Add this table to capture,apply,propagation rules.
--Configure an automated job to update the heartbeat.

create table SCOTT.HEARTBEAT(a number primary key, b date);
create sequence HEART_SEQ start with 1;

-- TO SUBMIT A JOB THAT RUN AN INSERT EVERY 60 SECONDS
variable jobno number;
begin
dbms_job.submit(:jobno,'insert into SCOTT.HEARTBEAT values (HEART_SEQ.nextval, sysdate);',
sysdate, 'sysdate+60/(60*60*24)');
commit;
end;
/

-- Add this table to the streams configuration