When working with Oracle GoldenGate (OGG), adding a new table to an already running replication setup is a common requirement. This blog post walks you through integrating a new table, performing an initial direct load, and validating that changes are replicated correctly, including how to use the SOURCEISTABLE parameter in the initial-load Extract.
In this example, we are adding the CUSTOMERS_TBL table from the avuser schema (source) to the ecmuser schema (target). Initially, avuser.CUSTOMERS_TBL has 15,505 rows, while ecmuser.CUSTOMERS_TBL is empty. We then configure the necessary Extract and Replicat parameter files, use direct load for the initial data population, and verify subsequent DML operations are replicated.
Enable Trandata
Before Oracle GoldenGate can capture changes, you must enable supplemental logging (TRANDATA) for the source table. This allows OGG to track row changes. If TRANDATA is already enabled, GoldenGate will show an informational message:
GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 252> ADD TRANDATA estpdb.avuser.customers_tbl
INFO OGG-15131 Logging of supplemental redo log data is already enabled for table ESTPDB.avuser.CUSTOMERS_TBL.
INFO OGG-15135 TRANDATA for instantiation CSN has been added on table ESTPDB.avuser.CUSTOMERS_TBL.Update the Online Extract Parameter File
The online Extract is responsible for continuously capturing changes from the source database. Add the new table reference:
TABLE estpdb.avuser.CUSTOMERS_TBL, TOKENS(BRAND_ID=15, REGION_ID=1);
Update the Online Replicat Parameter File
Next, include the corresponding mapping in the online Replicat
MAP estpdb.avuser.CUSTOMERS_TBL,
TARGET intpdb.ecmuser.CUSTOMERS_TBL,
KEYCOLS(CUSTOMER_ID),
COLMAP(
USEDEFAULTS,
BRAND_ID=@TOKEN('BRAND_ID'),
REGION_ID=@TOKEN('REGION_ID')
);Stop and Start the Running Extract and Replicat
With the updated parameters, stop and restart the existing online Extract/Replicat so they recognize the newly added table:
GGSCI> STOP AVUKEXT
GGSCI> STOP AVREP
GGSCI> START AVUKEXT
GGSCI> START AVREP
GGSCI> INFO ALLGGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 264> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING AVUKEXT 00:00:00 00:00:49
REPLICAT RUNNING AVREP 00:00:00 00:00:08 Create an Initial-Load Extract with SOURCEISTABLE
When migrating existing data from the source to the target for a newly added table, you can use direct load with a unique Extract and Replicat pair. In your initial-load Extract, specify SOURCEISTABLE and the new table, plus any tokens:
EXTRACT EXTUSER
SOURCEISTABLE
USERIDALIAS ggadmin
RMTHOST ggate2, MGRPORT 7809
RMTTASK REPLICAT, GROUP repuser
TABLE estpdb.avuser.CUSTOMERS_TBL, TOKENS(BRAND_ID=15, REGION_ID=1);
Create an Initial-Load Replicat
REPLICAT REPUSER
USERIDALIAS ggadmin
DISCARDFILE ./dirrpt/repuser.dsc, APPEND
MAP estpdb.avuser.CUSTOMERS_TBL,
TARGET intpdb.ecmuser.CUSTOMERS_TBL,
KEYCOLS(CUSTOMER_ID),
COLMAP(
USEDEFAULTS,
BRAND_ID=@TOKEN('BRAND_ID'),
REGION_ID=@TOKEN('REGION_ID')
);
Start the Initial-Load Extract and Replicat
GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 270> START EXTUSER
...
INFO OGG-02911 Processing table ESTPDB.avuser.CUSTOMERS_TBL.
INFO OGG-06510 Using the following key columns for target table INTPDB.ECMUSER.CUSTOMERS_TBL: CUSTOMER_ID.
...
INFO OGG-00991 EXTRACT EXTUSER stopped normally.
INFO OGG-00994 REPLICAT repuser stopped normally.
Start of Initial load direct load extract
After we start the extract process automatically, it will initiate the replicat as well. Before that should not start direct load replicat
GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 270> start extuser
Sending START request to MANAGER ...
EXTRACT EXTUSER starting
2024-06-21T21:39:27.550+0530 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle: REPLICAT repuser started.
2024-06-21T21:39:28.099+0530 INFO OGG-02911 Oracle GoldenGate Capture for Oracle, extuser.prm: Processing table ESTPDB.avuser.CUSTOMERS_TBL.
2024-06-21T21:41:25.404+0530 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle: MAP resolved (entry estpdb.avuser.CUSTOMERS_TBL): MAP "ESTPDB"."avuser"."CUSTOMERS_TBL", Target intpdb.ecmstg.CUSTOMERS_TBL, KEYCOLS(CUSTOMER_ID), COLMAP(USEDEFAULTS, BRAND_ID=@token('BRAND_ID'), REGION_ID=@token('REGION_ID')).
2024-06-21T21:41:36.314+0530 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle: The definition for table ESTPDB.avuser.CUSTOMERS_TBL is obtained from the trail file.
2024-06-21T21:41:36.315+0530 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle: Using the following key columns for target table INTPDB.ECMSTG.CUSTOMERS_TBL: CUSTOMER_ID.
2024-06-21T21:41:40.049+0530 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, extuser.prm: EXTRACT EXTUSER stopped normally.
2024-06-21T21:41:44.886+0530 INFO OGG-00994 Oracle GoldenGate Delivery for Oracle: REPLICAT repuser stopped normally.As per the output it appears the replicat was stopped normally without any issue s
SQL> select count(*) from ecmuser.customers_tbl;
COUNT(*)
----------
15506Verifying the Real-Time replication
SQL> commit;
Commit complete.
-- Inserted a row and the new row was receieved
SQL>
SQL> select count(*) from ecmuser.customers_tbl;
COUNT(*)
----------
15507
SQL> Review of the stats
GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 328> stats avukext
Sending STATS request to EXTRACT AVUKEXT ...
Start of Statistics at 2024-06-21 21:59:41.
Output to ./dirdat/av:
Extracting from ESTPDB.avuser.CUSTOMERS_TBL to ESTPDB.avuser.CUSTOMERS_TBL:
*** Total statistics since 2024-06-21 21:44:24 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2024-06-21 21:44:24 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2024-06-21 21:44:24 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2024-06-21 21:44:24 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.Adding a new table to an active Oracle GoldenGate replication involves enabling supplemental logging (TRANDATA) on the source table, updating your existing Extract/Replicat parameter files, and restarting them to include the new table. To load historical data without disrupting ongoing replication, you can set up a dedicated Extract (using SOURCEISTABLE) and Replicat for an initial direct load of all existing rows. After verifying the row counts on both the source and target, any new DML changes will flow through the regular Extract and Replicat, ensuring continuous synchronization of the newly added table.
