Steps for enabling DDL Replication with two Oracle databases

Steps for performing/testing Online DDL Replication between two Oracle Databases

Here I am performing Golden gate unidirectional replication between 2 Oracle11g instances.

Assuming that the user ogg are created on both Source and Target databases.

Run the following scripts on source from the directory where the GoldenGate software was installed.

SQL> alter session set recyclebin=OFF;

SQL> @marker_setup

SQL> @ddl_setup

SQL> @role_setup

Enter GoldenGate schema name: OGG

SQL> grant ggs_ggsuser_role to ogg;

SQL> @ddl_enable

SQL> @ddl_pin OGG # Optional

After installing please follow the below steps :

1. Make sure to create a same table structure on source and Target Machines and manager process must be running.

2. Enable supplemental redo logging on the tables to be replicated.

3. Sample Table structure:

Create table sender.globe

(phonecode number(10),

country varchar2(10),

constraint glb_key unique(phonecode));

Create table receiver.globe

(phonecode number(10),

country varchar2(10),

constraint glb_key unique(phonecode));

On Source machine

Step 1 : GGSCI> add extract extp_ddl, tranlog, Begin now

Step 2 : GGSCI> edit params extp_ddl

Extract extp_ddl

Userid ogg@goldengate:1521/source,password XXXXXX

Rmthost goldengate, mgrport 7810

Rmttrail /u01/app/oracle/product/OGG/dirdat/rt

DDL INCLUDE MAPPED

Table sender.globe;

On Target machine

Step 1 :GGSCI> add replicat repl_ddl, exttrail /u01/app/oracle/product/GG2/rt,

Step 2: GGSCI> edit params ./GLOBALS

checkpointtable ogg.chkptab

Step 3: GGSCI> exit

$ ./ggsci

GGSCI> dblogin userid ogg password xxx

GGSCI> add checkpointtable

Step 4: GGSCI> edit params repl_ddl

Replicat repl_ddl

Userid ggate@goldengate:1521/target,password XXX

assumetargetdefs

MAP sender.globe, TARGET receiver.globe;

Start Extract and Replicat processes on source & target machines

1. GGSCI> start extract extp_ddl

2. GGSCI> start replicat repl_ddl

Once the Lag is 0, perform the following:

On Source Databases

SQL> ALTER TABLE SENDER.GLOBE ADD STATE VARCHAR2(10);

On Target Machine

SQL> DESC SENDER.GLOBE

phonecode number(10)

country varchar2(10)

state varchar2(10)

Look on “DDL INCLUDE MAPPED” on source extract file. This is the parameter that causes DDL replication between the databases.

Short explanation for DDL INCLUDE MAPPED: This parameter enables DDL Support for all tables which have been mapped.