Golden Gate Configuration: replicate DDL operations
Configure DDL replication
In terms of DDL replication 2 factors are important to keep in mind: the Supplemental Logging factor and the Golden Gate DDL replication configuration.
1 Supplemental Logging
In order to replicate data properly supplemental logging needs to be activated for the tables. This can be done at the SCHEMA level or at the TABLE level. The best way to define it is at the SCHEMA level, as it automatically configures supplemental logging to new tables. When supplemental logging is activated at the TABLE level, new tables are not configured with supplemental logging automatically. Read more about supplemental logging in another of my posts
2 Golden Gate DDL replication configuration
DDL can be replicated by Golden Gate by specifying the DDL parameter in the extract parameter file with the following paramaters
DDL & INCLUDE MAPPED OBJTYPE TABLE & INCLUDE MAPPED OBJTYPE INDEX -- alternative configuration, to include all DDL of the specified (mapped) objects -- DDL INCLUDE MAPPED -- alternative configuration, to include all DDL, of any objects -- DDL INCLUDE -- table level settings when supplemental level logging is configured at the TABLE level TABLE HR.TABLE1; TABLE HR.TABLE2; -- table level settings when supplemental level logging is configured at the SCHEMA level -- TABLE HR.*
- The parameter settings above tell GoldenGate to replicate all the table and index DDL, but not other DDL like triggers, sequences, etc.
- Also, they tell GoldenGate to replicate only the MAPPED objects. Mapped objects are the objects as specified in the Extract file by the TABLE parameter.
- When you have configured supplemental logging at the SCHEMA level, you can use wildcards, so any table (including new tables) will be replicated. However, if you have configured supplemental level logging at the TABLE level, it is better not to use the wildcards. Because then also new tables will be replicated across, but they do not have supplemental logging configured yet. In that case you need a specific procedure to add new tables to the Golden Gate replication. (One of my other posts explains how to add a new table to an existing GoldenGate replication.
Monitor DDL replication
You can monitor new DDL in the extract report file. There are 3 options:
- The new DDL is not of the MAPPED scope, and will therefore not be logged in the Extract Report file
- the new DDL is of MAPPED scope, but is filtered out by the INCLUDE clause. It will be logged in the Extract Report file, but it will not be captured in the trail file.
- The new DDDL is of MAPPED scope and included in the filter. It will be logged in the Extract Report file, and it will be captured in the trail file
See some examples below
2017-08-05 17:18:42 INFO OGG-01487 DDL found, operation [CREATE TABLE HR.TBL_NEW (KOL1 VARCHAR2(10)) (size 49)], start SCN , commit SCN  instance [ (1)], DDL seqno , marker seqno . 2017-08-05 17:18:42 INFO OGG-00487 DDL operation included [INCLUDE OBJTYPE TABLE], optype [CREATE], objtype [TABLE], objowner [HR], objname [TBL_NEW]. 2017-08-05 17:18:42 INFO OGG-00497 Writing DDL operation to extract trail file. 2017-08-05 17:21:28 INFO OGG-01487 DDL found, operation [CREATE SEQUENCE ORABUP0.CUSTOMERS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE (size 82)], start SCN , commit SCN  instance [ (1)], DDL seqno , marker seqno . 2017-08-05 17:21:28 INFO OGG-00488 DDL operation excluded [not included by any filter], optype [CREATE], objtype [SEQUENCE], objowner [HR], objname [CUSTOMERS_SEQ]. 2017-08-05 17:24:05 INFO OGG-01487 DDL found, operation [CREATE OR REPLACE PROCEDURE HR.PROCPRINTHELLOWORLD IS BEGIN DBMS_OUTPUT.PUT_LINE('HELLO WORLD!'); END; (size 111)], start SCN , commit SCN  instance [ (1)], DDL seqno , marker seqno . 2017-08-05 17:24:05 INFO OGG-00488 DDL operation excluded [not included by any filter], optype [CREATE], objtype [PROCEDURE], objowner [HR], objname [PROCPRINTHELLOWORLD].
DDL Replication of triggers
Triggers can also be replicated, but the default setting is that triggers are not active on the target (DBOPTIONS SUPPRESSTRIGGERS). Read more about Golden Gate trigger handling in one of my other posts.