Can Replicate alert when CDC stops processing on a table in a task
Is it possible to have alerts triggered if a table's CDC stops running
Did a full load of a table, in a task on its own, but after the load it seems the CDC either did not start or it stopped shortly there after. Users noticed recent updates were not coming across to the target database. Determine the CDC was not processing. The task was running with out errors so looking at the Monitor screen of the task did not elude to any issues.
Are your concerns specifically about a specific table in a task, or CDC events for the task in general. Mind you, is a single table is 'stuck' mostly the whole replication is stuck looking for the next redo or waiting for space on target or similar.
You may want to put a monitoring of sorts in place. You can use Replicate REPCTL or better still Enterprise Managers API calls in a loop. Beyond the check for task status = 'RUNNING' also check for Latency; CDC event count ( cdc_transactions_counters:read_records_committed and similar, task_cdc_event_counters );
Personally I would look beyond Replicate in the target database. For example there may be a table which is known to get inserts of update every few second (or every few minutes, no matter). Check for some max(update_time) column comparing against last poll and current time.
You can use the optional attrep_history table to see if new records are arriving every few minutes, and what the even counts were.
The solution I like best is a 'ticker table' or 'heartbeat table' on the source(s) created for this purpose with a row inserted every minute. Columns might be just the local time, and optionall some comment test, maybe DB server name, possibly interesting DB server stats (CPU, Memory load). Next on the Replicate task, add columns for task name and replication time. Finally on the target DB add a column default to the current time.
With all that in place you can check end-to-end that rows are coming in every minute, 1440 per day, can you can 'see' the latency throughout the day without having to rely on anything Replicate. Just SQL calls to check the state of affairs and trigger alerts if need be. A single target table can handle multiple tasks/sources of course.