How To: Configure bidirectional or multidirectional replication with Oracle
Scenario:
The assumption for this is to configure Local Data Processing Oracle setup for a multi active-active environment. In an active/active replication environment there can always be collisions. Of course you want to prepare your application to prevent unforeseeable collisions such as primary key values generated by a database sequence or in the case of RAC you may choose to use a GUID to keep them unique across nodes.
However, despite the best preparations, collisions can still happen. For example users in different databases may update the same row in different databases as the same time, or a row may updated in one database and deleted in another database. If collisions happen the end result may be that your databases get out of sync which typically leads to databases growing further and further apart, and more and more errors arise. In some cases you my know based on the application whether collisions are likely to occur or not, and if they occur, how do you want to deal with them.
Local Data Processing provides a sophisticated collision detect capability using a history table for every database table that will ensure that the most recent change to a row always wins and systems remain in sync in environments with any number of active systems.
If this is insufficient or not desired we also provide another means resolving collisions. When CollisionDetect is defined with the parameter /TimestampColumn then it is supported for all DBMSs. This means that table must have a last update timestamp column. However, if this action is defined without the parameter /TimestampColumn, meaning there is no last update timestamp column, then it is supported only for Ingres and Oracle DBMSs.
But that solution implies that you have a timestamp column in the layout which your application always changes during any operation. So, the options are to use a timestamp column or our built-in history table which then creates another table in their database on the source and target are currently your out of box options.
Overview:
Active/active replication can be really challenging, and for most technologies you spend a lot of time on such an implementation because the setup is point-to-point, and you have to perform configuration steps on every system in the setup. Not so with Local Data Processing! Using Local Data Processing for multi-way active/active database replication is easy, as described in this document. Also we will show you to configure Local Data Processing for conflict resolution and to keep track of your changes worldwide and touch on alternatives.
Note that the all databases in an active/active replication setup must be in archive log mode as we are required to enable supplemental logging so we can capture the primary key value during updates. If the table does not have a primary key we enable supplemental logging on all columns except for LOBs and LONG datatypes.
Steps
Step 1] Define location configuration for all your locations participating in replication. For this example I’ve configured 3 application locations for each of the databases I want to connect t
For example, ,my first user is local on my PC, Oracle XE database. I configured the other 2 physical locations of the other 2 database in app2 & app3.
Create 2 more locations for app2 & app3 resulting in 3 location configurations as shown
Step 2] Define a channel for replication by right clicking on Channel Definitions
Step 3] Next I’m going to define the logical locations or as Local Data Processing refers to them as Location Groups. Since this is an active-active-active configuration, we are going to only define a single location group with all of logical locations participating.
So I’m first going to create a new location group by right clicking on Location Group and select New Group. I define the Group Name ‘Active’ and select all locations app1, app2, app3
Step 4] Use Table Explorer and select the tables you want as part of your replication set from one of your locations. My app1 location has a the table ‘test1’ that I want to replicate to all locations
So right click on Table and I selected app1 and connected to the database. There I then clicked on ‘test’ table.
Step 5] Our next step is to minimally create 2 actions, Capture and Integrate by selecting the location group and right clicking and selecting New Action and Capture
Step 6] Add action capture leaving all the rest default values except, Table Table * and the Group ACTIVE at the top
Step 7] Add action /Integrate /OnErrorSaveFailed, once again leaving all the rest default values except, Table * and the Group ACTIVE at the top
Step 8] We enable this action Collision detect by right clicking on the location group and adding the action to our channel. Select and select /AutoHistoryPurge on your replication group ACTIVE so we clean up after ourselves.
As I mentioned above when CollisionDetect is defined with the parameter /TimestampColumn then it is supported for all DBMSs. This means that table must have a last update timestamp column. However, if this action is defined without the parameter /TimestampColumn, meaning there is no last update timestamp column, then it is supported only for Ingres and Oracle DBMSs. For this scenario we let Local Data Processing history table resolve the conflict.
Step 9] So when we are done our hub looks like this
Step 10] Now we are just about done though some of our location may not have the tables created at first. So we will Refresh to create the tables on all your other locations. We select the source location as app1 because this schema has the table in it and the targets to be physical locations, app2 & app3. Select All_Tables in this case ‘test1’ and we also select ‘Create Absent Tables’, Online Refresh option to ensure that once replication ins running and catches up to the current state then all systems are in sync. If your number or size of table is large you have the option to choose to set parallelism for location and/or tables and value that your server has resources to handle.
Step 11] Once that is complete I now need to Initialize the channel. Which if you are familiar with one way replication this step is done before Refreshing but for multi-directional replication you need to create the tables in a for all locations which needs to be done first followed by Initializing. Just a slight difference in order the first time you set up active/active replication.
Note the options selected. If this is your initial setup you would select the options you see below. If you are adding a table to replication a running replication you would select other options such as Table Enrollment, Replace old Enrollment, Scripts & Jobs only. We have other KBs on our Local Data Processing Forum website that go into more depth but for now continue with the options shown.
Step 12] You will notice that this process has created though not started jobs under the Scheduler. Your setup is now complete.