Automatic Conflict Detection and Resolution

When Oracle GoldenGate replicates changes between Oracle databases, you can configure and manage Oracle GoldenGate Automatic Conflict Detection and Resolution in the Oracle databases.

Note:

The Automatic Conflict Detection and Resolution feature is available from Oracle Database 12c Release 2 (12.2) and later and works with Oracle GoldenGate 12c (12.3.0.1) and later releases. There is a manual conflict detection and resolution feature, which is called Oracle GoldenGate conflict detection and resolution (CDR). Oracle GoldenGate CDR is configured in the Replicat parameter file. To know more about Oracle GoldenGate CDR, see Manual Conflict Detection and Resolution.

About Automatic Conflict Detection and Resolution

When Oracle GoldenGate replicates changes between Oracle databases, you can configure and manage Oracle GoldenGate conflict detection and resolution automatically in these databases.

This feature is intended for use with active-active configurations, where Oracle GoldenGate must maintain data synchronization among multiple databases that contain the same data sets.

Note:

Automatic conflict detection and resolution (ACDR) feature that is available only when using Oracle GoldenGate with Oracle Database. For non-Oracle databases, there is a manual conflict detection and resolution (CDR) feature available with Oracle GoldenGate. Oracle GoldenGate CDR is configured in the Replicat parameter file.

Automatic Conflict Detection and Resolution

You can configure automatic conflict detection and resolution in an Oracle GoldenGate configuration that replicates tables between Oracle databases. To configure automatic conflict detection and resolution for a table, you need to call the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package. A prerequisite for setting up automatic conflict detection and resolution, the Oracle GoldenGate user must have the appropriate privileges. See Grant User Privileges for Oracle Database 23ai and Higher and Grant User Privileges for Oracle Database 21c and Lower to learn about user privileges.

The administrator user must be logged in to the appropriate PDB when calling the ADD_AUTO_CDR. The following constants, which represent bit flags are now added:

EARLIEST_TIMESTAMP_RESOLUTION sets TOMBSTONE KEY VERSIONING automatically
DELETE_ALWAYS_WINS sets TOMBSTONE KEY VERSIONING automatically.
IGNORE_SITE_PRIORITY

The following example uses an ALTER command for the HR.EMPLOYEES table:

BEGIN
 dbms_goldengate_adm.alter_auto_cdr
 (schema_name => 'HR' 
 ,table_name => 'EMPLOYEES' 
 ,additional_options => DBMS_GOLDENGATE_ADM.ADDITIONAL_OPTIONS_ADD_KEY_VERSION );
END;
/

See the description for additional_options in ADD_AUTO_CDR Procedure of Oracle Database PL/SQL Packages and Types Reference.

When Oracle GoldenGate captures changes that originated at an Oracle Database, each change is encapsulated in a row logical change record (LCR). A row LCR is a structured representation of a DML row change. Each row LCR includes the operation type, old column values, and new column values. Multiple row LCRs can be part of a single database transaction.

When more than one replica of a table allows changes to the table, a conflict can occur when a change is made to the same row in two different databases at nearly the same time. Oracle GoldenGate replicates changes using the row LCRs. It detects a conflict by comparing the old values in the row LCR for the initial change from the origin database with the current values of the corresponding table row at the destination database identified by the key columns. If any column value does not match, then there is a conflict.

After a conflict is detected, Oracle GoldenGate can resolve the conflict by overwriting values in the row with some values from the row LCR, ignoring the values in the row LCR, or computing a delta to update the row values.

Automatic conflict detection and resolution does not require application changes for the following reasons:

Oracle Database automatically creates and maintains invisible timestamp columns.
Inserts, updates, and deletes use the delete tombstone log table to determine if a row was deleted.
LOB column conflicts can be detected.
Oracle Database automatically configures supplemental logging on required columns.

Requirements for Automatic Conflict Detection and Resolution

Supplemental logging is required to ensure that each row LCR has the information required to detect and resolve a conflict. Supplemental logging places additional information in the redo log for the columns of a table when a DML operation is performed on the table. When you configure a table for Oracle GoldenGate conflict detection and resolution, supplemental logging is configured automatically for all of the columns in the table. The additional information in the redo log is placed in an LCR when a table change is replicated.

Extract must be used for capturing. Integrated Replicat or parallel Replicat in integrated mode must be used on the apply side. LOGALLSUPCOLS should remain the default.

There is a hidden field KEYVER$$ of type timestamp that is optionally added to the DELETE TOMBSTONE table. This field is required for EARLIEST TIMESTAMP, DELETE ALWAYS WINS, and SITE PRIORITY resolution and it also exists in the base table. The existence of the field in the base table needs to be provided in the trail file metadata as a flag or token.

