Making Local-Only Changes in an Active-Active Oracle GoldenGate Configuration

Oracle GoldenGate has always allowed customers to control replication at several levels, including objects, rows, columns, and DDL. However, in active-active or active-passive replication architectures, there are cases where you want to make local-only changes without replicating them to other active GoldenGate replicas within the configured replication set. Examples include:

This section describes how to configure local-only changes. This procedure prevents the selected transaction from replicating from the source database to its peer. It does not stop other traffic, prevent a concurrent transaction from the peer from arriving, or make the two databases consistent by itself.

Prerequisites

Before you begin, ensure that:

Plan the local-only change

In the example below, Database A and Database B are both actively receiving changes from the application. Each database has a primary Extract that captures local application changes, a Distribution Service path that transports the local trail, and a Replicat that applies changes arriving from the peer. When using GGHub, a Distribution Service is not required when the active Extract and active Replicat are on the same GGHub.

For any local-only change made on a database, the relevant filter is the Extract capturing changes from that database. The following table describes the component and action flow that affects local-only changes.

Note: The Distribution Service is downstream of the Extract, so stopping the Distribution Path is not the isolation mechanism. An untagged local-only change can remain in the source trail and be sent after the path is restarted.

GoldenGate component Function or action Role in local-only changes
DBMS_XSTREAM_ADM.SET_TAG Sets a redo tag for subsequent DML and DDL generated by the current database session. Marks the local-only change transaction before any local-only change DML runs.
Local Extract Reads the local database redo stream. Uses TRANLOGOPTIONS EXCLUDETAG to discard the tagged local-only change before it enters the outbound trail.
Distribution Service path (optional) Moves the source trail to the peer deployment. Does not see a local-only change that the local Extract excluded.
Peer Replicat Applies changes that arrive from the peer. Does not receive the local-only changes.

Understand tag values

A tag is a hexadecimal byte string. The value passed to DBMS_XSTREAM_ADM.SET_TAG must represent the same bytes as the hexadecimal value in the local Extract’s EXCLUDETAG configuration.

The following is an illustrative two-database layout. The names TAG_A and TAG_B describe the database where the tag is filtered; they are labels for explanation, not literal parameter values.

Database Inbound Replicat parameter file Local outbound Extract parameter file To suppress a local-only change made locally
Database A DBOPTIONS SETTAG 0A01 on Replicat A, which applies B→A changes. TRANLOGOPTIONS EXCLUDETAG 0A01 on Extract A, which captures A→B changes. Set the dedicated session tag to DBMS_XSTREAM_ADM.SET_TAG(tag => ‘0A01’).
Database B DBOPTIONS SETTAG 0B01 on Replicat B, which applies A→B changes. TRANLOGOPTIONS EXCLUDETAG 0B01 on Extract B, which captures B→A changes. Set the dedicated session tag to DBMS_XSTREAM_ADM.SET_TAG(tag => ‘0B01’).

Use the values that are actually configured in the environment. Do not infer the value from the process name, database name, or direction. If an Extract has several EXCLUDETAG lines, each line excludes one tag. If the Extract uses EXCLUDETAG +, any non-null tag is excluded; use a documented maintenance tag rather than an arbitrary value. Do not use EXCLUDETAG NULL as a shortcut: it has the opposite meaning and excludes untagged changes.

If the local-only change includes DDL, inspect both TRANLOGOPTIONS and DDLOPTIONS in the Extract parameter file. In Oracle database, tag filtering specified with TRANLOGOPTIONS applies to DML, while a separately specified DDLOPTIONS EXCLUDETAG controls DDL. The procedure below is intended primarily for data (DML) local-only changes; validate DDL filtering separately before using it.

Illustrative loop-prevention configuration

-- On Database A: Replicat A applies B→A changes
DBOPTIONS SETTAG 0A01

-- Extract A captures A→B changes
TRANLOGOPTIONS EXCLUDETAG 0A01

-- On Database B: Replicat B applies A→B changes
DBOPTIONS SETTAG 0B01

-- Extract B captures B→A changes
TRANLOGOPTIONS EXCLUDETAG 0B01

Do not change an existing loop-prevention value just to perform a local-only change unless the change has been reviewed as an operational configuration change.

Perform a local-only change

Perform the following steps on the specified database. The example performs a local-only change on Database A, with no replication to Database B.

