In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, December 23, 2018

DDL Replication and DDL mapping Restriction DEMO in oracle goldengate


The components that support the replication of DDL and the replication of transactional data changes (DML) are independent of each other. Therefore, you can synchronize:
  • just DDL changes
  • just DML changes
  • both DDL and DML
By default the DDL replication is disabled on the source database (extract side) but is enabled on the target Database (replicat side).
If you need to capture DDL from replicated process, then you need to configure the extract with GETREPLICATES.
Only supports Oracle Database to Oracle Database replications.
The source and target object definitions must be identical.
In Oracle database 12c, DDL replication does not require any setup of triggers as it is natively supported at the database level, you no longer need to run the SQL scripts such as marker_setup.sql and ddl_setup.sql to set up the DDL replication.


Configure Extract Process with DDL Replication

For learning purpose, I have configured both EXTRACT and REPLICAT in same server same database ggtest1
Adding “DDL INCLUDE MAPPED” enables extracting the DDL 
Don’t forget to add DDL INCLUDE MAPPED in the Pump and Replicat processes.


Sample :- Extract parameter file and REPLICAT parameter file

GGSCI (rac1.localdomain) 10> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED
TABLE us01.*;

GGSCI (rac1.localdomain) 11> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 21> start ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (rac1.localdomain) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EX1         00:00:00      00:00:01    
REPLICAT    STOPPED     REP2        00:00:00      00:04:04    


GGSCI (rac1.localdomain) 23> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting


GGSCI (rac1.localdomain) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EX1         00:00:00      00:00:00    
REPLICAT    RUNNING     REP2        00:00:00      00:00:00    

Testing by adding table and  add, modify, drop column in source schema database.

ON SOURCE SCHEMA US01

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB2 TABLE
TB1 TABLE

SQL> show user
USER is "US01"
SQL> create table tb3 as select * from tb1;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE
TB3 TABLE


on Target Schema DEMO, We can see the data replication 

SQL> col tname for a15
SQL> col tabtype for a15
SQL> select * from tab;

TNAME TABTYPE   CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE

SQL> show user
USER is "DEMO"
SQL> select * from tab;

TNAME TABTYPE   CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE
TB3 TABLE  --> Table created in DEMO schema

ON SOURCE SCHEMA US01

SQL> select count(*) from tb3;

  COUNT(*)
----------
6

SQL> set time on
20:42:57 SQL>               
20:43:08 SQL> truncate table tb3;

Table truncated.

20:43:14 SQL> select count(*) from tb3;

  COUNT(*)
----------
0

on Target Schema DEMO, We can see the data replication 

SQL> set time on
20:42:54 SQL> 
20:43:25 SQL> select count(*) from tb3;

  COUNT(*)
----------
 0  (After truncate statement executed on US01 schema replicated in demo)

ON SOURCE SCHEMA US01

20:43:22 SQL> insert into tb3 select * from tb1;

6 rows created.

20:43:48 SQL> commit;

Commit complete.

20:43:54 SQL> select count(*) from tb3;

  COUNT(*)
----------
6

on Target Schema DEMO, We can see the replication 

20:44:08 SQL> select count(*) from tb3;

  COUNT(*)
----------
 6 --> Data replicated in demo schema after insert statement in US01

ON SOURCE SCHEMA US01

20:44:20 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)

20:48:04 SQL> alter table tb3 add (Address varchar2(20));

Table altered.

20:48:54 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)
 ADDRESS     VARCHAR2(20)

on Target Schema DEMO, We can see the replication 

20:44:23 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)

20:49:07 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)
 ADDRESS     VARCHAR2(20) -->ADDRESS COLUMN added in replicat side

ON SOURCE SCHEMA US01

20:49:12 SQL> alter table tb3 drop column address;

Table altered.

20:49:41 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)

on Target Schema DEMO, We can see the replication 

20:49:19 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)
 COLUMN address dropped in demo schema TB3 Table

ON SOURCE SCHEMA US01

20:49:46 SQL> alter table tb3 modify (dept varchar2(40));

Table altered.

20:50:18 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(40)

on Target Schema DEMO, We can see the replication 

20:49:53 SQL> desc tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(40)

Column DEPT modified from varchar2(20) to Varchar(40) 

ON SOURCE SCHEMA US01

20:50:23 SQL> drop table tb3 purge;

Table dropped.

