Monitoring CDC Queues

There is no built in monitoring of CDC queues. This shell script and SQL query will allow you to alert on queues that are getting too long. If your queue grows over 2-3 days long then someone is probably not picking up their CDC changes. The lines highlighted in grey below should be updated for your environment. Create this file as cdcmon.sh and place in your monitor directory...
#!/bin/ksh

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.8; export ORACLE_HOME
ORACLE_SID=mysid; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
EMAILLIST='your.email.address.2@yourdomain.com,email.address.2@yourdomain.com'; export EMAILLIST

MONITORDIR=/u01/app/oracle/cdcmon; export MONITORDIR

rm $MONITORDIR/out.log
sqlplus -s "/ as sysdba" @$MONITORDIR/query.sql >> $MONITORDIR/out.log

if [ -s $MONITORDIR/out.log ]
then
  echo "CDC Lags Detected"
  echo "Subject: CDC Monitor - attention required" > $MONITORDIR/mail.txt
  cat $MONITORDIR/out.log >> $MONITORDIR/mail.txt
  mail $EMAILLIST  $MONITORDIR/mail.txt
  mail $EMAILLIST 

Create this file as query.sql and place in your monitor directory...
SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 3800
SET TRIMSPOOL ON
SET SPACE 0
SET PAGESIZE 0
SELECT Set_Name,
       ROUND(SYSDATE-LAST_PURGED,2) ||
       ' Days Since Last Purge' as DaysSinceLastPurge,
       (ROUND(SYSDATE-LAST_PURGED,2) * 24) ||
       ' Hours Since Last Purge' as HoursSinceLastPurge
FROM dba_subscriptions
WHERE STATUS = 'A'
AND LAST_PURGED