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.

 

EXCLUDETAG

Valid For

(Oracle) Valid for Extract and Replicat or data pump.

Description

Use EXCLUDETAG 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.

Use EXCLUDETAG + 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.

To BottomTo Bottom

Apr 3, 2018 HOWTO
Rate this document Email link to this document Open document in new window Printable Page

In this Document

Goal
Solution
References

Applies to:

Oracle GoldenGate - Version 12.1.2.0.2 to 12.1.2.1.9 [Release 12.1]
Information in this document applies to any platform.

Goal

We are trying to exclude the REPLICAT user in OGG 12.1.2 capturing from Oracle 12.1.0.2 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'.

 

Solution

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 v12.2.0.1.

 

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 v12.2.0.1+, 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.

 

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram