How to set up heartbeat tables (4256001)

Heartbeat table are setup to measure the time it takes for the record to reach from Source DB to Target DB through Shareplex Queues.

Here are the steps to setup Heartbeat tables.

NOTE: These steps stand valid assuming that the time stamp on Source and Target is the same.

On Source:

1. login to the Database as Shareplex DB user (OR can use any other Database user and make sure DDL replication is not turned ON during the process).
2. Drop the Hearbeat tables if they are already created.
drop table source_heartbeat;
3. Create a new Heartbeat table
create table source_heartbeat (testno number, source_time date default sysdate, target_time date);
4. Add a PK constraint on the source table.
alter table source_heartbeat add constraint pk_source_heartbeat primary key(testno);
5. Add a sequence on the source Heartbeat table.
create sequence s_heartbeat;

On Target:
1. login to the Database as Shareplex DB user (OR can use any other Database user and make sure DDL replication is not turned ON during the process).
2. Drop the Hearbeat tables if they are already created.
drop table target_heartbeat;
3. Create a new Heartbeat table
create table target_heartbeat (testno number, source_time date, target_time date default sysdate);
4. Add a PK constraint on the table.
alter table target_heartbeat add constraint pk_target_heartbeat primary key(testno);

After creating the tables, add the tables to replication.

in config file,
SPLEX.SOURCE_HEARTBEAT SPLEX.TARGET_HEARTBEAT <targetserver>@o.<Target SID>

In the above syntax, you can also add queue names if you want to set the hearbeat tables and measure the time lag for individual queues. Make sure the table names above differ for each queue to differentiate the data

Insert the data into the Source Hearbeat table. You can insert through sqlplus or cron in a particular interval and measure the time lag.

On Source:
Insert into source_heartbeat (testno) values (s_heartbeat.nextval); commit;

You can check the latency by using the following query on the target.

select testno, to_char(source_time,'YYYY/MM/DD:hh24:mi:ss')"Insert Time",((target_time-source_time)*24*60*60) "Delay in Seconds" from target_heartbeat;

You should get output something like
TESTNO Insert Time Delay in Seconds
---------- ------------------- ----------------
58 2004/12/15:11:27:48 1
59 2004/12/15:11:27:50 0
60 2004/12/15:11:27:50 2
61 2004/12/15:11:27:50 2
62 2004/12/15:11:27:51 1
63 2004/12/15:11:27:51 1