1. Inspect the active-active path Connect to the deployment that manages Database A. View the parameter files for Extract A and Replicat A, then confirm that the processes and the A→B Distribution Path are operating normally.

ADMINCLIENT Inspect the active-active path from the source deployment
OGG (not connected) 1> CONNECT https://<source-admin-host>:<port> <source-deployment> AS <deployment-user>
OGG (https://<source-admin-host>:<port> <source-deployment>) 2> VIEW PARAMS <extract-a>
OGG (https://<source-admin-host>:<port> <source-deployment>) 3> VIEW PARAMS <replicat-a>
OGG (https://<source-admin-host>:<port> <source-deployment>) 4> INFO EXTRACT <extract-a>
OGG (https://<source-admin-host>:<port> <source-deployment>) 5> INFO DISTPATH <distpath-a-to-b> DETAIL

Confirm that:

2. Prepare the local-only change

3. Set the session tag and perform the local-only change Connect directly to Database A using the service or PDB that owns the targeted objects. Run the appropriate DBMS_XSTREAM_ADM.SET_TAG command before the first local-only change DML or DDL statement. The procedure affects only the current session and applies to subsequent redo entries generated by that session.

Note: If automatic CDR is enabled on any tables that you are going to change with DML, you can overwrite the value of the timestamp columns, typically CDRTS$ROW, through your SQL statement. You want this value to be identical to the record of the source on the other database. That way, if a conflict occurs on this row in the future, it will be handled properly by the conflict resolution rules.

-- Use the tag that Extract A excludes. This example uses 0A01.
SELECT RAWTOHEX(DBMS_XSTREAM_ADM.GET_TAG) AS TAG_BEFORE FROM DUAL;

EXEC DBMS_XSTREAM_ADM.SET_TAG(tag => '0A01');

SELECT RAWTOHEX(DBMS_XSTREAM_ADM.GET_TAG) AS TAG_ACTIVE FROM DUAL;

– Example only: replace with the approved, narrowly scoped local-only change.

UPDATE hr.customers
   SET credit_status = 'REVIEWED',
       cdrs$row = TIMESTAMP '2026-09-01 20:55:48'
 WHERE customer_id = 12345;

COMMIT;

-- Clear session state before the session is reused.
EXEC DBMS_XSTREAM_ADM.SET_TAG(NULL);

SELECT RAWTOHEX(DBMS_XSTREAM_ADM.GET_TAG) AS TAG_AFTER FROM DUAL;

Session rule The tag is session state, not a transaction option. Keep the tagging call, local-only change statements, commit, and clearing call on the same physical database session. Run no unrelated DML while the tag is active. Because SET_TAG is not transactional, do not expect a rollback to undo the tag setting.

For a multi-statement local-only change, keep the tag active for the complete approved local-only change transaction or batch, and clear it immediately afterward. For a connection pool, closing the dedicated session is safer than returning it to the pool with an uncertain tag state.

4. Complete and verify the local-only change

For auditability, record the database and service, physical session or maintenance tool, exact tag in hexadecimal, Extract and Distribution Path names, Replicat name, SQL or change-script checksum, affected keys, before and after values, commit timestamp, AdminClient verification output, and the direct peer query result.

AdminClient command reference

The commands used on this page are available in the Oracle GoldenGate 26ai AdminClient command reference:

Command Use in this procedure 26ai reference
CONNECT Connect to the Service Manager or Administration Service for a deployment. CONNECT
VIEW PARAMS Inspect the Extract and Replicat parameter files. VIEW PARAMS
EDIT PARAMS Add a specific local-only change tag when the existing Extract configuration has no suitable excluded value. EDIT PARAMS
INFO EXTRACT Check Extract status, lag, and checkpoints. INFO EXTRACT
INFO DISTPATH Check the Distribution Path status and detailed checkpoints. INFO DISTPATH
STATS EXTRACT Reset and inspect source Extract operation statistics during the verification window. STATS EXTRACT
STATS REPLICAT Inspect target-table and CDR statistics during the verification window. STATS REPLICAT
STOP EXTRACT / START EXTRACT Restart the local Extract after adding a new EXCLUDETAG line. STOP EXTRACT and START EXTRACT

Related references

Note: This Microservices Architecture procedure uses a Distribution Service and Distribution Path. It does not use a Classic Architecture Extract Pump. The local-only decision is made at the originating Oracle Extract by filtering the session-tagged redo.