Adding schemas/tables to replication - DDL

Author: Bobby Curtis, MBA

Date: October 1, 2023

Updated: January 23, 2025

Organization: RheoData

Table of Contents

INTRODUCTION:

ADDING SCHEMA/TABLE TO REPLICATION (DDL)

CONCLUSION:

REFERENCE:

Introduction:

After Oracle GoldenGate is configured and data is flowing, there are times where additional schemas need to be added to the replication stream.  In order to do this, an administrator needs to remember that replication has to stay moving while adding these schemas.  The process seems to be simple but may still cause issues if not done correctly.  In this document, we will take a look at how to add a schema to existing replication.

The easiest way of thinking of adding a schema to replication is to define the replication pipeline as an equation – one-side has to equal the other side in one direction:

A = A

Based on this, we can quickly add a schema and enabled bi-direction replication for that schema after it has been added.

Note: After further discussion with ICON team, the concept of adding a schema could be as simple as the development team running a series of SQL statements on one sides of the equation; resulting in an empty schema in both source and target databases.

Adding Schema/Table to replication (DDL)

At times, developers, who are responsible for building out schemas for applications will be allowed to build empty schemas in both the source and target databases.  When this happens, adding a schema/table to replication will become simpler.   With no need to move data, due to schemas being empty, a schema/table can be added and replication started.

The following steps illustrate how enabling replication would be done with empty schemas via DDL in both source and target databases.  When doing it this way, a best practice would to be create a separate replication group for only DDL changes.

The steps to follow are:

1.     Create a CDC extract without starting it. 

adminclient> add extract <extract>, tranlog, begin now
adminclient> add exttrail aa, extract <extract>, megabytes 500

2.     Make sure the parameter file for the DDL extract has DDL enabled.

Note: The DDL parameter by itself will capture and replicat all DDL that is generated within the schema being replicated.

DDL

An example of the parameter file for the extract should look like this:

EXTRACT EXTPDB1

USERIDALIAS SourceCDB DOMAIN OracleGoldenGate

EXTTRAIL aa

DDL

TABLE <pluggable_database>.<schema>.*;

3.     Create DDL replicat without starting it.

Note:  The schema that is being replicated with DDL on first pass does not need to be created on the target side.  The DDL operation will create the schema and populate with the objects as they are processed.

Adminclient> add replicat <replicat>, integrated, exttrail aa

Make sure the parameter file for the DDL replicat has DDL enabled.

DDL

An example of the parameter file for the replicat should look like this:

REPLICAT REPPDB2

USERIDALIAS TargetPDB DOMAIN OracleGoldenGate

DDL

MAP <pluggable_database>.<schema>.*, TARGET <schema>.*;

4.     Start DDL replicat

adminclient> start replicat <replicat>

At this point, you may start seeing the DDL that was ran on the source start to be applied to the target database.

Conclusion:

Congratulations! You have successfully added a schema/table via DDL to your replication process.

Reference:

https://docs.oracle.com/en/middleware/goldengate/core/21.3/oracle-db/configuring-ddl-support.html#GUID-A2997164-CBDF-4E9B-B2B0-2CE6889BC2DF