Both Oracle Advanced Replication and SymmetricDS provide database replication, which is a valuable tool for improving access to critical data that drives a business. While some common replication concepts are used across both products, there are some differences in terminology and configuration. Let's take a look at some of the scenarios for database replication and how both products accomplish them.

Why Use Replication

Database replication enables fast, local access to shared data, and it can improve how quickly and how often data is available. Some common reasons to use replication are:

  • High Availability - Users can access data from the database that is running and available from the location that is closest to them geographically.
  • Performance - Balancing access requests across multiple servers or running heavy operations like reporting on a separate database.
  • Disconnected Operation - Users can work on data while disconnected from the central database, then send and receive changes when the connection is established.

Replication Objects

Oracle Advanced Replication can copy all types of database objects between one site and another when any updates are made to the object. Because it is native to Oracle, it is aware of platform-specific objects like stored procedures, views, and synonyms. SymmetricDS can replicate only table data and table schema between sites. Because it is cross platform, it supports only table objects which are common to any database. However, it is possible to send custom SQL scripts to sites through the synchronization in order to create platform-specific objects.

Replication Groups

Oracle Advanced Replication uses replication groups as a collection of tables that are logically related. The organization into groups makes it easy to administer data and support a particular application. In SymmetricDS, tables are assigned to a channel, which groups them for synchronization. Channels are assigned priorities and sync independently, which helps to control the flow of important data.

Replication Sites

Sites in Oracle Advanced Replication are a database that participates in replication, either as a master site or a materialized view site. A master site contains a complete copy of all the data within the replication group. They communicate directly with each other to continuously propagate changes. A materialized view site can contain a complete or partial copy of the data, and it can be either readonly or updateable. A materialized view has data from a point in time, and a refresh is performed periodically by contacting a master site for a full or incremental refresh. By putting a materialized view on another one, data refreshes can cascade from the master to a multi-tier arrangement of sites.

Nodes in SymmetricDS are a database that participates in replication. Nodes are assigned to node groups and configured to replicate tables with other node groups. A link is created between a source and target node group for each direction needed, which establishes communication to continuously propagate changes. Data changes pass through routers that determine which nodes will receive the changes. By configuring routers to send or filter data, a node group can replicate either a complete or partial copy of the data. Linking node groups for replication and configuring which table data to route in which direction allows for flexibility of a network that includes mult-tier arrangement.

Types of Replication

Oracle Advanced Replication supports multimaster replication, materialized view replication, and hybrid configurations using both. Multimaster replication means all sites have the same copy of data, allow updates, and propagate the changes to each other as peers. Materialized view replication means the site has a copy of the data from a point in time, and it contacts the master on a schedule to refresh and pull changes. The basic configuration for a materialized view is readonly, with changes only allowed from the master site, but advanced configuration allows for updates to materialized views.

SymmetricDS supports multimaster replication, filtered synchronization, and transformation. Data is replicated to all nodes with multimaster, allowing updates at any node, and changes are propagated across the network following configured node group links. Communication can be configured as a push, with the source node opening the connection to send changes, or a pull, with the target node opening the connection to receive changes. Push is more efficient since connections are only created when changes are waiting, but pull can easily traverse firewalls. The frequency of sending changes is controlled by parameters that can target all nodes, specific node groups, or individual nodes. Filtered synchronization allows selecting tables and columns for vertical subsetting as well as selecting rows for horizontal subsetting. Transformation allows manipulation of change data to translate values, split or merge rows, and enhance the data.

Synchronous, Asynchronous, and Procedural

With Oracle Advanced Replication, multimaster replication can be implemented as asynchronous replication, synchronous replication, or procedural replication. Asynchronous means changes are captured in a log as transactions commit locally, then the changes are propagated to other sites to be applied. Since data can be updated concurrently at different sites, conflict resolution is used to merge and resolve concurrent data changes. Synchronous means all sites participate in a transaction so data is committed or rolled back simultaneously. While it avoids conflicts, synchronous replication requires a stable environment for continous operation. Procedural replication allows for changing large amounts of data by replicating calls to stored procedures that modify the data, but it does not replicate the data modifications themselves.

SymmetricDS captures changes in a log as transactions commit locally, then propagates changes in the background asynchronously. Conflicts are detected and run through a resolver for automatic merging and resolution, and the user can configure or write scripts for custom resolvers. Procedure calls and custom SQL scripts can be sent to nodes through a replication event instead of sending individual row changes. A reload event can be used as an efficient way to send a large set of data with a SQL expression instead of capturing individual rows. There is also a special session variable that can also be used to temporarily prevent data capture during batch operations.

Administration Tools

Oracle Advanced Replication includes server tools for administration, including a web application, a PL/SQL interface, and a replication catalog. The Oracle Enterprise Manager provides a web application to setup replication and monitor operation, and it includes online documentation. The same administrative capabilities are available from interactive SQL with PL/SQL function and procedure calls. In the database, the replication catalog is a set of tables and views that contain administrative information about replication objects and groups at the site.

SymmetricDS includes server tools for administration, including a web application, multiple programming interfaces, and a database model. The web console includes setup of replication, monitoring, troubleshooting tools, and online documentation. Administration can also be done from several interfaces including command line tools, REST calls, Java Management eXtensions (JMX), and interactive SQL. In the database, the configuration tables control how the system behaves while the runtime tables contain the capture log, batches, and replication status.

Conclusion

While Oracle Advanced Replication and SymmetricDS use different terms and concepts for configuration, they both provide database replication for improving access to data. Ensuring that applications can function with data that is fast to access and always available is important for continued operation of business, and it is something that customers have come to expect. Database replication is used in scenarios of high availability, load distribution, and disconnected operation, and it can be the cornerstone of a real-time data integration strategy for the enterprise.

Eric Long
Author: Eric Long

Eric is a software developer and technology enthusiast with a background in developing custom applications for Information Technology. As a long-time Linux user, he strongly believes in open source. He focuses on product solutions and spreading the word for JumpMind.