At one of my recent client sites, there was a requirement to enable GoldenGate Change Data Capture for a schema with over 800 tables and also application tables were frequently added which also required to have CDC enabled without the need to stop and restart the extract because we had made a change to the extract parameter file when we added for example the new table name.
Using information gathered from AWR reports and DBA_TAB_MODIFICATIONS, we were able to identify the top 20 tables which encountered the highest level of DML activity and these tables (as well as their associated child tables) were placed in 5 different extract groups.
The sixth extract would be like a ‘catch-all’ extract for all other tables as well as any new tables which were subsequently added. Rather than have to list 780 tables in the extract parameter file we instead used the TABLEEXCLUDE clause to list the 20 tables which were contained in the 5 other extract group and then finally use a TABLE [SCHEMA NAME].* to account for all other tables which had not been explicitly listed in the extract parameter files.
Since we had enabled DDL replication, when a new table was created on the source, it was automatically created at the target as well.
Here is the ‘catch-all’ extract parameter file we used and I will briefly explain some of the parameters used.
CACHEMGR CACHESIZE 8G
userid gg_owner@RELIS password xxxxxxxxx
DDL include ALL
ddloptions addtrandata, report
When the extract process starts, if there are many tables listed in the parameter file, GoldenGate has to query the database and build a metadata records for each table listed via the TABLE clause. If there are many tables involved, it can affect startup time for the extract. DYNAMICRESOLUTION causes the record to be built one table at a time, instead of all at once. The metadata of any given table is added when Extract first encounters the object ID in the transaction log, while record-building for other tables is deferred until their object IDs are encountered.
DDL INCLUDE ALL
DDL support for not only objects referenced in MAPPED clauses but DDL operations that pertain to tables that are not mapped with a TABLE or MAP statement
DDLOPTIONS ADDTRANDATA, REPORT
Enable Oracle table-level supplemental logging automatically for new tables created with a CREATE TABLE statement. It produces the same results as executing the ADD TRANDATA command in GGSCI.
Also controls whether or not expanded DDL processing information is written to the report file. The default of NOREPORT reports basic DDL statistics. REPORT adds the parameters being used along with a step-by-step history of the operations that were processed as part of the DDL capture
CACHEMGR CACHESIZE 8G
See for more information on CACHEMGR and CACHESIZEparameter