Primary Key updates is also supported in the DELETE TOMBSTONE table. An entry is inserted into the DELETE TOMBSTONE table for the row of the original key value (before image). The logic in the Extract which matches inserts in the DELETE TOMBSTONE table to deletes also needs to be matched to PK updates, or unique key (UK) with at least one non-nullable field, if there is no PK.

Site priority needs support from the Replicat, both the parameters are implemented and the setting is passed to the apply.

Compatibility and Migration

If the base table at the source database does not contain the KEYVER$$ column, but the target base table has, DELETE and Primary Key Updates causes an error at the target database for EARLIEST TIMESTAMP, DELETE ALWAYS WINS, and SITE PRIORITY resolutions.

When replicating from a base table, which has a KEYVER$$ to a target table, which does not, the KEYVER$$ column is ignored.

Column Groups

A column group is a logical grouping of one or more columns in a replicated table. When you add a column group, conflict detection and resolution is performed on the columns in the column group separately from the other columns in the table.

When you configure a table for Oracle GoldenGate conflict detection and resolution with the ADD_AUTO_CDR procedure, all of the scalar columns in the table are added to a default column group. To define other column groups for the table, run the ADD_AUTO_CDR_COLUMN_GROUP procedure. Any columns in the table that are not part of a user-defined column group remain in the default column group for the table.

Column groups enable different databases to update different columns in the same row at nearly the same time without causing a conflict. When column groups are configured for a table, conflicts can be avoided even if different databases update the same row in the table. A conflict is not detected if the updates change the values of columns in different column groups.

Figure 11-1 Column Groups

Description of "Figure 11-1 Column Groups"

This example shows a row being replicated at database A and database B. The following two column groups are configured for the replicated table at each database:

One column group includes the Office column. The invisible timestamp column for this column group is TS1.
Another column group includes the Title and Salary columns. The invisible timestamp column for this column group is TS2.

These column groups enable database A and database B to update the same row at nearly the same time without causing a conflict. Specifically, the following changes are made:

At database A, the value of Office was changed from 1080 to 1030.
At database B, the value of Title was changed from MTS1 to MTS2.

Because the Office column and the Title column are in different column groups, the changes are replicated without a conflict being detected. The result is that values in the row are same at both databases after each change has been replicated.

Piecewise LOB Updates

A set of lob operations composed of LOB WRITE, LOB ERASE, and LOB TRIM is a piecewise LOB update. When a table that contains LOB columns is configured for conflict detection and resolution, each LOB column is placed in its own column group, and the column group has its own hidden timestamp column. The timestamp column is updated on the first piecewise LOB operation.

For a LOB column, a conflict is detected and resolved in the following ways:

If the timestamp for the LOB’s column group is later than the corresponding LOB column group in the row, then the piecewise LOB update is applied.
If the timestamp for the LOB’s column group is earlier than the corresponding LOB column group in the row, then the LOB in the table row is retained.
If the row does not exist in the table, then an error is raised.

Earliest Timestamp Conflict Detection and Resolution

Columns with names of the form CDRTS$ column group and CDRTS$ROW are used to contain timestamps that reflect modification times for column groups and the row.

Note:

Tables with $ or $$ symbols are internal or hidden tables.

The DBMS_GOLDENGATE_ADM includes the following procedures for configuring earliest and latest timestamp resolution:

ADD_AUTO_CDR()
ADD_AUTO_CDR_COLUMN_GROUP()
REMOVE_AUTO_CDR()
REMOVE_AUTO_CDR_COLUMN_GROUP()
ALTER_AUTO_CDR()
ALTER_AUTO_CDR_COLUMN_GROUP()

The field ADDITIONAL_OPTIONS in both ADD_AUTO_CDR() and ALTER_AUTO_CDR() turn on the use of earliest timestamp. Turning on earliest timestamp automatically turn on versioning, which adds a new hidden column KEYVER$$ (version number) of type timestamp. A new flag value is added to indicate the earliest timestamp usage. This field is also added to the DELETE TOMBSTONE table. Delete conflicts are the reason that version number is needed. With an earliest timestamp resolution, delete conflicts, which can be transparent, might not only incorrectly succeed, they might prevent new inserts of the row (new versions). With a version timestamp, the delete can be correctly resolved against a row DML for the same row version.

The original insert of the row receives the current timestamp from its default value. The delete of this row then inserts the version number and the time when this row was inserted, into the tombstone table when there is a delete. On a new insert, by default, the version number receives the current timestamp again, thereby avoiding a false conflict with the present delete entries in the tombstone table.

Example

Assume that you have a table tab1 which is globally consistent between databases on site 1 and site 2. The table contains a (primary) key. ACDR is automatically maintaining a key version (kv) and timestamp (ts) as columns for the base table (hidden) and the tombstone table. For key version kv and timestamp ts

