Symmetric DS is a very nice replication tool that is fairly easy to use. It replicates using SQL Apply, similar to what Oracle Golden Gate does. Oracle Golden Gate is substantially easier to configure, but SymmetricDS also gets the job done, and is available as open source software.
For the purposes of this exercise, I am using SymmetricDS version 3.8.41, and Oracle version 18.3.0, both plain vanilla versions installed with no additional patches.
SymmetricDS uses a group concept to manage replication. Each node is assigned a group, and each group has a set of replication rules assigned to it. The rules are defined in the tables created to manage the replication. The name of each of the management tables begin with sym_.
Environment Summary
Both servers run Oracle Enterprise Linux 7.5 and and Oracle database version 18.3. The database sid for store 1 is symdb, the database sid for store 2 is osymdb. Server names are oelafd1 and oelafd2, and ip address are 10.12.1.220 and 10.12.1.235 respectively. DNSMASQ is used for names resolution.
Tnsnames entries:
STORE1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.1.220)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=symdb)
)
)
STORE2 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.1.235)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=osymdb)
)
)
Each store schema has 3 tables, test1, test2, and test3 that should be identical.
Installation:
Installing SymmetricDS is very simple. Download the zip file from the web site, https://www.symmetricds.org/download.
Put the file in an appropriate directory on your environment (in my case, it is owned by Oracle on the database server), and unzip the file. Since SymmetricDS uses Java, it is ready to run once unzipped.
Setup
To set up SymmetricDS, there are three areas that must be configured, the engine file that defines the environment for the Java engine to use, the database setup that defines the environment, and the database setup that defines the replication rules.
Node/Group Identification Concepts
The individual nodes are defined in the sym_node table. This is just a list of the individual nodes involved in the replication process.
The sym_node_security table assigns creates connection information among the nodes.
The sym_node_group table contains the names of the groups involved in replication. In this example, we only have one group.
The sym_node_group_link table defines how data is replicated between groups. If the data_event_action column is set to ‘P’ data is pushed on events. If it is set to ‘W’ the group waits for a pull event. Note that for this example, we only have one group (store) and thus both stores will push data.
Driver Table Concepts
At the lowest level, the sym_channel table allocates a channel for replication. For large loads, multiple channels can be created.
Next, the sym_trigger table defines what tables are replicated, what events trigger events, and what channel the event is sent to.
The sym_router table defines routers that route events between groups. A router can have the same group as the source and destination, this would imply bi-directional replication is configured.
The sym_trigger_router table identifies what events are sent to which routers.
So, the basic concept is that you define a channel, define trigger event and assign it to a channel, define a router, and assign a trigger to a router.
Engine Setup
We have two stores configured for this two way replication, both in group store. The engine files are store-001.properties and store-002.properties.
store-001.properties:
engine.name=store-001
# The class name for the JDBC Driver
db.driver=oracle.jdbc.driver.OracleDriver
# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@10.12.1.220:1521:symdb
# The user to login as who can create and update tables
db.user=store
# The password for the user to login as
db.password=store4321
# The HTTP URL of the root node to contact for registration
registration.url=http://10.12.1.220:31415/sync/store-001
sync.url=http://10.12.1.220:31415/sync/store-001
# Do not change these for running the demo
group.id=store
external.id=001
# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=500
# This is how often the push job will be run.
job.push.period.time.ms=1000
# This is how often the pull job will be run.
job.pull.period.time.ms=1000
store-002.properties:
engine.name=store-002
# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
db.driver=oracle.jdbc.driver.OracleDriver
# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@10.12.1.235:1521:osymdb
# The user to login as who can create and update tables
db.user=store
# The password for the user to login as
db.password=store4321
# The HTTP URL of the root node to contact for registration
registration.url=http://10.12.1.220:31415/sync/store-001
sync.url=http://10.12.1.220:31415/sync/store-002
# Do not change these for running the demo
group.id=store
external.id=002
# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=500
# This is how often the push job will be run.
job.push.period.time.ms=1000
# This is how often the pull job will be run.
job.pull.period.time.ms=1000
Please take note of the registration URL and sync URL. They are very poorly documented by the SymmetricDS documentation, but they are critical for the replication to work. Both URLS use the IP address where the SymmetricDS engine is running, not the IP address where the database is running. SymmetricDS does expect one master engine, and that is where the registration URL resides.
Creating the driver tables
The required tables are created by the SymmetricDS engine, with the command create-sym-tables. The syntax is shown below:
./bin/symadmin –engine store-001 create-sym-tables
The output should show the creation of the tables for replication. This command will also initialize the identification of the source node. For reference, the commands below will also load the identification tables:
insert into store.sym_node
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id) values
('001','store','001',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
insert
into store.sym_node
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id) values
('002','store','002',1,null,null,null,null,null,current_timestamp,null,0,0,'000');<BR>insert
into store.sym_node_identity values ('001');
insert into
store.sym_node_security
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,
created_at_node_id) values
('001','5d1c92bbacbe2edb9e1ca5dbb0e481',0,current_timestamp,0,current_timestamp,'000');
insert
into store.sym_node_security
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,
created_at_node_id) values
('002','5d1c92bbacbe2edb9e1ca5dbb0e481',1,null,1,current_timestamp,'000');
-- note: the insert above sets initial_load_enabled to 1 to enable initial load
insert into sym_node_group (node_group_id) values
('store');
commit;
Be sure and verify that all the data has been properly loaded to the tables above. All tables named sym _ are required for replication to run properly. Review the output and resolve any errors that occur.
Next, set up the replication rules. Below is the configuration we use:
-- Configures the type of synch action, in this case we configure a Push
-- (‘P’) for store to store replication.
insert into sym_node_group_link (source_node_group_id, target_node_group_id,
data_event_action) values ('store', 'store', 'P');
--Configure a replication channel, this just says we will use the store
channel for replication
insert into sym_channel (channel_id, processing_order, max_batch_size,
enabled, description) values('store_outpound_ch', 1, 100000, 1, 'outbound
channel');
-- Configure one trigger for each table in this case, wildcards may be
used
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('store_outbound_tr_t1', 'TEST1','store_outpound_ch',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('store_outbound_tr_t2', 'TEST2','store_outpound_ch',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('store_outbound_tr_t3', 'TEST3','store_outpound_ch',sysdate,sysdate,1);
-- Router routes data from group to group.
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('store_2_store',
'store', 'store', 'default',current_timestamp, current_timestamp);
--Identifies which triggers use which routers.
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t1','store_2_store',
1, current_timestamp, current_timestamp);
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t2','store_2_store',
1, current_timestamp, current_timestamp);
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t3','store_2_store',
1, current_timestamp, current_timestamp);</P>
Note that the sym_trigger table will default to sync on insert, update, or delete. If you do not want to do that (eg, you may only want to track inserts and updates).
Next, start the replication.
../bin/sym (from the engines subdirectory). The initialization output will look something like this. Watch for errors:
[oracle@oelafd1 bin]
$ ./sym
Log output will be written to /home/oracle/symmetric-server-3.8.41/logs/symmetric.log
[startup] – SymmetricWebServer – About to start SymmetricDS web server on host:port 0.0.0.0:31415
[startup] – / – Initializing Spring root WebApplicationContext
[store-002] – AbstractSymmetricEngine – Initializing connection to database
[store-001] – AbstractSymmetricEngine – Initializing connection to database
[store-001] – JdbcDatabasePlatformFactory – Detected database ‘Oracle’, version ’18’, protocol ‘oracle’
[store-001] – JdbcDatabasePlatformFactory – The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[store-002] – JdbcDatabasePlatformFactory – Detected database ‘Oracle’, version ’18’, protocol ‘oracle’
[store-002] – JdbcDatabasePlatformFactory – The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[store-002] – OracleSymmetricDialect – The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
[store-002] – StagingManager – The staging directory was initialized at the following location: /home/oracle/symmetric-server-3.8.41/tmp/store-002
push request from store:001:001.
[store-001] – PushService – Pushed data to node store:002:002. 1 data and 1 batches were processed
[store-001] – StagingManager – Cleaning staging…
[store-002] – StagingManager – Cleaning staging…
[store-002] – StagingManager – Finished cleaning staging in 0 seconds.
[store-001] – StagingManager – Finished cleaning staging in 0 seconds.
[store-002] – RouterService – Routed 1 data events in 264 ms
[store-002] – PushService – Push data sent to store:001:001
[store-001] – DataLoaderService – 1 data and 1 batches loaded during push request from store:002:002.
[store-002] – PushService – Pushed data to node store:001:001. 1 data and 1 batches were processed
[store-001] – RouterService – Routed 1 data events in 330 ms
[store-001] – PushService – Push data sent to store:002:002
[store-002] – DataLoaderService – 1 data and 1 batches loaded during push request from store:001:001.
[store-001] – PushService – Pushed data to node store:002:002. 1 data and 1 batches were processed
The data events are data changes.
At this point, the replication should be loaded. Run a command on each instance to verify.
On store1:
select * from test1;
1 NAME ANDY
10 NAME ANDREW
11 NAME ANDREW
On store2:
select * from test1;
1 NAME ANDY
10 NAME ANDREW
11 NAME ANDREW
On store1:
insert into test1 (id,name,value) values (12,’LNAME’,’SMITH’);
commit;
select * from test1;
1 NAME ANDY
10 NAME ANDREW
11 NAME ANDREW
12 LNAME SMITH
store2
select * from test1;
1 NAME ANDY
11 NAME ANDREW
10 NAME ANDREW
12 LNAME SMITH
insert into test1 (id,name,value) values (14,’LNAME’,’JONES’);
commit;
select * from test1;
1 NAME ANDY
11 NAME ANDREW
10 NAME ANDREW
12 LNAME SMITH
14 LNAME JONES
store1:
select * from test1;
1 NAME ANDY
10 NAME ANDREW
11 NAME ANDREW
12 LNAME SMITH
14 LNAME JONES
Two way replication is working. Test further with updates and deletes.
In this blog we went over the creation of basic 2-way replication in SymmetricDS and Oracle. Note that we have not discussed any of the classic problems of conflict resolution that will arise when running bi-directional replication.
