Day: July 2, 2008

Oracle Streams: Recommendations for Heartbeat table

Here are some simple recommendations we thought be useful when implementing a streams heartbeat table in a bi-directional environment:

  • Oracle suggests adding a row every 1 minute to the heartbeat table. We recommend scheduling the job every 10-15 seconds which improves your probability of catching intermittent problems like buggy firewall or a faulty network cards.
  • Partition the table for easier maintenance. Dropping a partition is easier than deleting thousands of rows. Remember to create a job that adds new partitions.
  • Implement 1 table only in your streams environment, checking 1 table is easier than checking multiple tables.
  • Generate the primary key based on date easier than creating a sequence. Add site name or abbreviation to make the primary key unique globally.

Here is an example:

create table streams_heartbeat
(
id varchar2(20),
hb_time date,
constraint streams_heartbeat_pk primary key (id) using index tablespace users
) pctfree 0
partition by range(hb_time)
(
partition p_20080628 values less than (to_date(‘2008-06-29 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080629 values less than (to_date(‘2008-06-30 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080630 values less than (to_date(‘2008-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080701 values less than (to_date(‘2008-07-02 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’))
);

Site A:

begin
dbms_scheduler.create_job(
job_name => ‘streams_heartbeat_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into streams_heartbeat (id, hb_time) values (”A-” || to_char(sysdate, ”YYYYMMDDHH24MISS”), sysdate);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=secondly; interval=15’,
enabled => TRUE,
comments => ‘Streams heartbeat job from site A’);
end;
/

Site B:

begin
dbms_scheduler.create_job(
job_name => ‘streams_heartbeat_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into streams_heartbeat (id, hb_time) values (”B-” || to_char(sysdate, ”YYYYMMDDHH24MISS”), sysdate);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=secondly; interval=15’,
enabled => TRUE,
comments => ‘Streams heartbeat job from site B’);
end;
/

Hazem Ameen
Senior Oracle DBA