Database 1: insert tab1 key1 kv1 ts1

Database 2: delete tab1 key1 kv1 ts1

Insertion to DELETE TOMBSTONE table key1 kv1 ts1

Database 1: insert tab1 key1 kv2 ts2

Without using the key version, the insert would be ignored, the delete timestamp is earlier. As the key version is used, you know that kv2 is not the version of the row that was deleted and the insert succeeds.

Latest Timestamp Conflict Detection and Resolution

When you run the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package to configure a table for automatic Oracle GoldenGate conflict detection and resolution, a hidden timestamp column is added to the table. This hidden timestamp column records the time of a row change, and this information is used to detect and resolve conflicts.

When a row LCR is applied, a conflict can occur for an INSERT, UPDATE, or DELETE operation. The following table describes each type of conflict and how it is resolved.

Operation Conflict Detection Conflict Resolution

Operation	Conflict Detection	Conflict Resolution
`INSERT`	A conflict is detected when the table has the same value for a key column as the new value in the row LCR.	If the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table. If the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.
`UPDATE`	A conflict is detected in each of the following cases: There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table. There is a mismatch between an old value in a column group in the row LCR does not match the column value in the corresponding table row. A column group is a logical grouping of one or more columns in a replicated table. The table row does not exist. If the row is in the tombstone table, then this is referred to as an update-delete conflict.	If there is a value mismatch and the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table. If there is a value mismatch and the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained. If the table row does not exist and the timestamp of the row LCR is later than the timestamp in the tombstone table row, then the row LCR is converted from an `UPDATE` operation to an `INSERT` operation and inserted into the table. If the table row does not exist and the timestamp of the row LCR is earlier than the timestamp in the tombstone table row, then the row LCR is discarded. If the table row does not exist and there is no corresponding row in the tombstone table, then the row LCR is converted from an `UPDATE` operation to an `INSERT` operation and inserted into the table.
`DELETE`	A conflict is detected in each of the following cases: There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table. The table row does not exist.	If the timestamp of the row LCR is later than the timestamp in the table, then delete the row from the table. If the timestamp of the row LCR is earlier than the timestamp in the table, then the row LCR is discarded, and the table values are retained. If the delete is successful, then log the row LCR by inserting it into the tombstone table. If the table row does not exist, then log the row LCR by inserting it into the tombstone table.

INSERT

A conflict is detected when the table has the same value for a key column as the new value in the row LCR.

If the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table.

If the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.

UPDATE

A conflict is detected in each of the following cases:

There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table.
There is a mismatch between an old value in a column group in the row LCR does not match the column value in the corresponding table row. A column group is a logical grouping of one or more columns in a replicated table.
The table row does not exist. If the row is in the tombstone table, then this is referred to as an update-delete conflict.

If there is a value mismatch and the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table.

If there is a value mismatch and the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.

If the table row does not exist and the timestamp of the row LCR is later than the timestamp in the tombstone table row, then the row LCR is converted from an UPDATE operation to an INSERT operation and inserted into the table.

If the table row does not exist and the timestamp of the row LCR is earlier than the timestamp in the tombstone table row, then the row LCR is discarded.

If the table row does not exist and there is no corresponding row in the tombstone table, then the row LCR is converted from an UPDATE operation to an INSERT operation and inserted into the table.

DELETE

A conflict is detected in each of the following cases:

There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table.
The table row does not exist.

If the timestamp of the row LCR is later than the timestamp in the table, then delete the row from the table.

If the timestamp of the row LCR is earlier than the timestamp in the table, then the row LCR is discarded, and the table values are retained.

If the delete is successful, then log the row LCR by inserting it into the tombstone table.

If the table row does not exist, then log the row LCR by inserting it into the tombstone table.

The following image displays the conflict resolution between database A and database B:
Latest timestamp conflict resolution using latest timestamp method.

This example shows a row being replicated at database A and database B. The database columns are Name, RowTS, Office, Title, and Salary. The RowTS column is the invisible column in both databases. There is an update in the Office column in database A and at the same time there is a update in the Title column in database B. This causes a conflict and the resolution for this conflict is done applying the latest timestamp method.

In database A, the value in the Office column gets updated from 1080 to 1103 and the RowTS value changes from @TS10 to @TS20. A arrow indicates that this change is replicated to database B.
In database B, the value of the Title column changes from MTS1 to MTS2 and the RowTS value changes from @TS10 to @TS22.
To resolve this conflict, the latest timestamp which exists in database B wins. This implies that the changes in database A are not applied. The final values applied to database A and database B are Scott, @TS22, 1080, MTS2, 100.

