Goldengate – Refresh single table

With the recent versions of GoldenGate >11.2 you can see the current SCN on with the extract process is reading, even when it is stopped:

EXTRACT  E_TEST  Last Started 2013-11-13 03:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                   2013-11-13 09:32:34  Seqno 508717, RBA 4366336
                   SCN 78.1156360444 (336163809532)

This makes it easiear to refresh a single table on a GoldenGate replication, if you can allow a small delay. There can be this need if you discover that it does not match anymore, or if you need to add a new table to the configuration. For that you do, in case of a Oracle to Oracle replication:
1 – Stop extract process and do INFO <extract> (see above). Note down the SCN between parentheses.
2 – Add the table to the parameter file (or remove an existing TABLEEXCLUDE on extract process or MAPEXCLUDE on replicat process) 
3 – Export the table from the source with FLASHBACK_SCN parameter pointing to the SCN taken from parameter 1.
4 – Make sure the replicat process has no delay with the command LAG <replicat>. You should get “At EOF, no more records to process.”
Sending GETLAG request to REPLICAT R_TEST …
Last record lag: 4 seconds.
At EOF, no more records to process.
5 – Import the table at the destination (with TABLE_EXISTS=REPLACE, if needed)
6 – Re-start the extract process.
You can make the operation simpler if you have a database link between destination and source. In that case you skip the step 3 and use directly the FLASHBACK_SCN on step 5:
impdp NETWORK_LINK=source_db TABLES=test.table1 EXCLUDE=TRIGGER 

Leave a comment

Your email address will not be published. Required fields are marked *