Golden Gate Configuration: replicate DDL operations

From Robs_Wiki
Revision as of 07:38, 30 September 2017 by Qadmin wiki (talk | contribs) (Monitor DDL replication)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 
[1862929], commit SCN [1862956] instance [ (1)], DDL seqno [0], marker seqno [0].
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 [1863257], commit SCN [1863261] instance [ (1)], DDL seqno [0], marker seqno [0].
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 [1863575], commit SCN [1863583] instance [ (1)], DDL seqno [0], marker seqno [0].
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.