Delta Conflict Resolution

With delta conflict detection, a conflict occurs when a value in the old column list of the row LCR differs from the value for the corresponding row in the table.

To configure delta conflict detection and resolution for a table, run the ADD_AUTO_CDR_DELTA_RES procedure in the DBMS_GOLDENGATE_ADM package. The delta resolution method does not depend on a timestamp or an extra resolution column. With delta conflict resolution, the conflict is resolved by adding the difference between the new and old values in the row LCR to the value in the table. This resolution method is generally used for financial data such as an account balance. For example, if a bank balance is updated at two sites concurrently, then the converged value accounts for all debits and credits.

The following figure provides an example that illustrates delta conflict detection and resolution.

Figure 11-2 Delta Conflict Detection and Resolution

Description of "Figure 11-2 Delta Conflict Detection and Resolution"

This example shows a row being replicated at database A and database B. The Balance column is designated as the column on which delta conflict resolution is performed, and the RowTS column is the invisible timestamp column to track the time of each change to the Balance column. A change is made to the Balance value in the row in both databases at nearly the same time (@T20 in database A and @T22 in database B). These changes result in a conflict, and delta conflict resolution is used to resolve the conflict in the following way:

At database A, the value of Balance was changed from 1000 to 1750. Therefore, the value was increased by 750.
At database B, the value of Balance was changed from 1000 to 950. Therefore, the value was decreased by 50.
To resolve the conflict at database A, the value of the difference between the new and old values in the row LCR to the value in the table. The difference between the new and old values in the LCR is (1000+750-50=1700). The current value in the table is increased by 700 so that the value after conflict resolution is 1700.
To resolve the conflict at database B, the value of the difference between the new and old values in the row LCR to the value in the table. The difference between the new and old values in the LCR is 750 (1000 - 50 + 750)=1700. Therefore, the current value in the table (950) is increased by 750 so that the value after conflict resolution is 1700.

After delta conflict resolution, the value of the Balance column is the same for the row at database A and database B.

Site Priority CDR

Note:

SITE PRIORITY resolution takes precedence over all COLUMN GROUP resolution settings.

Note:

If SITE PRIORITY Replicat parameter is not placed before applicable map statements in the parameter file, it will not work. This parameter must be placed before the applicable map statements.

Priority resolution is specified in Replicat parameter file between source and target for conflict resolution.

SITE PRIORITY is enabled for a database or PDB in the Replicat parameter file with the parameter ACDR SITE_PRIORITY {source_db_name}{OVERWRITE | IGNORE }, which is specified to turn on SITE PRIORITY resolution for a table.

If the OVERWRITE option is specified, then the source table takes priority and conflicts are resolved by OVERWRITE. Conversely, if the IGNORE option is specified, then the target table takes priority and the source table changes are ignored in a conflict.

SITE PRIORITY resolution can be disabled by the field ADDITIONAL_OPTIONS in the ADD_AUTO_CDR() procedure in DBMS_GOLDENGATE_ADM package, and ALTER_AUTO_CDR() by setting IGNORE_SITE_PRIORITY.

Every Replicat source-target relationship can be set up differently, therefore, convergence is dependent on user setup.

Delete Always Wins Timestamp CDR

DELETE ALWAYS WINS is enabled through the field ADDITIONAL_OPTIONS in both DBMS_GOLDENGATE_ADM procedures ADD_AUTO_CDR() and ALTER_AUTO_CDR(). This is again a delete conflict resolution method, which is not using latest timestamp resolution, therefore, versioning is needed. Turning on DELETE ALWAYS WINS automatically turns on versioning, which adds a new hidden column KEYVER$$ (version number) of type timestamp. A new flag value is also added to acdrflags_kqldtvc to indicate DELETE ALWAYS WINS usage. This field is also added to the DELETE TOMBSTONE table. The same versioning issues exist as the EARLIEST TIMESTAMP resolution.

Example:

Key Version kv and Timestamp ts

Database 1: insert tab1 key1 kv1 ts1

Database 2: delete tab1 key1 kv1 ts1

Insertion to DELETE TOMBSTONE table key1 kv1 ts1

Database 1: insert tab1 key1 kv2 ts2

Without using the key version, the insert would be ignored, the delete always wins. As the key version is used, you know that kv2 is not the version of the row that was deleted and the insert succeeds.

DELETE TOMBSTONE Table

DELETE TOMBSTONE table is a marker for a deleted record to distinguish it from a record, which never existed. A DELETE TOMBSTONE table contains at minimum the key columns and operation timestamp. This information is required for delete convergence because some incoming updates and inserts may be delayed from another site and the incoming LCR needs to be filtered against the tombstone operation timestamp to determine whether it should be applied.

