Oracle Heartbeats (CDB Multi-Tenant)

Setup Oracle heartbeats for low volume databases

For low volume Oracle sources using the Oracle LogMiner implementation (Streamkap's default), it is possible that the log becomes stale due to a lack of change events. As a result, events may not be captured by the Connector.

To address this, the heartbeat process involves writing an insert or update to a heartbeat table periodically to generate a change event.

Setup Heartbeats

-- Replace {...} placeholders as needed
ALTER SESSION SET CONTAINER={PDB};

-- Create the heartbeat table with id, text, and last_update fields
CREATE TABLE STREAMKAP_USER.STREAMKAP_HEARTBEAT (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    text VARCHAR2(4000),
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant permission to the Streamkap user
GRANT SELECT, INSERT, UPDATE ON STREAMKAP_USER.STREAMKAP_HEARTBEAT TO STREAMKAP_USER;

-- Grant necessary privileges on the table to the common user
GRANT SELECT, INSERT, UPDATE ON STREAMKAP_USER.STREAMKAP_HEARTBEAT TO C##STREAMKAP_USER;

-- Insert the first row into the heartbeat table
INSERT INTO STREAMKAP_USER.STREAMKAP_HEARTBEAT (text) VALUES ('test_heartbeat');

Verify

  • Look for entries in the heartbeat table with an updated timestamp after 5 minutes to verify this is working