Enable ACDR for all tables in a GoldenGate Bi-Directional Replication

An essential consideration when configuring Oracle GoldenGate for bi-directional or multi-directional (active-active) data replication is the potential for data collision, which occurs when identical data is modified in distinct databases involved in the integration.

GoldenGate includes an out-of-the-box capability to detect and resolve such conflicts. Automatic Conflict Detection and Resolution (ACDR) is configured at the table level within the Oracle database.

To enable ACDR you need to execute a package in the database for each table you require detection and resolution.

DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
   schema_name             IN VARCHAR2,
   table_name              IN VARCHAR2,
   resolution_granularity  IN VARCHAR2 DEFAULT 'ROW',
   existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT 'SYSTIMESTAMP',
   tombstone_deletes       IN BOOLEAN DEFAULT TRUE,
   fetchcols               IN BOOLEAN DEFAULT TRUE,
   record_conflicts        IN BOOLEAN DEFAULT TRUE,
   use_custom_handlers     IN BINARY_INTEGER DEFAULT 0,
   additional_options      IN BINARY_INTEGER);

Below is a quick pl/sql script to facilitate the package execution in all tables within a schema. Feel free to modify for your needs.

Add AUTO CDR

-- Add ACDR to all table in a schema
BEGIN
  FOR T IN (SELECT OWNER, TABLE_NAME FROM CDB_TABLES WHERE OWNER IN ('HR'))
  LOOP
    DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
        SCHEMA_NAME => T.OWNER,
        TABLE_NAME  => T.TABLE_NAME,
        RECORD_CONFLICTS => TRUE,
        ADDITIONAL_OPTIONS => 0);   
            -- O -> LATEST_TIMESTAMP_RESOLUTION
            -- 1 -> EARLIEST_TIMESTAMP_RESOLUTION
  END LOOP;
END;

select * from DBA_GG_AUTO_CDR_TABLES;

If you need to remove the ACDR, you can alwatys execute the remove package.

Remove Auto CDR

--  Remove ACDR to all tables in a schema
BEGIN
  FOR T IN (SELECT OWNER, TABLE_NAME FROM CDB_TABLES WHERE TABLE_NAME NOT LIKE 'DT$%' AND OWNER IN ('HR'))
  LOOP
    DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR(
        SCHEMA_NAME => T.OWNER,
        TABLE_NAME  => T.TABLE_NAME);
  END LOOP;
END;

select * from DBA_GG_AUTO_CDR_TABLES;

For further ACDR Capabilities feel free to read the package documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_GOLDENGATE_ADM.html

Alex Lima