Track Primary Key Updates in Delete Tombstone

Full support of primary key (PK) updates requires handling conflicts on both the rows represented by the before image of the key and the row represented by the after image of the key. A PK update is an autonomous delete and insert, so, the PK update conflicts must be supported as a delete for conflicts with the before image of the key and inserts with the after image of the key (and row).

Supporting the PK update as a delete of the row represented by the before image of the key means that it should insert into the delete tombstone table as a delete. An update internal trigger is added to insert into the tombstone table when the PK is updated (actually the row identifying key, either the PK if it exists or the chosen UK with at least one non-nullable column). As a PK update may lead to two conflicts, up to two resolutions are attempted at the row level, delete of the row with the original PK and the insert of the row with the new PK.

Example: Using latest timestamp resolution

Database 1: Update to tab1 key1 at ts1

Database 2: Update to tab1 key1 set key1 to key2 ts2

Database 3: Update to tab1 key2 ts3

In this scenario, it appears that at the row level tab1 row with key1 should be deleted and the database 3 update should be the final modification of tab1 row key2. If instead the database 2 is at ts3 and database 3 is at ts3, then the PK update at database 2 would be the final modification of tab1 row key2.

Now, consider a case where the database 1 was at ts3, database 2 at ts2 and database 3 at ts1, then the update to tab1 row key1 on database 1 should succeed and the PK update from database 2 on tab1 row key2 should succeed. At this point, it looks like the complete resolution is that both the delete at the before image and the insert at the after image must be resolved separately. This implies that they are not dependent on each other and a loss for one, is not a loss for both.

Configuring Delta Conflict Detection and Resolution

The ADD_AUTO_CDR_DELTA_RES procedure in the DBMS_GOLDENGATE_ADM package configures delta conflict detection and resolution.

With delta conflict resolution, you specify one column for which conflicts are detected and resolved. The conflict is detected if the value of the column in the row LCR does not match the corresponding value in the table. The conflict is resolved by adding the difference between the new and old values in the row LCR to the value in the table.

You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the ADD_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
Run the ADD_AUTO_CDR_DELTA_RES procedure and specify the column on which delta conflict detection and resolution is performed.
Repeat the previous steps in each Oracle Database that replicates the table.

Example 11-23 Configuring Delta Conflict Detection and Resolution for a Table

This example configures delta conflict detection and resolution for the order_total column in the oe.orders table.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS');
END;
/

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_DELTA_RES(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS',
    COLUMN_NAME => 'ORDER_TOTAL');
END;
/

Configuring Latest Timestamp Conflict Detection and Resolution

The ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package configures latest timestamp conflict detection and resolution. The ADD_AUTO_CDR_COLUMN_GROUP procedure adds optional column groups.

For Oracle Database 23ai and higher, additional methods exist to manage and maintain ACDR configured tables. You can retain the underlying AUTO-CDR-related columns as UNUSED columns or drop them immediately after calling the REMOVE_AUTO_CDR procedure.

If you apply the ADD_AUTO_CDR procedure to a table, then by default, its internal columns are marked as unused if AUTO_CDR is removed. After calling REMOVE_AUTO_CDR, the unused columns could be manually deleted at a later stage or can be immediately removed using some additional parameters. For details, see Removing Conflict Detection and Resolution From a Table.

To know more, see ADD_AUTO_CDR Procedure in the Oracle Database PL/SQL Packages and Types Reference

With latest timestamp conflict detection and resolution, a conflict is detected when the timestamp column of the row LCR does not match the timestamp of the corresponding table row. The row LCR is applied if its timestamp is later. Otherwise, the row LCR is discarded, and the table row is not changed. When you run the ADD_AUTO_CDR procedure, it adds an invisible timestamp column for each row in the specified table and configures timestamp conflict detection and resolution. When you use the ADD_AUTO_CDR_COLUMN_GROUP procedure to add one or more column groups, it adds a timestamp for the column group and configures timestamp conflict detection and resolution for the column group.

You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

Connect to the inbound server database as a Oracle GoldenGate administrator.
Run the ADD_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
Run the ADD_AUTO_CDR_COLUMN_GROUP procedure and specify one or more column groups in the table.
Repeat the previous steps in each Oracle Database that replicates the table.

Example 11-24 Configuring the Latest Timestamp Conflict Detection and Resolution for a Table

This example configures latest timestamp conflict detection and resolution for the hr.employees table.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    SCHEMA_NAME => 'HR',
    TABLE_NAME  => 'EMPLOYEES');
END;
/

Example 11-25 Configuring Column Groups

This example configures the following column groups for timestamp conflict resolution on the HR.EMPLOYEES table:

