Data Synchronization Monitoring

Case Study-1
Oracle Streaming between two remote site having 2 Node Oracle10g RAC in Solaris 10 platform

CREATE OR REPLACE PROCEDURE PROC_DB_MONITOR AS
SITEA_CNT NUMBER;
SITEB_CNT NUMBER;
DIFF_CNT NUMBER;
BEGIN

/* This Procedure is Created in SITEA Database for Monitor
   the data synchroization between SITEA Database and SITEB Database */

---For SITEA Database use DB Link.
SELECT COUNT(1) INTO SITEA_CNT FROM AAA.CUSTOMER@@DBLINKSITEA;

---For SITEB Database use DB Link
SELECT COUNT(1) INTO SITEB_CNT FROM AAA.CUSTOMER@DBLINKSITEB;

--Print the count of SITEA Database.
DBMS_OUTPUT.PUT_LINE('CNT OF SITEA SITE:=====>'||SITEA_CNT);

--Print the count of SITEB Database.
DBMS_OUTPUT.PUT_LINE('CNT OF SITEB SITE:=====>'||SITEB_CNT);

--Find the count different between SITEA and SITEB Database.
SELECT TO_NUMBER(SITEA_CNT) - TO_NUMBER(SITEB_CNT) INTO DIFF_CNT FROM DUAL;

--Print the count different between SITEA and SITEB Database.
DBMS_OUTPUT.PUT_LINE('DIFF OF SITEA AND SITEB SITE: <======> '||DIFF_CNT);

END;

#SCRIPT for database synchronization monitor and send mail

#Read the database environment variables.
. /export/home/oracle/.profile
#Connect to the database subdbdel
sqlplus aaa/******@SITEB <

cronjob

#This process should run every early morning between 5 am.
0 5 * * * /export/home/oracle/hitesh/SCRIPTS/dbalert.sh > /tmp/datasynch/err 2>&1

Case Study-2
Oracle Data Guard between two remote site having 2 Node Oracle11g RAC in Linux 6.4 platform

V$ARCHIVED_LOG
V$LOG_HISTORY
V$ARCHIVE_GAP

GRANT SELECT ON V_$ARCHIVED_LOG to reports;
GRANT SELECT ON V_$LOG_HISTORY to reports;
GRANT SELECT ON V_$ARCHIVE_GAP to reports;

—Primary Server(sqlplus reports/*******@prddb)

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM sys.V_$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

–Standby Server(sqlplus reports/********@stydb)

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM sys.V_$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM sys.V_$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM sys.V_$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM sys.V_$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM sys.V_$ARCHIVE_GAP;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s