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.
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.*;
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
