Streams Replication Heartbeat

Heartbeat table in a Streams replication is very useful, to track the status of the replication. Instead of going to all monitoring tables, it can give us a status at a glance.

We can use following example to implement Streams Heartbeat. Streams replication should already be configured to implement this code.

I’ve used SCOTT schema to create this table.

—— source site ——

create table heartbeat_monitor
(
source varchar2(10),
last_update timestamp default systimestamp
)
/

1. SOURCE column, will store the details of the global_name of the site.
2. LAST_UPDATE column, will store the last activity time.

To replicate the data, we’ll need a JOB to insert the required details.

scott@sourcedb> variable jobno number;

scott@sourcedb>
begin
dbms_job.submit(:jobno, ‘insert into scott.heartbeat_monitor (source) select global_name from global_name;’, sysdate, ‘sysdate+60/(60*60*24)’);
commit;
end;
/

Secondly, I’ve created another job to delete entries older than 2 days, from the heartbeat_monitor table, so that heartbeat_monitor table does not become a problem

scott@sourcedb>
variable jobno number;
begin
dbms_job.submit(:jobno, ‘delete from scott.heartbeat_monitor where last_update < sysdate -2 and source = (select global_name from global_name);’, sysdate, ‘sysdate+60/(60)’);
commit;
end;
/

If we have 2-way replication configured, then we’ll also need similar jobs on target site

—— target site ——

scott@targetdb> variable jobno number;

scott@targetdb>
begin
dbms_job.submit(:jobno, ‘insert into scott.heartbeat_monitor (source) select global_name from global_name;’, sysdate, ‘sysdate+60/(60*60*24)’);
commit;
end;
/

scott@targetdb> variable jobno number;

scott@targetdb>
begin
dbms_job.submit(:jobno, ‘delete from scott.heartbeat_monitor where last_update < sysdate -2 and source = (select global_name from global_name);’, sysdate, ‘sysdate+60/(60)’);
commit;
end;
/

Now to track the heartbeat, we can use following script

select source, to_char(systimestamp,’dd-mon-yyyy hh24:mi:ss.ff5′) as “current time”,
to_char(max(last_update),’dd-mon-yyyy hh24:mi:ss.ff5′) as “last heartbeat received”
from scott.heartbeat_monitor
where source (select global_name from global_name) group by source
/

Advertisements
This entry was posted in Oracle Replication and tagged . Bookmark the permalink.

4 Responses to Streams Replication Heartbeat

  1. GoldenGate says:

    Very clear and to the point. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s