Here I am going to setup a real time table data synchronization between two Oracle Databases located in different locations. We are using here the SymmetricDS community version for data replication, SymmetricDS is open source software that is free to use. It includes command line tools, documentation, and example configuration files for getting setup and running. The community provides support through the project website, forums, and issue tracker.

Environment details

We will install SymmetricDS on two servers One on production and other on target. One will represent the Source server and one will represent the Target server. Each installed copy of SymmetricDS will be responsible for one database, and thus each copy acts as a single “node” in SymmetricDS terminology. This is the most common configuration of SymmetricDS – one installed copy of the software is responsible for one single database and represents one node.

Download Latest version of the symmetricds from the symmetricds website https://www.symmetricds.org/

INSTALLATION STEPS

On source(Production) , I have copied the symmetricsds zip file on /u04 and on target to /home/oracle/. After copying the binary login to servers as Oracle user and execute below commands
On Source -> Production DB with 12c EE
cd /u04/
unzip symmetric-server-3.12.1.zip
chown -R oracle:oinstall /u04/symmetric-server-3.12.1
chmod 775 /u04/symmetric-server-3.12.1

On Target -> Reporting DB server with 18c XE
mkdir -p /home/oracle/symds/
cd /home/oracle/symds/
unzip symmetric-server-3.12.1.zip
chown -R oracle:oinstall /home/oracle/symds/symmetric-server-3.12.1
chmod 775 /home/oracle/symds/symmetric-server-3.12.1

on Source we need mainly look in to two configuration files symmetric-server.properties & sym_service.conf, files located under conf folder on symetricds location. I have used the default parameter configuration and copied sample engine property from sample folder and create a source-000.properties under engines folder

ls -lrth

[oracle@MRCPRDBI-ORCL01 ~]$ cd /u04/symmetric-server-3.12.1/engines/
Key configuration parameters

db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@10.111.111.191:1521:mrcprdb
db.user=crm01
db.password=crm01
registration.url=http://10.111.111.191:31415/sync/source-000
sync.url=http://10.111.111.191:31415/sync/source-000
group.id=source
external.id=000

Full source-000.properties

On target we will create file dest-001.properties under engines directory

Key parameter for dest-001.properties

db.url=jdbc:oracle:thin:@10.121.121.191:1521:XE
db.user=crm
db.password=crm
registration.url=http://10.111.111.191:31415/sync/source-000
sync.url=http://10.121.121.191:31415/sync/dest-001
group.id=dest
external.id=001

On Production Source database

create the SymmetricDS-specific tables in the Production database. These tables will contain the configuration for synchronization. The following command uses the auto-creation feature to create all the necessary SymmetricDS system tables. thie command is required to be run only on production server.

cd /u04/symmetric-server-3.12.1/bin
./symadmin -engine source-000 create-sym-tables

Register target db from Source

./symadmin -engine source-000 open-registration dest 001

For initial load we are updating below tables update SYM_NODE_SECURITY set initial_load_enabled=1 where node_id=001;
update SYM_NODE set sync_enabled=1 where node_id=001;
commit;

Pr e-requiste table structure has to be there