The JOB_IDENTIFIER_CG column group includes the JOB_ID, DEPARTMENT_ID, and MANAGER_ID columns.
The COMPENSATION_CG column group includes the SALARY and COMMISSION_PCT columns.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    COLUMN_LIST       => 'JOB_ID, DEPARTMENT_ID, MANAGER_ID',
    COLUMN_GROUP_NAME => 'JOB_IDENTIFIER_CG');
END;
/

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    COLUMN_LIST       => 'SALARY, COMMISSION_PCT',
    COLUMN_GROUP_NAME => 'COMPENSATION_CG');
END;
/

Configuring Delta Conflict Detection and Resolution

The ADD_AUTO_CDR_DELTA_RES procedure in the DBMS_GOLDENGATE_ADM package configures delta conflict detection and resolution.

You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the ADD_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
Run the ADD_AUTO_CDR_DELTA_RES procedure and specify the column on which delta conflict detection and resolution is performed.
Repeat the previous steps in each Oracle Database that replicates the table.

Example 11-26 Configuring Delta Conflict Detection and Resolution for a Table

This example configures delta conflict detection and resolution for the order_total column in the oe.orders table.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS');
END;
/

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_DELTA_RES(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS',
    COLUMN_NAME => 'ORDER_TOTAL');
END;
/

Managing Automatic Conflict Detection and Resolution

You can manage Oracle GoldenGate automatic conflict detection and resolution in Oracle Database with the DBMS_GOLDENGATE_ADM package.

Altering Conflict Detection and Resolution for a Table

The ALTER_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package alters conflict detection and resolution for a table.

Oracle GoldenGate automatic conflict detection and resolution must be configured for the table:

Connect to the inbound server database as the Oracle GoldenGate administrator.
Run the ALTER_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 11-27 Altering Conflict Detection and Resolution for a Table

This example alters conflict detection and resolution for the HR.EMPLOYEES table to specify that delete conflicts are tracked in a tombstone table.

BEGIN
  DBMS_GOLDENGATE_ADM.ALTER_AUTO_CDR(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    TOMBSTONE_DELETES => TRUE);
END;
/

Altering a Column Group

The ALTER_AUTO_CDR_COLUMN_GROUP procedure alters a column group.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the ALTER_AUTO_CDR_COLUMN_GROUP procedure and specify one or more column groups in the table.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 11-28 Altering a Column Group

This example removes the MANAGER_ID column from the JOB_IDENTIFIER_CG column group for the HR.EMPLOYEES table.

BEGIN
  DBMS_GOLDENGATE_ADM.ALTER_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME        => 'HR',
    TABLE_NAME         => 'EMPLOYEES',
    COLUMN_GROUP_NAME  => 'JOB_IDENTIFIER_CG',
    REMOVE_COLUMN_LIST => 'MANAGER_ID');
END;
/

Note:

If there is more than one column, then use a comma-separated list.

Purging Tombstone Rows

The PURGE_TOMBSTONES procedure removes tombstone rows that were recorded before a specified date and time. This procedure removes the tombstone rows for all tables configured for conflict resolution in the database.

It might be necessary to purge tombstone rows periodically to keep the tombstone log from growing too large over time.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the PURGE_TOMBSTONES procedure and specify the date and time.

Example 11-29 Purging Tombstone Rows

This example purges all tombstone rows recorded before 3:00 p.m. on December, 1, 2015 Eastern Standard Time. The timestamp must be entered in TIMESTAMP WITH TIME ZONE format.

EXEC DBMS_GOLDENGATE_ADM.PURGE_TOMBSTONES('2015-12-01 15:00:00.000000 EST');

Online Redefinition on ACDR Tables

Oracle Database 23ai allows mitigating application interaction when reorganizing tables or columns using the DBMS_REDEFINITION package. The DBMS_REDEFINITION package can be used to perform the following tasks:

Remove unused columns.
Reorganization, tablespace redesign, and partitioning.

The DBMS_REDFINITION.START_REDEF_TABLE automatically manages the hidden timestamp column to the interim table.

For details about the DBMS_REDFINITION Package, see the DBMS_REDEFINITION in the Oracle Database PL/SQL Packages and Types Reference.

Removing Conflict Detection and Resolution From a Table

With Oracle Database 23ai and higher, removing Automatic Conflict Detection and Resolution (ACDR) entirely from the table has lesser impact on the table because the AUTO_CDR-related columns are marked as UNUSED if AUTO_CDR is removed.

After calling the REMOVE_AUTO_CDR procedure, the unused columns can be manually deleted in a maintenance window. This is useful for large tables where the ALTER TABLE ... DROP COLUMN operation is resource intensive.

