News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Adding new tables to a GoldenGate Extract and using the TABLEEXCLUDE parameter

  • Posted by Gavin Soorma
  • On June 19, 2014
  • 0 Comments
  • add trandata, CACHEMGR, ddl, extract, TABLE, TABLEEXCLUDE

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.

extract ext6
CACHEMGR CACHESIZE 8G
userid gg_owner@RELIS password xxxxxxxxx
exttrail ./dirdat/cc
dynamicresolution
DDL include ALL
ddloptions addtrandata, report
TABLEEXCLUDE RELIS.AUD_TRANSACTION_LOG
TABLEEXCLUDE RELIS.NEVDIS_ERROR_LOG
TABLEEXCLUDE RELIS.NEVDIS_MSG_LOG
TABLEEXCLUDE RELIS.CLI_MERGES
TABLEEXCLUDE RELIS.CLI_MERGE_CLIENTS
TABLEEXCLUDE RELIS.CLI_MERGE_OBJECTS
TABLEEXCLUDE RELIS.CLI_MERGE_CONTACTS
TABLEEXCLUDE RELIS.CLI_MERGE_ADDRESSES
TABLEEXCLUDE RELIS.ACC_ACCOUNTS
TABLEEXCLUDE RELIS.ACC_ACCOUNT_ITEMS
TABLEEXCLUDE RELIS.ACC_COLLECTION_TYPE_CODES
TABLEEXCLUDE RELIS.ACC_COLLECTIONS
TABLEEXCLUDE RELIS.ACC_PENDING_ACCOUNT_ITEMS
TABLEEXCLUDE RELIS.ACC_ADJUSTMENTS
TABLEEXCLUDE RELIS.ACC_ALLOCATIONS
TABLEEXCLUDE RELIS.ACC_ADJUSTED_ALLOCATIONS
TABLEEXCLUDE RELIS.ACC_CLEARING_DETAILS
TABLEEXCLUDE RELIS.ACC_DEBIT_NOTE_ITEMS
TABLEEXCLUDE RELIS.ACC_DEFERRED_ADJUSTMENTS
TABLEEXCLUDE RELIS.ACC_DISHONOURED_ITEMS
TABLEEXCLUDE RELIS.ACC_FER_HOLDING_DETAILS
TABLEEXCLUDE RELIS.ACC_FOLLOWUP_LETTERS
TABLEEXCLUDE RELIS.ACC_PAYMENT_OPTION_ITEMS
TABLEEXCLUDE RELIS.ACC_PENDING_ASSOCIATIONS
TABLEEXCLUDE RELIS.ACC_RECEIPTS
TABLEEXCLUDE RELIS.ACC_RECEIPT_ITEMS
TABLEEXCLUDE RELIS.ACC_ASSOCIATIONS
TABLEEXCLUDE RELIS.VEH_INSPECTION_PAYMENT
TABLEEXCLUDE RELIS.ACC_SUNDRY_CREDITOR_DETAILS
TABLEEXCLUDE RELIS.ACC_UNDER_OVER_BANKS
TABLEEXCLUDE RELIS.ACC_PAYMENT_OPTIONS
TABLEEXCLUDE RELIS.ACC_PAYMENT_ITEMS
TABLEEXCLUDE RELIS.ACC_CARDGATE
TABLEEXCLUDE RELIS.ACC_PAYMENT_DUE
TABLEEXCLUDE RELIS.VEH_REGISTERED_OWNERS
TABLEEXCLUDE RELIS.EVT_REGISTRATIONS
TABLEEXCLUDE RELIS.EVT_VEH_SANCTIONS
TABLEEXCLUDE RELIS.VEH_FLEET_VEHICLES
TABLEEXCLUDE RELIS.VEH_GARAGE_ADDRESSES
TABLEEXCLUDE RELIS.VEH_IMMOBILISERS_INSTALLED
TABLEEXCLUDE RELIS.VEH_OWNER_CONCESSIONS
TABLEEXCLUDE RELIS.VEH_OWNER_CONDITIONS
TABLEEXCLUDE RELIS.VEH_PLATE_RETURNS
TABLEEXCLUDE RELIS.VEH_PLATE_TITLES
TABLEEXCLUDE RELIS.VEH_REMINDER_SUB
TABLEEXCLUDE RELIS.VEH_SANCTION_VEHICLES
TABLE RELIS.*;

DYNAMICRESOLUTION

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

 1

0 Comments

Leave Reply

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