Oracle Heartbeats (CDB Multi-Tenant)

Setup Oracle heartbeats for low and intermittent traffic databases

In low-traffic or intermittent databases, the log can become stale due to inactivity, which may cause the Connector to miss change events.

To prevent this, the heartbeat process — which runs within the Connector — periodically inserts or updates a record in the heartbeat table. This triggers a change event, keeping the logs active and ensuring that all changes are detected and captured by the Connector.

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

  • After a few minutes, look for entries with a recent timestamp in the heartbeat table to verify it's working