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;