If you want to remove all AUTO_CDR internal columns immediately when calling the REMOVE_AUTO_CDR procedure, you have to first mark the table using the additional_options parameter REMOVE_HIDDEN_COLUMNS for the ADD_AUTO_CDR or ALTER_AUTO_CDR procedure.

Use the REMOVE_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package to tag a table as UNUSED, which minimizes blocking. You can choose to drop a column or retain it at a later stage.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the REMOVE_AUTO_CDR procedure and specify the table.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 11-30 Removing Conflict Detection and Resolution for a Table

This example removes conflict detection and resolution for the HR.EMPLOYEES table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR(
    SCHEMA_NAME => 'HR',
    TABLE_NAME  => 'EMPLOYEES');
END;
/

You can choose to drop columns by using the ADD_AUTO_CDR.REMOVE_HIDDEN_COLUMNS flag as an additional_flags parameter in the ADD_AUTO_CDR procedure.

Here is an example that you can use to view hidden columns in a table.

The following query uses the DBA_UNUSED_COL_TABS package to determine if there unused columns in the EMPLOYEES table.


SELECT OWNER, TABLE_NAME, COUNT
  FROM DBA_UNUSED_COL_TABS
  WHERE OWNER = 'HR'
  AND TABLE_NAME = 'EMPLOYEES'
  ORDER BY OWNER, TABLE_NAME;

The output displays as follows:

OWNER        TABLE_NAME    COUNT
--------    ------------ ----------
HR            EMPLOYEES      1

The following query lists out the hidden columns that were tagged by the system when ACDR was removed for the column group in the EMPLOYEES table.

SELECT OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
  FROM DBA_TAB_COLS
  WHERE OWNER = 'HR'
  AND TABLE_NAME = 'EMPLOYEES'
  AND HIDDEN_COLUMN = 'YES' AND USER_GENERATED= 'NO'
  ORDER BY OWNER, TABLE_NAME, COLUMN_ID;

The output displays as follows:

OWNER        TABLE_NAME    COLUMN_ID  COLUMN_NAME      DATA_TYPE     HIDDEN_COLUMN  
------------ ------------ ---------- -------------    ------------ ---------------
HR           EMPLOYEES    SYS_C00014_22092220:30:52$   TIMESTAMP(6)       YES

Removing a Column Group

With Oracle Database 23ai and higher, removing Automatic Conflict Detection and Resolution (ACDR) from column groups has lesser impact on the table because the ACDR related columns are marked as UNUSED. You can also choose to drop a column or retain it at a later stage.

Use the REMOVE_AUTO_CDR_COLUMN_GROUP procedure in the DBMS_GOLDENGATE_ADM package to tag a table, which minimizes blocking. See the example in Removing Conflict Detection and Resolution From a Table.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the REMOVE_AUTO_CDR_COLUMN_GROUP procedure and specify the name of the column group.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 11-31 Removing a Column Group

This example removes the COMPENSATION_CG column group from the HR.EMPLOYEES table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    COLUMN_GROUP_NAME => 'COMPENSATION_CG');
END;
/

Removing Delta Conflict Detection and Resolution

The REMOVE_AUTO_CDR_DELTA_RES procedure in the DBMS_GOLDENGATE_ADM package removes delta conflict detection and resolution for a column.

Delta conflict detection and resolution must be configured for the specified column.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the REMOVE_AUTO_CDR_DELTA_RES procedure and specify the column.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 11-32 Removing Delta Conflict Detection and Resolution for a Table

This example removes delta conflict detection and resolution for the ORDER_TOTAL column in the OE.ORDERS table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_DELTA_RES(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS',
    COLUMN_NAME => 'ORDER_TOTAL');
END;
/

Monitoring Automatic Conflict Detection and Resolution

You can monitor Oracle GoldenGate automatic conflict detection and resolution in an Oracle Database by querying data dictionary views.

Displaying Information About the Tables Configured for Conflicts

The ALL_GG_AUTO_CDR_TABLES view displays information about the tables configured for Oracle GoldenGate automatic conflict detection and resolution.

Connect to the database.
Query the ALL_GG_AUTO_CDR_TABLES view.

Example 11-33 Displaying Information About the Tables Configured for Conflict Detection and Resolution

This query displays the following information about the tables that are configured for conflict detection and resolution:

The table owner for each table.
The table name for each table.
The tombstone table used to store rows deleted for update-delete conflicts, if a tombstone table is configured for the table.
The hidden timestamp column used for conflict resolution for each table.

COLUMN TABLE_OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A15
COLUMN TOMBSTONE_TABLE FORMAT A15
COLUMN ROW_RESOLUTION_COLUMN FORMAT A25

