Category Archives: GoldenGate 12.2

GoldenGate Loop Avoidance GG 12.2

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.

 

GoldenGate Integrated Capture Mode

The Integrated Capture GoldenGate Mode also known as the Integrated Extract Goldengate process in 12c (also backported to 11gr2) is one of the more interesting and useful feature released with this version. This capture process is the component responsible for extracting the DML transactional data and DDL’s from the source database redo log files. This data is then written to local trail files which eventually is move to the destination database to be applied there.

Here are the list of topics to be covered in this article.

• What is the GoldenGate Integrated Capture Mode?
• Integrated Extract Goldengate vs Classic Capture
• On-Source Capture
• Downstream Capture
• Prerequisites
• Configuration
• Monitoring/Views

What is the GoldenGate Integrated Capture Mode?

Integrated Capture Mode (IC) is a new form of the Extract process, were in this process is moved closer, inside the source database. In the traditional Classic extract process, the extract works on the redo logs outside the domain of the actual database. In this new integrated capture mode, a server Log Miner process is started which extracts all the DML data and DDLS statements creating Logical Change Records (LCR’s). These are then handed to the Goldengate memory processes which writes these LCR’s to the local trail files. This Log Miner server process is not the Log Miner utility we are used to in the database but is a similar mechanism which has been tuned and enhanced for specific use by the Goldengate processes.

The purpose of moving this inside the database is to be able to make use of the already existing internal procedures in the database, making it easier to provide support for the newer features of Oracle faster than was previously possible. Due to this change, Oracle is now able to provide the following.

• Full Support of Basic, OLTP and EHCC compressed data.
• No need to fetch LOB’s from tables.
• Full Secure File support for Secure file lobs.
• Full XML support.
• Automatically handles addition of nodes and threads in RAC environment.
• Senses node up down in RAC and handles it in its processes transparently.
Integrated Capture vs Classic Capture

The Integrated Capture mode offers the following.

• Integrated with Database features
• Allows to mine earlier versions of integrated capture on secondary
• More efficient. It does not have to fetch data because of the datatype, etc..
• No longer necessary to set this: Threads, ASMUSER, ASMBUF, DBLOGREADER, DECRYPASSWORD
• For RAC no additional manual steps required. Transparent with RAC.
Integrated Capture Modes

Integration capture supports two types of deployment configurations. They are:
• On-Source Capture
• Downstream Capture

On-Source Capture

When the integrated capture process is configured using the on-source capture mode, the capture process is started on the actual source database server itself. Changes as they happen on source database will be captured locally, routed, transformed and applied on target database in near real-time.

This may seem convenient but consideration needs to be given to the additional workload that will be placed by this process on the database server. However if real-time replication is required this is the best option.

Note: All features are supported in both On-Source or Downstream Deployment
Downstream Capture

In the downstream mode, the capture process is configured to run on a remote database host. All the database redo logs from the source database are shipped to this remote server using Dataguard technology and then mined there by the capture process.

In this mode there is an inherent latency introduced due to the fact that the redo log on the source needs to switch first before the log can be shipped downstream. So there will be some delay in the replication of data to a target database as the extraction will be delayed due to the log switch. The main benefit of this setup however is the offset of the resource usage on the source server.

In this mode, to overcome the log switch latency, Oracle has provide a near Real time capture using Standby redo logs for extraction. In this configuration the redo log from the source continuously writes into the standby redo logs of the downstream database. The capture process directly capture the data from here.

It is important to keep in mind when deciding whether to use the Integrated capture or the classic capture mechanism that both configuration will remain available in future releases. However Oracle recommends to use the new Integrated capture mechanism as Oracle will not be adding new features to classic capture in the future and it will only be there for legacy support purposes.
Prerequisites

The database where integrated capture runs:
• Must be at least 11.2.0..3
• Database patch 1411356.1 must be installed.
• Works with Oracle 12c.

GoldenGate Setup of a Hub

Setup Of GG Hub Article

If the GG Hub server is a standalone environment then the first step is to install the most recent Oracle client on the GoldenGate hub server, choosing the administrator installation.

In a hub configuration, GoldenGate is installed only on the hub server, it is not installed on the database servers. The Oracle client is installed on the hub server. We will be using the thick client.

In a hub environment that is install in a fault tolerant configuration, (RAC, DBFS, and XAG  w/DataGuard).

GoldenGate sample environment file ggsora12_env

Here is a sample GoldenGate environment file, feel free to comment about changes or additions


# This should be a .ggora12_env file

# This variable could be used to identify a site
export GG_SITE=01

# This could be used to identify a location
export GG_LOC=CO

# if there is only one DB related to this GG home,
# set NEW_ORACLE_SID to avoid constant switch between a DB env and its GG env
export NEW_ORACLE_SID=MRC01D011; . ~/.std_profile

# otherwise, set NEW_ORACLE_SID to dummy if there are multiple replicated databases related to same GG home
#export NEW_ORACLE_SID=dummy; . ~/.std_profile

# uncomment next three lines if  ORACLE_SID is dummy
# export ORACLE_HOME=/oracle/product/11.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib/$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

export GGS_HOME=/oracle/product/gg12.1

export PATH=$GGS_HOME:$PATH

# For OEM12c GG monitoring
export JAVA_HOME=/oracle/product/12.1.0/oem_1/agent/core/12.1.0.2.0/jdk/jre
export PATH=$JAVA_HOME/bin:$PATH
# export LD_LIBRARY_PATH=$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH

#############################################
## For GI Agent (XAG)
##############################################
export XAG_HOME=/oracle/product/xag71
export PATH=$XAG_HOME/bin:$PATH

PS1="\\
\${PWD} \\
\${SNAME} [\${ORACLE_SID}] [GG12_site$GG_SITE"_"$GG_LOC]-> "
export PS1

alias ggsora12='. $HOME/.ggsora12_env;cd $GGS_HOME'
alias xag='. $HOME/.ggsora12_env;cd $XAG_HOME'
alias ggstatus='$XAG_HOME/bin/agctl status goldengate testdb01_oggapp'