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 row logical change records (LCRs). It detects a conflict by comparing the old values in the row LCR with the current values of the corresponding table row 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.

XStream inbound servers and outbound servers can be used in an XStream configuration in a multitenant container database (CDB). A CDB is an Oracle database that includes zero, one, or many user-created pluggable databases (PDBs).

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

An Oracle GoldenGate administrator must be configured at each Oracle database in the table’s replication environment, and Oracle GoldenGate must be configured to replicate the table at each Oracle database. You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

The conflict detection and resolution configured by this procedure is based on the timestamp of the changes. The procedure adds one or more hidden columns of TIMESTAMP type to the table, and each hidden column is counted against the limit of 1,000 columns for each table.

The procedure automatically places the columns in the table into a default column group and into an unconditional supplemental log group, excluding nonscalar columns. To create column groups that include a subset of the columns in the table, use the ADD_AUTO_CDR_COLUMN_GROUP procedure in this package.

Syntax

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 NULL,
   tombstone_deletes       IN BOOLEAN DEFAULT TRUE,
   fetchcols               IN BOOLEAN DEFAULT TRUE,
   record_conflicts        IN BOOLEAN DEFAULT FALSE,
   use_custom_handlers     IN BINARY_INTEGER DEFAULT 0);

Parameters

For a table that has been configured for timestamp conflict detection and resolution, this procedure adds a column group that includes a specified subset of columns in the table. Any columns in the table that are not part of a column group remain in the default column group for the table.

When you add a column group to a table, conflict detection and resolution is performed on the columns in the column group separately from the other columns in the table. Column groups enable different databases to update different columns in the same row at nearly the same time without causing a conflict.

For example, a replicated table that contains employee information might have a salary column and a bonus column as well as other columns that identify the employee and a location column for the employees office number. Assume that one department in the company updates its database to change the employee's salary while another department updates its database to change the employee's location. If the salary and bonus columns are in a column group, these changes are applied to the replicated table in each database without requiring conflict resolution.

The procedure automatically places the columns in the column group into an unconditional supplemental log group, excluding nonscalar columns.

Before this procedure can be run on a table, the DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR procedure must be run in the table with ROW specified for the resolution_granularity parameter.

Syntax

DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
   schema_name             IN VARCHAR2,
   table_name              IN VARCHAR2,
   column_list             IN VARCHAR2,
   column_group_name       IN VARCHAR2 DEFAULT NULL,
   existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

Parameters

The 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 LCR to the value in the table. For example, if a bank balance is updated at two sites concurrently, then the converged value accounts for all debits and credits. This resolution method is generally used for financial data such as an account balance.

The procedure automatically places the column into an unconditional supplemental log group.

Before this procedure can be run on a table, the DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR procedure must be run in the table with ROW specified for the resolution_granularity parameter.

Syntax

DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_DELTA_RES(
   schema_name IN VARCHAR2,
   table_name  IN VARCHAR2,
   column_name IN VARCHAR2);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.ALTER_AUTO_CDR(
   schema_name             IN VARCHAR2,
   table_name              IN VARCHAR2,
   tombstone_deletes       IN BOOLEAN DEFAULT NULL,
   fetchcols               IN BOOLEAN DEFAULT NULL,
   record_conflicts        IN BOOLEAN DEFAULT NULL,
   use_custom_handlers     IN BINARY_INTEGER DEFAULT NULL);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.ALTER_AUTO_CDR_COLUMN_GROUP(
   schema_name        IN VARCHAR2,
   table_name         IN VARCHAR2,
   column_group_name  IN VARCHAR2,
   add_column_list    IN VARCHAR2,
   remove_column_list IN VARCHAR2);

Parameters

When a database object is on the exclusion list for Oracle GoldenGate procedural replication, execution of subprogram in the package is not replicated if the subprogram operates on the excluded object. For example, if hr.employees is an excluded database object for the DBMS_REDEFINITION package, then an execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure on the hr.employees table is not replicated.

Syntax

DBMS_GOLDENGATE_ADM.DELETE_PROCREP_EXCLUSION_OBJ(
   package_owner     IN VARCHAR2 DEFAULT NULL,
   package_name      IN VARCHAR2 DEFAULT NULL,
   object_owner      IN VARCHAR2 DEFAULT NULL,
   object_name       IN VARCHAR2 DEFAULT NULL);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.GG_PROCEDURE_REPLICATION_ON
RETURN NUMBER;

When a database object is on the exclusion list for Oracle GoldenGate procedural replication, execution of subprogram in the package is not replicated if the subprogram operates on the excluded object. For example, if hr.employees is an excluded database object for the DBMS_REDEFINITION package, then an execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure on the hr.employees table is not replicated.

Syntax

DBMS_GOLDENGATE_ADM.INSERT_PROCREP_EXCLUSION_OBJ(
   package_owner     IN VARCHAR2 DEFAULT NULL,
   package_name      IN VARCHAR2 DEFAULT NULL,
   object_owner      IN VARCHAR2 DEFAULT NULL,
   object_name       IN VARCHAR2 DEFAULT NULL);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.PURGE_TOMBSTONES(
   purge_timestamp IN TIMESTAMP WITH TIME ZONE);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR(
   schema_name             IN VARCHAR2,
   table_name              IN VARCHAR2);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_COLUMN_GROUP(
   schema_name             IN VARCHAR2,
   table_name              IN VARCHAR2,
   column_group_name       IN VARCHAR2);

Parameters

Syntax

DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_DELTA_RES(
   schema_name IN VARCHAR2,
   table_name  IN VARCHAR2,
   column_name IN VARCHAR2);

Parameters