Oracle Distributed Systems

Your Replicated Environment

Once you have a replicated system up and running, you need to monitor it to ensure that you detect problems early. The most important things to monitor are:

• The number of transactions queued at each originating site

• The number of unresolved errors at each destination site

• The number of entries in each snapshot log

If any of these three counts becomes too high, it can be very time-consuming to recover. By the same token, all are usually easy to correct if you catch them early.

Monitoring Queued Transactions

The following SQL query returns the number of deferred transactions that are currently queued:

SELECT  count(*)
FROM    deftrandest d, deftran t
WHERE   d.deferred_tran_id      = t.deferred_tran_id
AND     d.delivery_order        = t.delivery_order;

This query has been incorporated into a Unix shell script, checklatency , shown here, which sends email to the DBA when the number of deferred transactions exceeds 150:

#! /bin/ksh #-------------------------------------------------------------------------- # Filename: checklatency # Purpose: Notifies the dba when more than 150 replicated transactions # are queued. # Author: Chas. Dye ([e-mail]) # Date: 21-Oct-1998 # Remarks: Requires OPS$ account for whichever OS user crons this script. #-------------------------------------------------------------------------- HOST=`/bin/uname -n` MAIL=/bin/mailx DISTLIST="[e-mail]" export HOST MAIL DISTLIST # ORACLE_HOME=/u/oracle/product/8.0.4.2 ; export ORACLE_HOME ORACLE_SID=PHQS ...