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
| Source | Target | |
| Instance Type | Production | Reporting |
| Oracle Database version | Oracle 12c EE | Oracle 18c XE |
| DB Schema Name | CRM01 | CRM |
| Operating System | Oracle Linux 7.3 | Oracle Linux 7.8 |
| Symetric DS Version | SymmetricDS 3.12.1 | SymmetricDS 3.12.1 |
| Symetric DS Location | /u04/symmetric-server-3.12.1/ | /home/oracle/symds/symmetric-server-3.12.1 |
| IP Address | 10.111.111.191 | 10.121.121.191 |
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 conf]$ pwd /u04/symmetric-server-3.12.1/conf [oracle@MRCPRDBI-ORCL01 conf]$ cat symmetric-server.properties # Licensed to JumpMind Inc under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. JumpMind Inc licenses this file to you under the GNU General Public License, version 3.0 (GPLv3) (the “License”); you may not use this file except in compliance with the License. # You should have received a copy of the GNU General Public License, version 3.0 (GPLv3) along with this library; if not, see http://www.gnu.org/licenses/. # Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. # Specify the hostname/IP address to bind to. (Default 0.0.0.0 will bind to all interfaces.) # host.bind.name=0.0.0.0 Enable synchronization over HTTP. # http.enable=true Port number for synchronization over HTTP. # http.port=31415 Enable synchronization over HTTPS (HTTP over SSL). # https.enable=false Enable HTTPS/2 for multiplexing and resistance to protocol attacks. # https2.enable=false Port number for synchronization over HTTPS (HTTP over SSL). # https.port=31417 Use a trust manager that allows self-signed server SSL certificates. # https.allow.self.signed.certs=true List host names that are allowed for server SSL certificates. # https.verified.server.names=all auto.registration = true Accept cookies if load balancer requires it for clustering # server.http.cookies.enabled=false |
| cat sym_service.conf #***************************** # Wrapper Java Properties #***************************** # Java Runtime Executable wrapper.java.command=java #Java Classpath wrapper.java.classpath.1=patches wrapper.java.classpath.2=patches/* wrapper.java.classpath.3=lib/.jar wrapper.java.classpath.4=web/WEB-INF/lib/.jar # Java Additional Parameters wrapper.java.additional=-Dlog4j2.configurationFile=file:conf/log4j2.xml wrapper.java.additional=-Djava.util.logging.config.file=conf/logging.properties wrapper.java.additional=-Dsym.keystore.file=security/keystore wrapper.java.additional=-Djavax.net.ssl.trustStore=security/cacerts wrapper.java.additional=-Djavax.net.ssl.keyStorePassword=obf:cHVuYXRydmc= wrapper.java.additional=-Dorg.eclipse.jetty.server.Request.maxFormContentSize=800000 wrapper.java.additional=-Dorg.eclipse.jetty.server.Request.maxFormKeys=100000 wrapper.java.additional=-Djava.io.tmpdir=tmp wrapper.java.additional=-Dsun.net.client.defaultReadTimeout=300000 wrapper.java.additional=-Dsun.net.client.defaultConnectTimeout=300000 wrapper.java.additional=-Dfile.encoding=utf-8 wrapper.java.additional=-XX:+HeapDumpOnOutOfMemoryError wrapper.java.additional=-XX:HeapDumpPath=tmp wrapper.java.additional=-Djava.net.preferIPv4Stack=true wrapper.java.additional=-Dcom.sun.management.jmxremote wrapper.java.additional=-Dcom.sun.management.jmxremote.authenticate=false wrapper.java.additional=-Dcom.sun.management.jmxremote.port=31418 wrapper.java.additional=-Dcom.sun.management.jmxremote.rmi.port=31418 wrapper.java.additional=-Dcom.sun.management.jmxremote.ssl=false wrapper.java.additional=-Djava.rmi.server.hostname=localhost wrapper.java.additional=-Dcom.ibm.as400.access.AS400.guiAvailable=false wrapper.java.additional=-Dsymmetric.concurrent.engines.starting.count=5 wrapper.java.additional=-Dsymmetric.ssl.ignore.ciphers=TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA,TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA,TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA,SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA,SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA # Initial Java Heap Size (in MB) wrapper.java.initmemory=256 #Maximum Java Heap Size (in MB) wrapper.java.maxmemory=1024 # Wrapper Application Properties #Application main class and arguments wrapper.app.parameter.1=org.jumpmind.symmetric.SymmetricLauncher # This how long Jetty will wait until timing out on an HTTP response # This value must be greater than the send.ack.keepalive.ms parameter wrapper.app.parameter.2=–max-idle-time=90000 Wrapper Logging Properties # Log file to use for wrapper output logging. wrapper.logfile=logs/wrapper.log # Wrapper process ID wrapper.pidfile=tmp/wrapper.pid # SymmetricDS process ID wrapper.server.pidfile=tmp/server.pid # Log level (SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST) wrapper.logfile.loglevel=INFO # Size of log file when it will rotate to next file wrapper.logfile.maxsize=10m # Number of log files to rotate and keep wrapper.logfile.maxfiles=3 #Wrapper Service Properties # Name of the service wrapper.name=SymmetricDS # Display name of the service wrapper.displayname=SymmetricDS #Description of the service wrapper.description=Database Synchronization #Wrapper Windows NT/2000/XP Service Properties # Mode in which the service is installed. Auto, manual, or delay. wrapper.ntservice.starttype=delay # Service dependencies. Use + prefix for groups. wrapper.ntservice.dependency.1= # First failure action (NONE, RESTART, RUN_COMMAND) wrapper.ntservice.failure.action.type.1=RESTART # Milliseconds to wait before performing the action wrapper.ntservice.failure.action.delay.1=10000 # Second failure action (NONE, RESTART, RUN_COMMAND) wrapper.ntservice.failure.action.type.2=NONE # Milliseconds to wait before performing the action wrapper.ntservice.failure.action.delay.2=0 # Subsequent failure action (NONE, RESTART, RUN_COMMAND) wrapper.ntservice.failure.action.type.3=NONE # Milliseconds to wait before performing the action wrapper.ntservice.failure.action.delay.3=0 # Seconds after which to reset the failure count to zero wrapper.ntservice.failure.reset.period=300 #For failure action type RUN_COMMAND, the command line to run wrapper.ntservice.failure.action.command= |
[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
| [oracle@MRCPRDBI-ORCL01 engines]$ cat source-000.properties # Licensed to JumpMind Inc under one or more contributor # license agreements. See the NOTICE file distributed # with this work for additional information regarding # copyright ownership. JumpMind Inc licenses this file # to you under the GNU General Public License, version 3.0 (GPLv3) # (the “License”); you may not use this file except in compliance # with the License. # # You should have received a copy of the GNU General Public License, # version 3.0 (GPLv3) along with this library; if not, see # http://www.gnu.org/licenses/. # # Unless required by applicable law or agreed to in writing, # software distributed under the License is distributed on an # “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY # KIND, either express or implied. See the License for the # specific language governing permissions and limitations # under the License. # # Friendly name to refer to this node from command line engine.name=source-000 # The class name for the JDBC Driver # db.driver=com.mysql.jdbc.Driver db.driver=oracle.jdbc.driver.OracleDriver # db.driver=org.postgresql.Driver # db.driver=org.apache.derby.jdbc.EmbeddedDriver # db.driver=org.hsqldb.jdbcDriver # db.driver=net.sourceforge.jtds.jdbc.Driver # db.driver=com.ibm.db2.jcc.DB2Driver # db.driver=com.informix.jdbc.IfxDriver # db.driver=org.firebirdsql.jdbc.FBDriver # db.driver=interbase.interclient.Driver # db.driver=org.sqlite.JDBC # db.driver=com.sybase.jdbc4.jdbc.SybDriver # db.driver=com.nuodb.jdbc.Driver # db.driver=org.h2.Driver # The JDBC URL used to connect to the database # db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false db.url=jdbc:oracle:thin:@10.111.111.191:1521:mrcprdb # db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified # db.url=jdbc:derby:store001;create=true # db.url=jdbc:hsqldb:file:store001;shutdown=true # db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 # db.url=jdbc:db2://localhost/store001 #db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1 # db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename #db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb # db.url=jdbc:sqlite:store001.sqlite # db.url=jdbc:sybase:Tds:localhost:5000/databasename # db.url=jdbc:com.nuodb://localhost/database?schema=database # db.url=jdbc:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000 # The database user that SymmetricDS should use. db.user=crm01 # The database password db.password=crm01 # This node will contact the root node’s sync.url to register itself. registration.url=http://10.111.111.191:31415/sync/source-000 sync.url=http://10.111.111.191:31415/sync/source-000 # Node group this node belongs to, which defines what it will sync with who. #Must match the sym_node_group configuration in database. group.id=source # External ID for this node, which is any unique identifier you want to use. external.id=000 # How to run routing (in millis), which puts changes into batches. job.routing.period.time.ms=5000 # How often to run push (in millis), which sends changes to other nodes. job.push.period.time.ms=10000 # How often to run pull (in millis), which receives changes from other nodes. job.pull.period.time.ms=10000 |
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
| # Licensed to JumpMind Inc under one or more contributor # license agreements. See the NOTICE file distributed # with this work for additional information regarding # copyright ownership. JumpMind Inc licenses this file # to you under the GNU General Public License, version 3.0 (GPLv3) # (the “License”); you may not use this file except in compliance # with the License. # # You should have received a copy of the GNU General Public License, # version 3.0 (GPLv3) along with this library; if not, see # http://www.gnu.org/licenses/. # # Unless required by applicable law or agreed to in writing, # software distributed under the License is distributed on an # “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY # KIND, either express or implied. See the License for the # specific language governing permissions and limitations # under the License. # # Friendly name to refer to this node from command line engine.name=dest-001 # The class name for the JDBC Driver # db.driver=com.mysql.jdbc.Driver db.driver=oracle.jdbc.driver.OracleDriver # db.driver=org.postgresql.Driver # db.driver=org.apache.derby.jdbc.EmbeddedDriver # db.driver=org.hsqldb.jdbcDriver # db.driver=net.sourceforge.jtds.jdbc.Driver # db.driver=com.ibm.db2.jcc.DB2Driver # db.driver=com.informix.jdbc.IfxDriver # db.driver=org.firebirdsql.jdbc.FBDriver # db.driver=interbase.interclient.Driver # db.driver=org.sqlite.JDBC # db.driver=com.sybase.jdbc4.jdbc.SybDriver # db.driver=com.nuodb.jdbc.Driver # db.driver=org.h2.Driver # The JDBC URL used to connect to the database # db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false db.url=jdbc:oracle:thin:@10.121.121.191:1521:XE # db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified # db.url=jdbc:derby:store001;create=true # db.url=jdbc:hsqldb:file:store001;shutdown=true # db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 # db.url=jdbc:db2://localhost/store001 # db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1 # db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename # db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb # db.url=jdbc:sqlite:store001.sqlite # db.url=jdbc:sybase:Tds:localhost:5000/databasename # db.url=jdbc:com.nuodb://localhost/database?schema=database # db.url=jdbc:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000 # The database user that SymmetricDS should use. db.user=crm # The database password db.password=crm # This node will contact the root node’s sync.url to register itself. registration.url=http://10.111.111.191:31415/sync/source-000 sync.url=http://10.121.121.191:31415/sync/dest-001 # Node group this node belongs to, which defines what it will sync with who. # Must match the sym_node_group configuration in database. group.id=dest # External ID for this node, which is any unique identifier you want to use. external.id=001 # How to run routing (in millis), which puts changes into batches. job.routing.period.time.ms=5000 # How often to run push (in millis), which sends changes to other nodes. job.push.period.time.ms=10000 # How often to run pull (in millis), which receives changes from other nodes. job.pull.period.time.ms=10000 |
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
Nice Explanation
Hey, can you please help on configure symmetric ds mysql to ms sql