20:50:49 SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE

on Target Schema DEMO, We can see the replication 

20:50:28 SQL> select * from tab;

TNAME TABTYPE   CLUSTERID
--------------- --------------- ----------
TB2 TABLE
TB1 TABLE

Table TB3 dropped in DEMO Schema. 

Below is the DEMO of DDL replication restricting example if you do not want to drop statement replication we can restrict and allow set of DDL replication as shown below. 

Below is demo to DDL replication of only CREATE TABLE statement

GGSCI (rac1.localdomain) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EX1         00:00:00      00:00:09    
REPLICAT    RUNNING     REP2        00:00:00      00:00:01    


GGSCI (rac1.localdomain) 12> stop rep2

Sending STOP request to REPLICAT REP2 ...
Request processed.


GGSCI (rac1.localdomain) 13> stop ex1

Sending STOP request to EXTRACT EX1 ...
Request processed.


GGSCI (rac1.localdomain) 14> info all 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EX1         00:00:00      00:00:05    
REPLICAT    STOPPED     REP2        00:00:00      00:00:10    


GGSCI (rac1.localdomain) 16> edit params ex1



GGSCI (rac1.localdomain) 17> edit params rep2



GGSCI (rac1.localdomain) 18> edit params ex1



GGSCI (rac1.localdomain) 19> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table'
TABLE us01.*;


GGSCI (rac1.localdomain) 20> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
--DDL INCLUDE ALL
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table'
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;


GGSCI (rac1.localdomain) 21> start ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting

GGSCI (rac1.localdomain) 23> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting


GGSCI (rac1.localdomain) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EX1         00:00:00      00:00:00    
REPLICAT    RUNNING     REP2        00:00:00      00:00:00    

ON SOURCE SCHEMA US01

20:53:35 SQL> create table tb3 as select * from tb1;

Table created.

on Target Schema DEMO
21:19:28 SQL> /

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB3 TABLE
TB2 TABLE
TB1 TABLE

ON SOURCE SCHEMA US01

21:19:22 SQL> desc us01.TB3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)

21:19:42 SQL> alter table tb3 add (address varchar2(20));

Table altered.

21:20:12 SQL> desc US01.tb3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)
 ADDRESS     VARCHAR2(20)
on Target Schema DEMO
21:20:20 SQL> DESC DEMO.TB3
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID     NUMBER
 NAME     VARCHAR2(20)
 DEPT     VARCHAR2(20)


added ADDRESS COLUMN not replicated due to mapping restriction
ALTER statement not replicated 


ON SOURCE SCHEMA US01

21:21:27 SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE
TB3 TABLE

21:21:40 SQL> drop table tb3 purge;

Table dropped.

21:21:54 SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE
on Target Schema DEMO
21:22:05 SQL> select * from tab;

TNAME TABTYPE CLUSTERID
--------------- --------------- ----------
TB1 TABLE
TB2 TABLE
TB3 TABLE --> Table still exists, DROP Statement not replicated due to mapping restriction. 

Sample mapping Extract and Replicat Parameter files to allow alter table, create index, alter index, create view, and alter view.


GGSCI (rac1.localdomain) 45> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' 
TABLE us01.*;


GGSCI (rac1.localdomain) 46> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
--DDL INCLUDE ALL
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table'
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;


GGSCI (rac1.localdomain) 52> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' 
INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'index'
TABLE us01.*;


GGSCI (rac1.localdomain) 53> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
--DDL INCLUDE ALL
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'index'
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 61> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE create OBJTYPE 'view' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'view'
TABLE us01.*;


GGSCI (rac1.localdomain) 63> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
--DDL INCLUDE ALL
DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &
INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'index' &
INCLUDE MAPPED OPTYPE create OBJTYPE 'view' &
INCLUDE MAPPED OPTYPE alter OBJTYPE 'view'
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;



hope, this helps.

References:-
https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/ddl.htm#GIORA307
http://www.juliandyke.com/Research/GoldenGate/GoldenGateDDLReplication.php
https://www.perftuning.com/blog/ddl-mapping-oracle-goldengate-12-2/
http://www.vitalsofttech.com/configure-goldengate-ddl-replication/
https://jinyuwang.weebly.com/for-oracle-db/goldengate-121-new-feature-how-to-configure-ddl-replications-in-oracle-goldengate




 
Share/Bookmark