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:

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 both sides of the equation; resulting in an empty schema in both source and target databases.

Adding Schema/Table to replication (Simplified)

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 in both source and target databases.

1.     Edit CDC Extract and Replicats (both AM and EU)

adminclient> edit params <extract>

TABLE <schema>.*;

adminclient> edit params <replicat>

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

2.     Stop CDC extract and replicats (both AM and EU)

Note: the ER command stands for ExtractReplicat.  This will interact on all extract/replicats within the deployment.  Use caution when executing this command.

adminclient> stop er *

3.     Start CDC extract and replicats (both AM and EU)

adminclient> start er *

At this point, the new schema/table should be replicating changed data as transactions begin to come in. The statistics command should show you the number of inserts, updates and deletes that are being processed.

Conclusion:

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