Avoid Looping in GoldenGate 12.2 with Oracle 12c Container Databases

Note: For Oracle GoldenGate 12.2, LOOPBACK is synonymous with the term “Ping Pong effect”.

We all know that Ping Pong is fun in the garage, but not in Oracle 12c container databases using GoldenGate 12.2 to replicate data for many different types of data integration use cases.

Now, what exactly do we mean by avoiding “LOOPING”,”Ping Pong effect”?

Basically, in an Active-Active/Passive environment, we don’t want a record to repeat continuously between each other.

Example A:

SystemA captures insertX and delivers insertX to SystemB
SystemB captures insertX and delivers insertX to A and it repeats over and over.

As new versions of the Oracle Database and GoldenGate are released, some things stay the same and other things change.  One of the thing that has changed is the aforementioned loop avoidance.  In prior configurations, the TRANLOGOPTIONS EXCLUDEUSER was the method to avoid the Ping-Pong effect.  This option is valid with GoldenGate 12.2 with an extract that is either classic or integrated as long as the Oracle 12c database is not a container (PDB) database.

But, you have a Container database, so with a container database you are required to use integrated capture and the EXCLUDEUSER option is no longer available.  This is due to the fact that the replicat might be running against the CDB$ROOT database or a Pluggable Database (PDB).  The excluded user might be a common user or a user specific to a single PDB.

With Integrated Extract, you should now use the TRANLOGOPTIONS EXCLUDETAG <tag> option in order to avoid Looping.  This parameter corresponds to the DBOPTIONS SETTAG <tag>.

This allows you to configure specific replicats with different tags and either allow or disallow replication of these transactions.

Syntax example and Reference Example A above:

On SystemA you will add the below to your prm files as exampled below.

Replicat01 – PDB01
DBOPTIONS SETTAG 01

———-

Replicat02 – PDB02
DBOPTIONS SETTAG 02

———-

Replicat03 – PDB03
DBOPTIONS SETTAG 03

———-

Extract01 – CDB$ROOT
DBOPTIONS EXCLUDETAG 01
DBOPTIONS EXCLUDETAG 03

This will allow cascade operations from Replicat02 while disallowing extracting transactions generated by Replicat01 and Replicat03.

By using SETTAG and EXCLUDETAG you have more flexibility on how you configure replication between various databases.  For Oracle Database 12c container databases this is the only option to avoid data looping.

So there you have it, that’s all there is to avoiding Looping/Ping-Pong effect.

 

Thanks and happy implementing!

 

 

Topics: Oracle GoldenGate, Oracle GoldenGate 12.2, Multitenant Database