Repgen Features
and Benefits
Repgen
Demo Available for download...
-- script name: repcheck
-- Features: Contains the sql commands for creating the replication_check
-- procedure. The replication_check procedure can be scheduled thru
-- an OEM job to check replication status and it will then send an email
-- if the table counts are off by more than 2 records. This procedure
-- is to be installed at a non-master def site, and is owned by the
-- repadmin account.
-- Copyright 2002 by .com Solutions Inc.
-- This output file was created by Repgen version 2.2.4 on Fri May 31
13:29:24 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
CREATE OR REPLACE PROCEDURE repadmin.replication_check
IS
-- The replication_check procedure checks the number of records
-- in replicated tables at each master site.
-- The replication_check procedure can be scheduled thru
-- an OEM job to check replication status and it will then send an email
-- if the table counts are off by more than 2 records. This procedure
-- is to be installed at the master def site, and is owned by the
-- repadmin account.
--
-- define the variables which will contain the count of records in each
table
v_customers_prod3 NUMBER := 0;
v_addresses_prod3 NUMBER := 0;
v_state_province_prod3 NUMBER := 0;
v_country_prod3 NUMBER := 0;
v_customers_prod5 NUMBER := 0;
v_addresses_prod5 NUMBER := 0;
v_state_province_prod5 NUMBER := 0;
v_country_prod5 NUMBER := 0;
-- define variables and cursors for deferror and other replication status
variables
v_deferror_prod3 NUMBER := 0;
v_deftrandest_prod3 NUMBER := 0;
v_deftran_prod3 NUMBER := 0;
v_2pc_pending_prod3 NUMBER := 0;
v_2pc_neighbors_prod3 NUMBER := 0;
-- cursor for storing deferror summary for prod3
CURSOR deferror_cur_prod3
IS
SELECT count(1) AS def_cnt,error_msg
FROM deferror@prod3
GROUP BY error_msg;
v_deferror_prod5 NUMBER := 0;
v_deftrandest_prod5 NUMBER := 0;
v_deftran_prod5 NUMBER := 0;
v_2pc_pending_prod5 NUMBER := 0;
v_2pc_neighbors_prod5 NUMBER := 0;
-- cursor for storing deferror summary for prod5
CURSOR deferror_cur_prod5
IS
SELECT count(1) AS def_cnt,error_msg
FROM deferror@prod5
GROUP BY error_msg;
-- difference between table counts
v_difference NUMBER :=0;
-- output text to be sent in email message
v_output_text VARCHAR2 (32000);
-- if error flag gets set, then at least one table is out of replication
sync
v_error_flag INTEGER :=0;
-- replicated tables should not be out of sync by more than this number
of records
v_max_limit CONSTANT INTEGER := 2;
mailhost VARCHAR2(50) := 'mail.dotcomsolutionsinc.net';
mail_conn utl_smtp.connection;
sender VARCHAR2(50) := 'dba\@dotcomsolutionsinc.net';
recipient1 VARCHAR2(100) := 'dba\@dotcomsolutionsinc.net';
recipient2 VARCHAR2(100) := 'dba2\@dotcomsolutionsinc.net';
BEGIN
-- ########## customers
SELECT COUNT(1) INTO v_customers_prod3
FROM widgets1.customers@prod3;
SELECT COUNT(1) INTO v_customers_prod5
FROM widgets1.customers@prod5;
v_difference := v_customers_prod3 - v_customers_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
-- ########## addresses
SELECT COUNT(1) INTO v_addresses_prod3
FROM widgets1.addresses@prod3;
SELECT COUNT(1) INTO v_addresses_prod5
FROM widgets1.addresses@prod5;
v_difference := v_addresses_prod3 - v_addresses_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
-- ########## state_province
SELECT COUNT(1) INTO v_state_province_prod3
FROM widgets1.state_province@prod3;
SELECT COUNT(1) INTO v_state_province_prod5
FROM widgets1.state_province@prod5;
v_difference := v_state_province_prod3 - v_state_province_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
-- ########## country
SELECT COUNT(1) INTO v_country_prod3
FROM widgets1.country@prod3;
SELECT COUNT(1) INTO v_country_prod5
FROM widgets1.country@prod5;
v_difference := v_country_prod3 - v_country_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
-- check for errors and deferred transactions not applied at each site
SELECT COUNT(1) INTO v_deferror_prod3 FROM deferror@prod3;
SELECT COUNT(1) INTO v_deftrandest_prod3 FROM deftrandest@prod3;
SELECT COUNT(1) INTO v_deftran_prod3 FROM deftran@prod3;
SELECT COUNT(1) INTO v_2pc_pending_prod3 FROM dba_2pc_pending@prod3;
SELECT COUNT(1) INTO v_2pc_neighbors_prod3 FROM dba_2pc_neighbors@prod3;
SELECT COUNT(1) INTO v_deferror_prod5 FROM deferror@prod5;
SELECT COUNT(1) INTO v_deftrandest_prod5 FROM deftrandest@prod5;
SELECT COUNT(1) INTO v_deftran_prod5 FROM deftran@prod5;
SELECT COUNT(1) INTO v_2pc_pending_prod5 FROM dba_2pc_pending@prod5;
SELECT COUNT(1) INTO v_2pc_neighbors_prod5 FROM dba_2pc_neighbors@prod5;
v_difference := v_deferror_prod3 - v_deferror_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
v_difference := v_deftrandest_prod3 - v_deftrandest_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
v_difference := v_deftran_prod3 - v_deftran_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
v_difference := v_2pc_pending_prod3 - v_2pc_pending_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
v_difference := v_2pc_neighbors_prod3 - v_2pc_neighbors_prod5;
IF ABS(v_difference) > v_max_limit THEN v_error_flag := 1; END IF;
-- if any of the tables are out of sync by more than the maximum allowed
- send email
IF v_error_flag > 0
THEN
-- build the email header so that subject line gets sent to recipients
v_output_text := 'Subject: prod3, prod5 Replication Status Report' ||
CHR(10);
v_output_text := v_output_text || ' Date: ' || TO_CHAR(SYSDATE, 'mm/dd/yyyy
HH24:MI:SS') || CHR(10);
v_output_text := v_output_text || ' From: dba\@dotcomsolutionsinc.net'
|| CHR(10);
v_output_text := v_output_text || ' To: dba\@dotcomsolutionsinc.net
, dba2\@dotcomsolutionsinc.net' || CHR(10);
-- concatenate all table counts together for output
v_output_text := v_output_text ||'Replication status output report for
sites: prod3, prod5 ' || TO_CHAR(SYSDATE, 'mm/dd/yyyy HH24:MI:SS') ||
CHR(10);
v_output_text := v_output_text || '-------------------------- customers'
|| CHR(10);
v_output_text := v_output_text || 'widgets1.customers@prod3 = ' || TO_CHAR(v_customers_prod3)
|| CHR(10);
v_output_text := v_output_text || 'widgets1.customers@prod5 = ' || TO_CHAR(v_customers_prod5)
|| CHR(10);
v_output_text := v_output_text || '-------------------------- addresses'
|| CHR(10);
v_output_text := v_output_text || 'widgets1.addresses@prod3 = ' || TO_CHAR(v_addresses_prod3)
|| CHR(10);
v_output_text := v_output_text || 'widgets1.addresses@prod5 = ' || TO_CHAR(v_addresses_prod5)
|| CHR(10);
v_output_text := v_output_text || '-------------------------- state_province'
|| CHR(10);
v_output_text := v_output_text || 'widgets1.state_province@prod3 = '
|| TO_CHAR(v_state_province_prod3) || CHR(10);
v_output_text := v_output_text || 'widgets1.state_province@prod5 = '
|| TO_CHAR(v_state_province_prod5) || CHR(10);
v_output_text := v_output_text || '-------------------------- country'
|| CHR(10);
v_output_text := v_output_text || 'widgets1.country@prod3 = ' || TO_CHAR(v_country_prod3)
|| CHR(10);
v_output_text := v_output_text || 'widgets1.country@prod5 = ' || TO_CHAR(v_country_prod5)
|| CHR(10);
-- concatenate replication status info together for output (for each
site)
v_output_text := v_output_text || '*************** prod3' || CHR(10);
v_output_text := v_output_text || 'deferror@prod3 = ' || TO_CHAR(v_deferror_prod3)
|| CHR(10);
v_output_text := v_output_text || 'deftrandest@prod3 = ' || TO_CHAR(v_deftrandest_prod3)
|| CHR(10);
v_output_text := v_output_text || 'deftran@prod3 = ' || TO_CHAR(v_deftran_prod3)
|| CHR(10);
v_output_text := v_output_text || '2pc_pending@prod3 = ' || TO_CHAR(v_2pc_pending_prod3)
|| CHR(10);
v_output_text := v_output_text || '2pc_neighbors@prod3 = ' || TO_CHAR(v_2pc_neighbors_prod3)
|| CHR(10);
v_output_text := v_output_text || '*************** prod5' || CHR(10);
v_output_text := v_output_text || 'deferror@prod5 = ' || TO_CHAR(v_deferror_prod5)
|| CHR(10);
v_output_text := v_output_text || 'deftrandest@prod5 = ' || TO_CHAR(v_deftrandest_prod5)
|| CHR(10);
v_output_text := v_output_text || 'deftran@prod5 = ' || TO_CHAR(v_deftran_prod5)
|| CHR(10);
v_output_text := v_output_text || '2pc_pending@prod5 = ' || TO_CHAR(v_2pc_pending_prod5)
|| CHR(10);
v_output_text := v_output_text || '2pc_neighbors@prod5 = ' || TO_CHAR(v_2pc_neighbors_prod5)
|| CHR(10);
-- create summary of deferror output errors at each site
v_output_text := v_output_text || '*************** deferror summary
for: prod3' || CHR(10);
FOR deferror_rec_prod3 IN deferror_cur_prod3
LOOP
v_output_text := v_output_text || TO_CHAR(deferror_rec_prod3.def_cnt)
|| ' = ' || deferror_rec_prod3.error_msg || CHR(10);
END LOOP;
v_output_text := v_output_text || '*************** deferror summary
for: prod5' || CHR(10);
FOR deferror_rec_prod5 IN deferror_cur_prod5
LOOP
v_output_text := v_output_text || TO_CHAR(deferror_rec_prod5.def_cnt)
|| ' = ' || deferror_rec_prod5.error_msg || CHR(10);
END LOOP;
-- put notes into email concerning how to interpret the results
v_output_text := v_output_text || CHR(10) || '#########################
Replication Status Notes ' || CHR(10);
v_output_text := v_output_text || 'Notes: ' || CHR(10);
v_output_text := v_output_text || 'deferror -The number of replication
errors which have occurred. ' || CHR(10);
v_output_text := v_output_text || 'deftrandest -The number of transactions
which have been pushed to other sites. ' || CHR(10);
v_output_text := v_output_text || ' (These transactions are purged from
deftrandest periodically.) ' || CHR(10);
v_output_text := v_output_text || ' (If deftrandest continues to grow,
check the status of the replication purge job.) ' || CHR(10);
v_output_text := v_output_text || 'deftran -The number of transactions
which have not yet been pushed to the other sites. ' || CHR(10);
v_output_text := v_output_text || ' (If deftran continues to grow, then
check the replication push job.) ' || CHR(10);
v_output_text := v_output_text || '2pc_pending -The number of distributed
transactions which have not completed at remote sites. ' || CHR(10);
v_output_text := v_output_text || ' (2pc_pending should always be 0.
The Oracle RECO process will attempt to correct these in-doubt transactions
the next time the database is started. Otherwise, Oracle Technical Support
needs to be called to provide a resolution.) ' || CHR(10);
v_output_text := v_output_text || '2pc_neighbors-The number of remote
transactions which are depending upon the completion of tranactions
in 2pc_pending. ' || CHR(10);
v_output_text := v_output_text || ' (2pc_neighbors should also be 0.
These transactions should clear automatically when their parent transactions
are resolved.) ' || CHR(10);
-- send email
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient1);
utl_smtp.rcpt(mail_conn, recipient2);
utl_smtp.data(mail_conn, v_output_text);
utl_smtp.quit(mail_conn);
END IF;
END replication_check;
/