SELECT TABLE_OWNER,
       TABLE_NAME, 
       TOMBSTONE_TABLE,
       ROW_RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_TABLES
  ORDER BY TABLE_OWNER, TABLE_NAME;

Your output looks similar to the following:

TABLE_OWNER     TABLE_NAME      TOMBSTONE_TABLE ROW_RESOLUTION_COLUMN
--------------- --------------- --------------- -------------------------
HR              EMPLOYEES       DT$_EMPLOYEES   CDRTS$ROW
OE              ORDERS          DT$_ORDERS      CDRTS$ROW

Displaying Information About Conflict Resolution Columns

The ALL_GG_AUTO_CDR_COLUMNS view displays information about the columns configured for Oracle GoldenGate automatic conflict detection and resolution.

The columns can be configured for row or column automatic conflict detection and resolution. The columns can be configured for latest timestamp conflict resolution in a column group. In addition, a column can be configured for delta conflict resolution.

Connect to the database as an Oracle GoldenGate administrator.
Query the ALL_GG_AUTO_CDR_COLUMNS view.

Example 11-34 Displaying Information About Column Groups

This query displays the following information about the tables that are configured for conflict detection and resolution:

The table owner for each table.
The table name for each table.
If the column is in a column group, then the name of the column group.
The column name.
If the column is configured for latest timestamp conflict resolution, then the name of the hidden timestamp column for the column.

COLUMN TABLE_OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A10
COLUMN COLUMN_GROUP_NAME FORMAT A17
COLUMN COLUMN_NAME FORMAT A15
COLUMN RESOLUTION_COLUMN FORMAT A23

SELECT TABLE_OWNER,
       TABLE_NAME, 
       COLUMN_GROUP_NAME,
       COLUMN_NAME,
       RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_COLUMNS
  ORDER BY TABLE_OWNER, TABLE_NAME;

Your output looks similar to the following:

TABLE_OWNE TABLE_NAME COLUMN_GROUP_NAME COLUMN_NAME     RESOLUTION_COLUMN
---------- ---------- ----------------- --------------- -----------------------
HR         EMPLOYEES  COMPENSATION_CG   COMMISSION_PCT  CDRTS$COMPENSATION_CG
HR         EMPLOYEES  COMPENSATION_CG   SALARY          CDRTS$COMPENSATION_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG MANAGER_ID      CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG JOB_ID          CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG DEPARTMENT_ID   CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  IMPLICIT_COLUMNS$ PHONE_NUMBER    CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ LAST_NAME       CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ HIRE_DATE       CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ FIRST_NAME      CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ EMAIL           CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ EMPLOYEE_ID     CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_MODE      CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_ID        CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_DATE      CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ CUSTOMER_ID     CDRTS$ROW
OE         ORDERS     DELTA$            ORDER_TOTAL
OE         ORDERS     IMPLICIT_COLUMNS$ PROMOTION_ID    CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_STATUS    CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ SALES_REP_ID    CDRTS$ROW

In this example, the columns with IMPLICIT_COLUMNS$ for the column group name are configured for row conflict detection and resolution, but they are not part of a column group. The columns with DELTA$ for the column group name are configured for delta conflict detection and resolution, and these columns do not have a resolution column.

Displaying Information About Column Groups

The ALL_GG_AUTO_CDR_COLUMN_GROUPS view displays information about the column groups configured for Oracle GoldenGate automatic conflict detection and resolution.

You can configure Oracle GoldenGate automatic conflict detection and resolution using the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package. You can configure column groups using the ADD_AUTO_CDR_COLUMN_GROUP procedure in the DBMS_GOLDENGATE_ADM package.

Connect to the database as an Oracle GoldenGate administrator.
Query the ALL_GG_AUTO_CDR_COLUMN_GROUPS view.

Example 11-35 Displaying Information About Column Groups

This query displays the following information about the tables that are configured for conflict detection and resolution:

The table owner.
The table name.
The name of the column group.
The hidden timestamp column used for conflict resolution for each column group.

COLUMN TABLE_OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A15
COLUMN COLUMN_GROUP_NAME FORMAT A20
COLUMN RESOLUTION_COLUMN FORMAT A25

SELECT TABLE_OWNER,
       TABLE_NAME, 
       COLUMN_GROUP_NAME,
       RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_COLUMN_GROUPS
  ORDER BY TABLE_OWNER, TABLE_NAME;

The output looks similar to the following:

TABLE_OWNER     TABLE_NAME      COLUMN_GROUP_NAME    RESOLUTION_COLUMN
--------------- --------------- -------------------- -------------------------
HR              EMPLOYEES       COMPENSATION_CG      CDRTS$COMPENSATION_CG
HR              EMPLOYEES       JOB_IDENTIFIER_CG    CDRTS$JOB_IDENTIFIER_CG