1. Oracle GoldenGate Classic Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. SQL Server
  5. Understanding What's Supported for SQL Server
  6. Non-Supported Objects and Operations for SQL Server
  7. Requirements for Table Level DDL Changes

Requirements for Table Level DDL Changes

Oracle GoldenGate for SQL Server does not support the capture or delivery of DDL changes. However, beginning with Oracle GoldenGate 21c, changes made to tables enabled with TRANDATA will not cause Extract to abend. Extract will continue to process change data for the table as it existed when TRANDATA was enabled.

Operations considered to be table-level DDL changes include, but are not limited to: ALTER TABLE, TRUNCATE TABLE, index rebuilds, and partition switches.

To avoid data inconsistencies due to table level DDL changes, the following steps are required.

  1. Source: Pause or Stop application data to the table or tables to be modified.
  2. Source: Ensure that there are no open transactions against the table to be modified.
  3. Source: Ensure that the SQL Server CDC Capture job processes all remaining transactions for the table that is to be modified.
  4. Source: Ensure that the Extract processes all the transactions for the table that is to be modified, prior to making any DDL changes.
  5. Target: Ensure that the Replicat processes all the transactions for the table that is to be modified, prior to making any DDL changes.
  6. Optionally, implementing an Event Marker table can be used to determine when all of the remaining transactions have been processed for the table that is to be modified, and handle the coordination of when to correctly stop the Extract and Replicat.
  7. Source: Stop the Extract process.
  8. Target: Stop the Replicat process.
  9. Source: Disable supplemental logging for the table to be modified by running DELETE TRANDATA.
  10. Source: Make table DDL changes to the source table.
  11. Target: Make table DDL changes to the target table.
  12. Source: Re-enable supplemental logging by running ADD TRANDATA to the table(s) after the modifications have been performed.
  13. Source: Start the Extract.
  14. Target: Start the Replicat.
  15. Source: Resume application data to the table or tables that were modified.