Table Synchronization using DBMS_COMPARISON

Description: These processes will synchronize tables between two databases in different server. This is an example how to synchronize two tables between two databases in different server using DBMS_COMPARISON.

###############################################################
-- Secondary Database
###############################################################
-- As sys User
SQL> create user replication identified by replication;
SQL> grant create session,create table to replication;

SQL> alter user replication quota unlimited on USERS;       -- Choose quota and tablespace based on your database

-- As schema user (Here tmp_test_replocation)
CREATE TABLE departments
(
   id_department NUMBER CONSTRAINT pk_department PRIMARY KEY,
   name_department VARCHAR2(50)
) tablespace users;
 
INSERT INTO departments VALUES (1001,'Administration');
INSERT INTO departments VALUES (1002,'Marketing');
INSERT INTO departments VALUES (2001,'Purchasing');
INSERT INTO departments VALUES (2002,'Human Resources');
INSERT INTO departments VALUES (4001,'Shipping');
INSERT INTO departments VALUES (4002,'Public Relations');
 
commit;

###################################################################
-- Primary Database 

###################################################################
-- As sys User

SQL> create user replication identified by replication;
SQL> grant create session,create table,create procedure to replication;
GRANT EXECUTE ON dbms_comparison TO replication;
GRANT SELECT ON DBA_COMPARISON_SCAN TO replication;
GRANT SELECT ON DBA_COMPARISON TO replication;
SQL> grant create database link to replication;

alter user replication quota unlimited on USERS;           -- Choose quota and tablespace based on your database

-- As schema user (Here tmp_test_replocation)
CREATE TABLE departments
(
   id_department NUMBER CONSTRAINT pk_department PRIMARY KEY,
   name_department VARCHAR2(50)
);
 
INSERT INTO departments VALUES (1001,'Administration');
INSERT INTO departments VALUES (1002,'Marketing');
INSERT INTO departments VALUES (2001,'Purchasing');
INSERT INTO departments VALUES (2002,'Human Resources');
INSERT INTO departments VALUES (4001,'Shipping');
INSERT INTO departments VALUES (4002,'Public Relations');
 

commit;

CREATE DATABASE LINK XE_TABLES_SYN CONNECT TO replication IDENTIFIED BY replication USING 'STEGE_XE2';         -- Make sure you have enty in tnsnames.ora

BEGIN
   DBMS_COMPARISON.CREATE_COMPARISON
   (
      comparison_name => 'compare_departments',
      schema_name     => 'replication',
      object_name     => 'departments',
      dblink_name     => 'XE_TABLES_SYN'
   );
END;
/

-- This procedure will synchronize the department table if any DML operation perform in primary database. I have deleted scan information for comparison older than 1 month in this procedure so that it will not occupy more space in data dictionary.


CREATE OR REPLACE PROCEDURE DEPARTMENT_SYN AS
   consistent BOOLEAN;
   scan_info DBMS_COMPARISON.COMPARISON_TYPE;
   back_date date;
   lowest_date date;
BEGIN
   consistent := DBMS_COMPARISON.COMPARE
                 (
                    comparison_name  => 'compare_departments',
                    scan_info        => scan_info,
                    perform_row_dif  => TRUE
                 );
   DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
   DBMS_COMPARISON.CONVERGE
   (
      comparison_name    => 'compare_departments',
      scan_id            => scan_info.scan_id,
      scan_info          => scan_info,
      converge_options   => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS
   );
   DBMS_OUTPUT.PUT_LINE('Local Rows Merged:   '||scan_info.loc_rows_merged);
   DBMS_OUTPUT.PUT_LINE('Remote Rows Merged:  '||scan_info.rmt_rows_merged);
   DBMS_OUTPUT.PUT_LINE('Local Rows Deleted:  '||scan_info.loc_rows_deleted);
   DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);

   select min(to_char(last_update_time,'DD-MON-YY')) into lowest_date from user_comparison_scan;
   select sysdate-30 into back_date from dual;

   DBMS_OUTPUT.PUT_LINE('Date untill to be Deleted:   '||back_date);

   if lowest_date < back_date THEN
   DBMS_COMPARISON.PURGE_COMPARISON(
   comparison_name    => 'compare_departments',
   purge_time        => back_date);
   END if;

END;
/

################################################################################################################
-- Linux Script (You can set this script in cron so that the procedure will execute by cron e.g every hour)
################################################################################################################
-- File name "synchronize.sh"
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/bin/bash


SUBJECT="/home/oracle/sync_tables_scripts/synchronyzation.sh"
EMAIL="mohammad.nazmulhuda@netsize.com"

log() {
    logfile=/home/oracle/sync_tables_scripts/xe_synchronyzation.log
    echo "$(/bin/date) $@" >> $logfile
}

errexit() {
    log "$@"
    echo "$0: $@ "|/bin/mailx -s "$SUBJECT" "$EMAIL"
    exit 1
}

sqlplus=/oracle/app/oracle/product/11.2.0/xe/bin/sqlplus


log "$0 starts"

log "Executing Procedure"


$sqlplus -s replication/replication @execute_procedure.sql >>/home/oracle/sync_tables_scripts/xe_synchronyzation.log|| errexit "Could not execute consumer update"

log "$0 is finished"


-- File name "execute_procedure.sql"

set echo on
set pagesize 0
set verify off
set lines 32000
set trimspool on
set feedback off
set serveroutput on

WHENEVER SQLERROR EXIT SQL.SQLCODE

Execute REPLICATION.DEPARTMENT_SYN();

exit;

Comments