Avoiding rows being added to a target system
With Integrated Extract, you should 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.
tag in your data pump or Replicat parameter file to specify changes to be excluded from trail files. The limitation for this parameter is that the tag value can be up to 2000 hexadecimal digits (0-9A-F) or the plus sign (
+). You can have multiple
EXCLUDETAG lines, but each
EXCLUDETAG should have a single value. By default, Replicat the individual records every change it applies to the database by
00 in both classic mode or integrated mode.Compare with older versions, new trail file contains tag tokens, which would not introduce problems for older trail readers.
+ to ignore the individual records that are tagged with any redo tag.
To tag the individual records, use the
DBOPTIONS parameter with the
SETTAG option in the Replicat parameter file. Use these parameters to prevent cycling (loop-back) of Replicat the individual records in a bi-directional configuration or to filter other transactions from capture. The default
SETTAG value is 00. Valid value is any single Oracle Streams tag. A tag value can be up to 2000 hexadecimal digits (0-9 A-F) long. For more information about Streams tags, see Oracle Streams Replication Administrator’s Guide.
In this Document
Oracle GoldenGate – Version 220.127.116.11.2 to 18.104.22.168.9 [Release 12.1]
We are trying to exclude the REPLICAT user in OGG 12.1.2 capturing from Oracle 22.214.171.124 bi-directional. But using EXTRACT TRANLOGOPTIONS EXCLUDEUSER to exclude a user in one of the PDBs:
TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG
EXTRACT is returning the message:
2015-03-25 15:44:27 ERROR OGG-00303 Could not find USER_ID corresponding to USERNAME ‘ZEPP01.ZE_GG’.
When using Integrated EXTRACT, EXCLUDEUSER is currently not supported in OGG V12.1.2. We have a enhancement request tracked via Bug 21891811 – Support EXCLUDEUSER in Integrated Extract (IE) for DDL records which has been implementd in OGG v126.96.36.199.
For excluding users in a 12c multitenant DB, OGG v12.1.2 has a new EXTRACT param TRANLOGOPTIONS EXCLUDETAG <nn>. This is typically used to exclude the REPLICAT user in bi-directional configurations.
Changes made by Integrated REP are tagged by default in redo as 00. So adding the EXTRACT param TRANLOGOPTIONS EXCLUDETAG 00
Would exclude those operations.
The tag can also be explicitly set in REPLICAT using:
DBOPTIONS SETTAG 0935
Then in EXTRACT param:
TRANLOGOPTIONS EXCLUDETAG 0935
With OGG v188.8.131.52+, you can use the earlier option of TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG
Also following Bug fixes are mandatory to use above options
Bug 25830410 : EXCLUDEUSER in IE does not support CDB
Bug 25496669 : EXCLUDEUSER in IE against a PDB checks for user_id in CDB
Oracle GoldenGate for Java –JMS CaptureOracle GoldenGate for Java also provides the capability to capture data present in JMS text messages and write it to Oracle GoldenGate Trail Files, from where it can be delivered to any of the supported target systems.