This post we are going to discuss a lower cost alternative to Oracle GoldenGate. SymmetricDS.
SymmetricDS is open source Java software designed to run SQL replication. It works well, but since it uses triggers on the database for all of its Processing, it is going to have a significant impact on your system, and is likely to run into problems at high volumes. I have not load tested SymmetricDS vs Golden Gate, but the tuning options for SymmetricDS are limited, so it is unlikely to be able to compete with GoldenGate on performance. However, SymmetricDS is indeed a viable and low cost option.
In our example, we are using the same configuration as our previous blog, https://dbakerber.wordpress.com/2019/08/09/goldengate-multi-master-with-integrated-replicat-and-apply/ . The tnsnames entries for each database are here. Note that we dont actually use the TNS entries, but having them available makes the connection information easy to find:
ORA183T =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.1.28)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora183t)
)
)
ORA121T =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.1.8)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora121t)
)
)
The SymmetricDS program is running on an OEL 7.6 server named oralin, with an IP address of 10.12.1.229. The Oracle client has been installed on this server. We have the bin directory for the client in the PATH.
SymmetricDS is installed by simply unzipping the distribution file. In our case, we unzipped the files to /u01/app/symmetricds, so that it sits in the same structure as our Oracle client.
In symmetricDS, your sources are defined by ‘engine’ files, which give the connection information, including the driver and connection username and password. The sample engine files are in the samples directory. In this case, we took the store-001.properties file, renamed it to ora121t-001.properties, and copied it to the engines subdirectory. The store-002.properties files was copied to ora183t.properties. SymmetricDS expects the engine files to be in the engines subdirectory, and will automatically connect to the instances defined there.
Below are our two engine files:
[oracle@oralin engines]$ cat ora121t-001.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=ora121t-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.8:1521:ora121t
# The database user that SymmetricDS should use.
db.user=hrds
# The database password
db.password=tiger
# This node will contact the root node’s sync.url to register itself.
registration.url=http://10.12.1.229:31415/sync/ora121t-001
sync.url=http://10.12.1.229:31415/sync/ora121t-001
# Node group this node belongs to, which defines what it will sync with
# Must match the sym_node_group configuration in database.
group.id=ora121t
# External ID for this node, which is any unique identifier.
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 job.pull.period.time.ms=10000
[oracle@oralin engines]$ cat ora183t-002.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=ora183t-002
# 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.28:1521:ora183t
# The database user that SymmetricDS should use.
db.user=hrds
# The database password
db.password=tiger
# This node will contact the root node’s sync.url to register itself.
# registration.url=http://localhost:31415/sync/corp-000
registration.url=http://10.12.1.229:31415/sync/ora121t-001
sync.url=http://10.12.1.229:31415/sync/ora183t-002
# 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=ora183t
# External ID for this node, which is any unique identifier you want to use.
external.id=002
# 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
Once the engine files are created, create the required symmetricDS tables by running symadmin to create the required tables in the 001 engine:
[oracle@oralin bin]$ ./bin/symadmin –engine ora121t-001 create-sym-tables
We are using the same schema for replication and running the symmetricDS application, the HRDS schema. This is not required, but remember to grant appropriate privileges using a different schema (eg, symmetric). Below are the commands used to build the identity information for this configuration:
insert into hrds.sym_node (node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version, database_type,database_version,batch_to_send_count, batch_in_error_count,created_at_node_id)
values ('000','ora121t','000',1,null,null,null,null,null,0,0,'000');
insert into hrds.sym_node (node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version, database_type,database_version,batch_to_send_count, batch_in_error_count,created_at_node_id)
values ('001','ora183t','001',1,null,null,null,null,null,0,0,'000');
insert into hrds.sym_node_identity values ('001');
insert into hrds.sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time, created_at_node_id)
values ('000','5d1c92bbacbe2edb9e1ca5dbb0e481',0,current_timestamp,0,current_timestamp,'000');
insert into hrds.sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time, created_at_node_id)
values ('001','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 ('ora121t');
insert into sym_node_group (node_group_id) values ('ora183t');
commit;
Once the identify information is created as shown above, its time to define the replication. Note that the tables are not particularly intuitive, so the summary below is from my previous blog to explain the table usage:
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.
Below are the commands we used to load data into the driver tables:
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('ora121t', 'ora183t', 'W');
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('ora121t_outbound', 1, 100000, 1, 'ora121t transaciton');
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch)
values ('ora121t_outbound_tr', '*','ora121t_outbound',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('ora121t_2_ora183t', 'ora121t', 'ora183t', 'default',current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('ora121t_outbound_tr','ora121t_2_ora183t', 1, current_timestamp, current_timestamp);
commit;
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('ora183t', 'ora121t', 'P');
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('ora183t_outbound', 1, 100000, 1, 'ora183t transaciton');
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch)
values ('ora183t_outbound_tr', '*','ora183t_outbound',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('ora183t_2_ora121t', 'ora183t', 'ora121t', 'default',current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('ora183t_outbound_tr','ora183t_2_ora121t', 100, current_timestamp, current_timestamp);
commit
Note that even though we use ‘*’, only the tables without a sym_ name prefix will be replicated. SymmetricDS assumes that all table names beginning with sym_ are its own tables and does not replicate them.
Next, we start the program. ./bin/sym. Watch the output of the program. There may be errors. Since these are java errors, tracking down the cause and finding a fix may take time.
The error you are most likely to see is this:
[ora121t-001] – RegistrationUriHandler – ora183t:002:? was not allowed to register.
[ora183t-002] – RegistrationService – Waiting for registration to be accepted by the server. Registration is not open.
[ora183t-002] – RegistrationService – Could not register. Sleeping for 20000ms before attempting again.
If you see the registration error, just let it run and start a session on another terminal and run this command:
./symadmin –engine ora121t-001 open-registration ora183t 002
The command above opens registration for the second engine. If the command works, you will see this:
[ora183t-002] – DataLoaderService – Using registration URL of http://10.12.1.229:31415/sync/ora121t-001/registration?nodeGroupId=ora183t&externalId=002&syncURL=http%3A%2F%2F10.12.1.229%3A31415%2Fsync%2Fora183t-002&schemaVersion=%3F&databaseType=Oracle&databaseVersion=18.0&symmetricVersion=3.10.3&deploymentType=server&hostName=oralin.localdomain&ipAddress=10.12.1.229
[ora121t-001] – RegistrationService – Completed registration of node ora183t:002:002
[ora121t-001] – ConfigurationChangedDataRouter – About to refresh the cache of nodes because new configuration came through the data router
After successful registration, you will see output about creating a large number of triggers. As noted earlier, SymmetricDS creates triggers on the database to run replication. The triggers you see being created are in response to the SQL statements we ran above to configure the replication.
Once again, follow the output for errors.
You will note that in the above process we only ran updates on one database instance. SymmetricDS then read the information in the configuration tables and wrote the required information to the other instance.
At this time, the bi-directional replication is configured and running. Run updates on individual tables and verify that the data is replicating properly.
