GoldenGate has a built-in loop detection, which is configured through the IGNOREREPLICATES and GETAPPLOPS parameters to prevent local transactions from being replicated and causing endless loops. Another solution would be the TRANLOGOPTIONS EXCLUDEUSER parameter in the Extract process configuration, which effectively blocks the GGADMIN user on the target system (the user associated with the Replicat process). However, loop detection is only half the battle in a bidirectional environment. We must also consider conflict detection and resolution.

Oracle GoldenGate now supports Conflict Detection and Resolution (CDR) as a new feature of 12c. This has been long awaited in GoldenGate, given that Oracle Streams included CDR in its out-of-the-box functionality.

Conflicts occur in a bidirectional environment when the same row in a given table is updated at the same time on both sites. Which transaction will succeed? Should they both fail? These are both valid questions and must be answered before configuring your conflict handler.

GoldenGate is renowned for its low latency, which helps to alleviate any conflicts. However, the best solution would be at the application layer, segregating users at the different locations, only allowing each group to update specific ranges of records in tables, thus avoiding conflicts. This is sometimes not possible, and CDR must be employed.

To understand how CDR works, we need to appreciate the significance of Before and After images in the Logical Change Records (LCR):

The LOGALLSUPCOLS Extract parameter ensures that a full record of the Before image is captured from the source database and written to the trail files. The GETBEFORECOLS option of the TABLE parameter tells the Extract to store the Before images of the columns specified. GoldenGate then evaluates them against the existing values in the target table's row.

GoldenGate can resolve the following detected conflicts out of the box:

Prior to Oracle GoldenGate 12c, the following code shows the manual configuration required in the Replicat's parameter file to detect a conflict. SQLEXEC is used initially to obtain the existing value in the target table. Then it is compared with the Before image from the source table by the BEFORE and CHECK parameters of the FILTER clause:

REPERROR (9999, EXCEPTION)
MAP SRC.CREDITCARD_PAYMENTS, TARGET TGT.CREDITCARD_PAYMENTS, &
SQLEXEC (ID CHECK_CONFLICT, ON UPDATE, BEFOREFILTER, &
QUERY 'SELECT PAYMENT FROM TGT.CREDITCARD_PAYMENTS &
WHERE ID = :P1', &
PARAMS (P1 = ID)), &
FILTER (ON UPDATE, BEFORE.PAYMENT <> CHECK.PAYMENT, &
RAISEERROR 9999);
INSERTALLRECORDS
MAP SRC.CREDITCARD_PAYMENTS, TARGET TGT.EXCEPTIONS, EXCEPTIONSONLY, &
COLMAP (USEDEFAULTS, ERRTYPE = 'Conflict Detected'),

The SQLEXEC parameter allows GoldenGate to interact with the database directly to SELECT a value, perform a DML operation, or execute a stored procedure. The BEFOREFILTER parameter of SQLEXEC allows the SQL to execute before the FILTER statement, enabling the results to be used in the filter.

Now, in Oracle GoldenGate 12c, the configuration is far simpler as there are a number of powerful parameters that support CDR.

These are:

The following options of the RESOLVECONFLICT option are types of conflict:

The following options of the preceding conflict types are resolution methods:

Here is an example of the use of the UPDATEROWEXISTS and USEMAX CDR options for the MAP statement:

MAP PDB1.SRC.CREDITCARD_PAYMENTS, TARGET PDB2. TGT.CREDITCARD_PAYMENTS, 
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (payment));

Depending on the application, applying the maximum or minimum values to resolve a conflict may not be appropriate. The USEDELTA option may be the solution for bidirectional environments, where only the difference between column values is applied. Using this option, there is no data divergence that allows both systems to become synchronized. However, as expected, USEDELTA only supports numeric columns. Here is a similar example to the preceding example, but this time using the USEDELTA option:

MAP PDB1.SRC.CREDITCARD_PAYMENTS, TARGET PDB2. TGT.CREDITCARD_PAYMENTS, 
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (delta_res_method, USEDELTA, COLS (payment, balance));

Some of the previous examples use the DEFAULT column group. The associated resolution method affects all columns in the row of the target database, whereas the named column group (via the resolution_name parameter) references a list of specific columns in the target database that CDR will apply its rules. This enables the GoldenGate administrator the option to be selective over the columns effected by the CDR mechanism. Use either a named resolution or DEFAULT, but not both.

GGSCI (db12server02) 1> STATS REPLICAT ROLAP01, REPORTCDR

You may wish not to have CDR automatically detect and resolve conflicts. Instead, you may wish to detect the exception and execute a task, such as populating an exceptions table to log the event for manual processing. I have dedicated a section to discuss this in detail in Chapter 10, Troubleshooting GoldenGate.

GoldenGate does not support the replication of Oracle database sequence values in a bidirectional (active-active) configuration. The database sequences must generate values on the target database independent to the source. Therefore, to ensure that the source and target database sequence numbers are unique across the environment, it is good practice to assign odd and even values to each source and target respectively.

For active-passive, Oracle sequence values can be replicated using the SEQUENCE parameter in the Extract and data pump configuration. Although DDL replication of sequences is supported in a GoldenGate environment, the feature does not need to be explicitly installed to replicate sequence values.

Some guidelines to use Oracle sequences in the DDL replication are as follows:

The support for GoldenGate's DDL replication is discussed later in this chapter.

Another consideration in a bidirectional environment is Oracle triggers. Having triggers firing on your primary source database may not be a problem, but when the related transactions are applied to your target where the same triggers are enabled, data duplication may result. Furthermore, should the triggered transactions on the target be replicated back to the source, you have a real problem because the IGNOREREPLICATES and EXCLUDEUSER parameters will not work in this case.

One solution would be to disable triggers on the target during replication. This can be achieved through the SQLEXEC parameter statement that calls bespoke stored procedures, which initially disables and then reenables the triggers, as shown in the following code:

DISCARDFILE 
REPLICAT RLOAD01
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rload01.dsc, APPEND
USERIDALIAS tgtdb DOMAIN admin
SQLEXEC "call disable_triggers ()"
MAP PDB1.SRC.CHECK_PAYMENTS, TARGET PDB1.TGT.CHECK_PAYMENTS;
MAP PDB1.SRC.CHECK_PAYMENTS_STATUS, TARGET PDB1.TGT.CHECK_PAYMENTS_STATUS;
MAP PDB1.SRC.CREDITCARD_ACCOUNTS, TARGET PDB1.TGT.CREDITCARD_ACCOUNTS;
SQLEXEC "call enable_triggers ()"

The individual procedure calls are shown in the preceding example of the Replicat parameter file.