Login to CRM01 user, create dummy table for replication
““““““““““““““““““““““““““`
sqlplus CRM01
create table TEST1 (a1 number, desc01 varchar2(100));
create table TEST2 (a1 number, desc01 varchar2(100));
create table TEST3 (a1 number, desc01 varchar2(100));
insert into TEST1 values(1,’das’);
insert into TEST1 values(2,’faheem’);
insert into TEST1 values(3,’shibin’);
insert into TEST2 values(2,’dev’);
insert into TEST3 values(3,’plato’);
commit;

symmetricds table configuration

The below script need to be executed for the data syncronization configuration between source to target. On target we will only create the table strucutre and initial load of data will happen to target once we start the service on source and target

insert into sym_node_group_link (source_node_group_id, target_node_group_id,data_event_action) values (‘source’, ‘dest’, ‘P’);
insert into sym_channel (channel_id, processing_order, max_batch_size,enabled, description) values(‘ho_sync_capricon’, 1, 100000, 1, ‘outbound channel’);
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values (‘outbound_tr_t1’, ‘TEST1′,’ho_sync_capricon’,sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values (‘outbound_tr_t2’, ‘TEST2′,’ho_sync_capricon’,sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values (‘outbound_tr_t3’, ‘TEST3′,’ho_sync_capricon’,sysdate,sysdate,1);
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values(‘ho-capricon’, ‘source’, ‘dest’, ‘default’,current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values(‘outbound_tr_t1′,’ho-capricon’, 1, current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values(‘outbound_tr_t2′,’ho-capricon’, 1, current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values(‘outbound_tr_t3′,’ho-capricon’, 1, current_timestamp, current_timestamp);

On Target login to CRM user and create table structure for replication
sqlplus crm/crm
create table TEST1 (a1 number, desc01 varchar2(100));
create table TEST2 (a1 number, desc01 varchar2(100));
create table TEST3 (a1 number, desc01 varchar2(100));
commit;

START SYMMETRICDS

• Source Start service and then on target from Oracle OS user
  cd /u04/symmetric-server-3.12.1/bin
  nohup ./sym > /u04/symmetric-server-3.12.1/sym.log 2>&1 &
  To monitor the symmetricds status you can tail the log file found on source location
  (/u04/symmetric-server-3.12.1/log)
  tail -f ../logs/symmetric.log

• Target Start service and then on target from Oracle OS user
  cd /home/oracle/symds/symmetric-server-3.12.1/bin
  ./sym_service start
  tail -f ../logs/symmetric.log
  –on source check table sym_node updated with correct url

CHECK THE DATA SYNC STATUS

Query the tables login to CRM user on target and check table data has been replicated from source to target. Can insert some data on Source test tables and check the data getting replicated on Target

CREATE DB LINK FOR SYNC MAIL REPORTING
To monitor the status of sync between source and target we created a dblink on source.
CREATE DATABASE LINK xetrg CONNECT TO crm IDENTIFIED BY crm USING ‘XE’;

Create SQL FILE for monitoring the Data sync Status
We created scripts to monitor the database sync status for the replicated tables,
in our case test1, test2 and test3 tables. We will also configure cronjob to trigger
a mail on hourly basis to know the sync status

vi /home/oracle/scripts/rep_check.sql
SET SERVEROUTPUT Off
SET DEFINE OFF
set pagesize 100
set linesize 150
–set heading off
Set Newpage none
column Table_name format a30
column HO format 999999999999
column CRM_Trg format 999999999999
column Difference format 999999999999
spool /home/oracle/scripts/dailymonitordb.log
select ” from dual;
select ‘TEST1’ Table_name, (select count() from TEST1) HO, (select count() from TEST1@xetrg) CRM_Trg, (select count() from TEST1)-(select count() from TEST1@xetrg)Difference from dual union all
select ‘TEST2’ Table_name, (select count() from TEST2) HO, (select count() from TEST2@xetrg) CRM_Trg, (select count() from TEST2)-(select count() from TEST2@xetrg)Difference from dual union all
select ‘TEST2_PRV’ Table_name, (select count() from TEST2_PRV) HO, (select count() from TEST2_PRV@xetrg) CRM_Trg, (select count() from TEST2_PRV)-(select count() from TEST2_PRV@xetrg)Difference from dual;
select ” from dual;
exit

[oracle@MRCPRDBI-ORCL01 ~]$ cat /home/oracle/scripts/dailymonitordbdr.sh
cd /home/oracle/scripts/
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=MRCPRDBI-ORCL01
export ORACLE_UNQNAME=mrcprdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=mrcprdb
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export PATH=$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
echo “Below Replication Symetric status” >> dailymonitordb.log
sqlplus crm01/crm01 @/home/oracle/scripts/rep_check.sql
echo “” >> dailymonitordb.log
mail -s “MRCPRDBI Data sync monitoring” -v -r donotreply@MRCPRDBI.com oracleprolab@gmail.com < dailymonitordb.log
exit

[oracle@MRCPRDBI-ORCL01 ~]$ chmod 775 /home/oracle/scripts/dailymonitordbdr.sh
[oracle@MRCPRDBI-ORCL01 ~]$ chmod 775 /home/oracle/scripts/rep_check.sql

Schedule cronjob to run mail alert hourly
[oracle@MRCPRDBI-ORCL01 ~]$ crontab -l
0 * * * * /home/oracle/scripts/dailymonitordbdr.sh

Sample mail Alert

************************Below Replication Symetric status***************

‘Test1’ CRM01 XETRG DIFFERENCE
Test1 327183 327183 0
Test2 22297 22297 0
Test3 384438 384438 0