Oracle GoldenGate 21c MA -- How to Add Additional Tables to Existing Replication

Problem

You want to add additional tables to existing data replication configuration in Oracle GoldenGate 21c Microservices Architecture

In the configuration one Extract ext1 and one Replicat rep1 are used to replicate data between source and target databases

Solution

1) Using either the Adminstartion service or the adminclient, stop the extract:

OGG (https://127.0.0.1:7123 gg_inst1) 8> stop extract ext1
2023-03-04T16:44:35Z  INFO    OGG-08100  Sending STOP request to Extract group EXT1.
2023-03-04T16:44:35Z  INFO    OGG-02964  Extract group EXT1 is down (gracefully).

2) Wait until replication catches up with the last transaction in the trail file, and stop the replicat:

Connect to the target database:

OGG (https://127.0.0.1:7123 gg_inst1) 14> dblogin useridalias <useridalias>
Successfully logged into database. 

Wait until the lag shows "At EOF":

OGG (https://127.0.0.1:7123 gg_inst1 as <useridalias>) 15> lag replicat rep1

Sending GETLAG request to Replicat group REP1 ...

Low watermark lag: 1,109 seconds
High watermark lag: 1,109 seconds
At EOF, no more records to process

Stop the replicat:

OGG (https://127.0.0.1:7123 gg_inst1 as <useridalias>) 17> stop replicat rep1
2023-03-04T16:56:07Z  INFO    OGG-08100  Sending STOP request to Replicat group REP1.
2023-03-04T16:56:07Z  INFO    OGG-02965  Replicat group REP1 is down (gracefully).

3) At the source database, add trandata for the new tables which to be included in the replication configuration:

Connect to the source database and issue:

add trandata <schema>.<tablename> allcols

4) Update the parameter file of the Extract to include the new tables:

TABLE <schema>.<table_name>;

5) Start the Extract:

OGG (https://127.0.0.1:7123 gg_inst1) 22> start extract ext1
2023-03-04T17:11:58Z  INFO    OGG-00975  Extract group EXT1 starting.
2023-03-04T17:11:58Z  INFO    OGG-15426  Extract group EXT1 started.

6) Perform synchronization of the tables from the source to the target database:

Get the current SCN of the source database:

SQL> select current_scn from v$database:

CURRENT_SCN
--------------
39370639522458

Using the identified SCN, perform either:

• export/import of the tables using expdp/impdp with the FLASHBACK_SCN export option, or
• precreate the tables at the target, and using a db link, perform insert into the tables using the flashback query clause (AS OF SCN)

7) Add the tables into the Replicat parameter file, using the FILTER option to start replication after the SCN at which the tables were loaded into the target database:

MAP <schema>.<table_name>, TARGET <schema>.<table_name>, FILTER ( @GETENV ('TRANSACTION', 'CSN') > <scn>); 

8) Start the Replicat:

OGG (https://127.0.0.1:7123 gg_inst1) 3> start replicat rep1
2023-03-04T17:49:32Z  INFO    OGG-00975  Replicat group REP1 starting.
2023-03-04T17:49:32Z  INFO    OGG-15445  Replicat group REP1 started.

9) Once the Replicat catches up and the tables are in sync, remove the FILTER option from the parameter file and restart the Replicat