Oracle GoldenGate - Adding schemas/tables to replication - DDL

Oracle GoldenGate - Adding schemas/tables to replication - DDL

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

 

    • Related Articles

    • Oracle GoldenGate - Adding schemas/tables to replication - simplified

      Adding schemas/tables to replication - simplified Author: Bobby Curtis, MBA Date: September 27, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: ADDING SCHEMA/TABLE TO REPLICATION (SIMPLIFIED) CONCLUSION: ...
    • Oracle GoldenGate - Adding schemas to replication

      Adding schemas to replication Author: Bobby Curtis, MBA Date: September 25, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: ADDING A SCHEMA/TABLE BETWEEN DATABASES ADDING SCHEMA/TABLE TO REPLICATION CONCLUSION: ...
    • Oracle GoldenGate - Exception Handling

      Oracle GoldenGate Exception Handling Author: Bobby Curtis, MBA Date: October 3, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: EXCEPTION TABLE(S) INFO Exception Table Exception Macro Update Replicat CONCLUSION: ...
    • Oracle GoldenGate - Installing Oracle GoldenGate binaries

      Installing Oracle GoldenGate binaries Author: Bobby Curtis, MBA Date: August 4, 2023 Organization: RheoData Table of Contents INTRODUCTION: Prerequisites: STEP 1: DOWNLOAD ORACLE GOLDENGATE 21.10 STEP 2: TRANSFER INSTALLATION FILES STEP 3: EXTRACT ...
    • Oracle GoldenGate - Backup Oracle GoldenGate Deployments

      Backup Oracle GoldenGate Deployments Author: Bobby Curtis, MBA Date: January 23, 2025 Updated: October 13, 2023 Organization: RheoData Table of Contents INTRODUCTION: Assumption: STEP 1: ACCESS THE ORACLE GOLDENGATE SERVER STEP 2: IDENTIFY THE ...