Simplify Active-Active Replication with Auto-CDR

For Active-Active environments it is best practice for Oracle Databases to apply Automatic Conflict & Detection resolution (Auto-CDR) to your Replication environment. With Auto-CDR, you not only detect conflicts, but you also resolve conflicts by applying one of the following resolution methods:

• Latest Timestamp Resolution for tables with Primary Keys ^(12gR2)
• Column Groups ^(12gR2)
• Automatic CDR for tables with Unique Keys ^(18c)
• Earliest timestamp resolution ^(21c)
• Delete always win ^(21c)
• Site priority resolution ^(21c)

As an example, I use a bi-directional environment between the databases DB01 and DB02 where I add the Latest Timestamp Auto-CDR solution.  The way of how Oracle GoldenGate provides this Auto-CDR solution is extremely simple. It is a single PL/SQL call for each table on every contributing database. On each participating Oracle Database (DB01 and DB02), you simply execute one procedure for the table in interest:

SQL> exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(‘HR’,’EMPLOYEES’)
Because there is no need to make any configuration changes within the Extract/Replicat parameter file[1], the Auto-CDR solution is much easier and less error-prone than the ‘manual’ Conflict Detection & Resolution (CDR). Depending on the architecture and your use case, different Auto-CDR resolutions might be applicable to your system.

• Unidirectional Replication with additional changes on the target system.
  Changes made from one site (source) are replicated to another side (target), and the target system itself performs changes on replicated data. As an example: If you are replication data from a production system into a test environment where data is changed at the test system, the Site priority resolution might be a good choice as the production is the source of truth.
• Bi-directional Replication
  Changes from the 1st site are replicated to a 2nd site, and the changes from the other site are replicated back to the 1st site. In most cases, the Latest Timestamp Resolution is used to guarantee consistent data all over the place.
• N-way Replication
  Replication environments with more than two sites (N-way Replication) can even become more complex as databases can be organized in a Peer-to-Peer, Hub-and-Spoke, extended/cascaded system, or a combination of all. 

The following pictures show a deeper into the context of Auto-CDR and explain more about the two key components of Conflict Detection and Conflict Resolution.

Conflict Detection
A conflict is identified if the before image of the change does not match the current image of the target system.

In this case, the phone number of the before image of the change does not match the current phone number at the target.

Conflict Resolution
The global data consistency of the system can be reconstructed with a conflict resolution method. There are multiple resolution methods available. This example uses the latest Timestamp resolution method: On each database, the record with the latest timestamp will be applied while the record with the earlier timestamp will be ignored. After processing, the data is globally consistent,  again.   

Regarding Performance: As there is additional logic on top of the basic Replication with (Auto-) CDR, there is an additional overhead. In general, you should avoid the possibility of conflicts by limiting the number of databases in the system that have simultaneous access to the tables with shared data. Primary ownership prevents conflicts, because only a single database permits updates to a set of shared data. If this is not possible, Oracle GoldenGate with Conflict Detection and resolution methods is solving this problem.

The only requirements for the use of Auto-CDR is that Replicat must be in an ‘integrated flavor’ (Parallel Replicat in integrated mode or Integrated Replicat) and the table contains a Primary Key or at least a unique index. By default, the ADD_AUTO_CDR procedure uses the Latest Timestamp Conflict Resolution method. The procedure also contains additional parameters that I would like to briefly discuss. To identify if tables are managed by AUTO_CDR, you may query the DBA_GG_AUTO_CDR_TABLES dictionary view.

As previously mentioned, you only must address the base object defined by the schema and table name on all participating databases. If you want to add multiple tables to the Auto-CDR solution, you can do this by ‘looping’ over the interesting set of tables. In addition, I also enable the WIN/LOSS monitoring option with the parameter record_conflict.

BEGIN
  FOR REC in (SELECT owner, table_name FROM dba_tables WHERE owner = ‘HR’) LOOP 
     DBMS_GOLDEMGATE_ADM.ADD_AUTO_CDR(REC.owner,REC.table_name, record_conflicts =>TRUE);
  END LOOP;
END;
/

The call of this procedure prepares the source site as it enabled supplemental logging on all columns at the Extract (source) site. It also implements the Conflict Detection logic and Conflict Resolution method internally at the Replicat (target) site. This CDR method is fully maintained within the database – there are no additional configuration steps needed in GoldenGate. Therefore, the name Auto-CDR.

At the database as a target, the LATEST TIMESTAMP method becomes enabled by default. No additional configuration steps are needed within the Extract or Replicat parameter file. As the name implies, the LATEST TIMESTAMP conflict resolution works the way, that only the latest change of the conflicting record will be processed:

• The record with the latest timestamp will be applied (WIN).
• The record with the earliest timestamp will be ignored (LOST)        

In my bidirectional environment, I also use the parameter record_conflicts => TRUE to track WIN/LOSS situations. When enabling conflict monitoring, the WIN/LOSS in the DBA_APPLIED_ERROR_MESSAGES dictionary view.
DB01:

 

DB02:


There are much more options within the ADD_AUTO_CDR procedure. A full description is available in the PL/SQL Reference guide.
Behind the scenes, it is interesting to understand how Auto-CDR works with the base objects. With Auto-CDR, the base table is modified as an invisible timestamp (CDRTS$ROW) column is added. As the column is hidden, there is no impact on the database application as this column is invisible to it:

GoldenGate, however, knows and uses this column. This column is internally populated with the timestamp (in UTC time zone) whenever an INSERT or an UPDATE operation on this record occurs. Compared to manual-CDR, this is a great advantage as the application does not require any timestamp related column. With the timestamp information, the Beside the invisible column, Auto-CDR also creates an additional table for each base table. This tombstone table contains the columns of the primary key and a timestamp column:

This tombstone table is used for DELETE conflicts: Whenever a DELETE operation happens, the record in the base table is removed, but necessary information for this record is INSERTED in the tombstone table. If the DELETE operation occurs earlier than the UPDATE operation from the opposite site, the UPDATE is transformed into an INSERT. As supplemental logging is enabled for the full table and all supplemental logged columns are part of the trail file by default, all necessary information is available to insert the record into the database. If the application does not perform any DELETE operations, you do not need the tombstone table. In this case, you could use the parameter tombstone_deletes => FALSE.

As you see within this article, the sophisticated logic of Conflict Detection and Resolution is fully maintained within the database so that the implementation from the GoldenGate perspective is easy. Compared to the older way of the manual configuration causing convoluted Extract and Replicat files. From the perspective of performance, any solution using Conflict Detection Resolution causes an additional overhead as specific checks and actions are run. However, as the maintenance of CDR operations are internally managed, the overhead is extremely low compared to other solutions.