This is an advanced topic in Oracle Goldengate Microservices when you need to add additional tables to an existing running EXTRACT-REPLICAT pair
There is a few ways you can achieve this, but the best way is to have a transactionally consistent version of your table export with FLASHBACK_SCN and then restarting your REPLICAT with that CSN using the FILTER clause in the REPLICAT parameter file.
This has been tested on Oracle Goldengate Microservices 19c, 21c and 23ai
Environment:
We start with 1 EXTRACT and 1 REPLICAT as below which is already running replication for a table called ORDERS. We will add another table ORDERS_2 which has continuous writes happening on it.
Sample Initial Extract
EXTRACT EXT
USERIDALIAS SourceDBCS DOMAIN OracleGoldenGate
EXTTRAIL XX
TABLE ORCL122_PDB1.DEMO.ORDERS;
Sample Initial Replicat
REPLICAT REP
USERIDALIAS TargetDBCS DOMAIN OracleGoldenGate
MAP ORCL122_PDB1.DEMO.ORDERS, TARGET DEMO.ORDERS;
Steps:
1) Stop the Extract and Replicat together
2) Enable Supplemental Logging at table level on source side3) Add the new table in Extract parameter file and save it
3) Add the new table in Extract parameter file and save it
Sample New Extract
EXTRACT EXT
USERIDALIAS SourceDBCS DOMAIN OracleGoldenGate
EXTTRAIL XX
TABLE ORCL122_PDB1.DEMO.ORDERS;
TABLE ORCL122_PDB1.DEMO.ORDERS_2;4) Start the Extract
5) Get the current SCN from the source database
SQL> select current_scn from v$database;
CURRENT_SCN
------------------------
153115366
6) Re-sync the newly added table from source to target (using normal export/import).
Make sure to use FLASHBACK_SCN parameter for the export
expdp demo/*****@ORCL122_PDB1 directory=EXPORT_DIR tables=orders_2
dumpfile=orders_%U.dmp
parallel=4
logfile=orders.log
FLASHBACK_SCN=153115366
reuse_dumpfiles=y
ENCRYPTION_PASSWORD=*****
ENCRYPTION_ALGORITHM=AES256
impdp demo/*****@demopdb
tables=orders_2
directory=import_dir
dumpfile=orders_%U.dmp
logfile=import_orders.log
ENCRYPTION_PASSWORD=*****
parallel=4
7) Add the table in the Replicat parameter file including the below option till 11g ( FILTER ( @GETENV (“TRANSACTION”, “CSN”) > <scn_number obtained from source db>) ) and for 12c onwards it will be like ( FILTER ( @GETENV (‘TRANSACTION’, ‘CSN’) > <scn_number obtained from source db>) ) as shown in the below example
eg:- till OGG 11g :
MAP source.test1, TARGET target.test1, FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);
MAP source.test2, TARGET target.test2, FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);
for OGG 12c onwards the statement will be like below:
MAP source.test1, TARGET target.test1, FILTER ( @GETENV ('TRANSACTION', 'CSN') > 5343407);
MAP source.test2, TARGET target.test2, FILTER ( @GETENV ('TRANSACTION', 'CSN') > 5343407);
Sample New Replicat
REPLICAT REP
USERIDALIAS TargetDBCS DOMAIN OracleGoldenGate
MAP ORCL122_PDB1.DEMO.ORDERS, TARGET DEMO.ORDERS;
MAP ORCL122_PDB1.DEMO.ORDERS_2, TARGET DEMO.ORDERS_2, FILTER ( @GETENV('TRANSACTION', 'CSN') > 153115366);
8) Start the Replicat and let the transactions sync-up with lag becoming 0
9) Stop Replicat after the lag is 0. Remove FILTER clause from Replicat parameter file
Sample New Replicat After Lag is 0
REPLICAT REP
USERIDALIAS TargetDBCS DOMAIN OracleGoldenGate
MAP ORCL122_PDB1.DEMO.ORDERS, TARGET DEMO.ORDERS; MAP ORCL122_PDB1.DEMO.ORDERS_2, TARGET DEMO.ORDERS_2;
10) Start Replicat again
Important Notes:
[1] Replicat should be stopped before you get the SCN. Because, if the replicat is still applying, there are chances that you can skip past the recorded SCN. So, it’s better to stop the replicat before you load the data to be sure that the SCN is still in the trail files.
[2] If you stop the extract and replicat in this sequence, it will not work :
1. Stop Extract Only
2. Add Tran Data, Add Table to Extract and Re-Start Extract
3. --Replicat in Start State--
4. Take export dump with flashback scn, restore to target
5. Shutdown Replicat
6. Add FILTER ( @GETENV (‘TRANSACTION’, ‘CSN’) > 153115366); to Replicat parameter file
7. Start Replicat
But when you perform the same steps in the below sequence it will work :
1. Stop Extract and Replicat Together
2. Add Tran Data, Add Table to Extract and Start Extract
3. --Replicat in Shut State--
4. Take export dump with flashback scn, restore to target
5. Add FILTER ( @GETENV (‘TRANSACTION’, ‘CSN’) > 153115366); to Replicat parameter file
6. Start Replicat
The 2nd sequence of steps works without any issues, and there is a transactionally consistent positioning of the table from the CSN. The reason is the replicat should be stopped before you get the SCN. Becuase, if the replicat is still applying, there are chances that you can skip past the recorded SCN. So, it’s better to stop the replicat before you load the data to be sure that the SCN is still in the trail files.
[3] References
[x] : How to add new tables for OGG replication in the current running extract and replicat ? (MOS Doc ID 1332674.1)
[x] : OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (MOS Doc ID 1339317.1)
