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;