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/126.96.36.199; export ORACLE_HOME ORACLE_SID=mysid; export ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH; export PATH EMAILLISTfirstname.lastname@example.org,email@example.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 else # Feel free to comment this out if you don't want notifications when things are running normally echo "No CDC lags detected" echo "Subject: CDC Monitor - No Lags Detected" > $MONITORDIR/mail.txt mail $EMAILLIST < $MONITORDIR/mail.txt fi
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 < (SYSDATE-2); exit