Adding schemas/tables to replication - DDL
Author: Bobby Curtis, MBA
Date: October 1, 2023
Updated: January 23, 2025
Organization: RheoData
Table of Contents
ADDING SCHEMA/TABLE TO REPLICATION (DDL)
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.
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.
Congratulations! You have successfully added a schema/table via DDL to your replication process.