Enabling DDL Replication on Oracle GoldenGate

Configuring on Source Side (Unidirectional)

1. Stop all OGG processes.

GGSCI> stop capt00
GGSCI> stop pump00
GGSCI> stop rept00

2. Specify the ggschema for DDL replication.

GGSCI> edit params ./GLOBALS
GGSCHEMA goldengate

3. Grant ddl privileges to OGG user on both side.

SQL> grant execute on utl_file to goldengate;
SQL> grant restricted session to goldengate;
SQL> grant create table, create sequence to goldengate;

4. Run the scripts to enable DDL replication.

~]$ cd /ggs

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;
SQL> @ddl_enable.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
SQL> @ddl_pin.sql goldengate

5. Modify the capture and replicat processes.

GGSCI> edit params capt00
ddl include all
ddloptions addtrandata, report

GGSCI> edit params rept00
ddl include all
ddlerror default ignore retryop

6. Start all OGG processes.

GGSCI> start capt00
GGSCI> start pump00
GGSCI> start rept00

7. Verify the DDL replication valid.

SQL> conn jove/jove
SQL> alter table jove.t1 add (city varchar(20) default 'beijing' not null);
SQL> insert into jove.t1 (id, name) values (1, 'b')
SQL> select * from jove.t1;
SQL> drop table jove.t1;

Disable/Enable

SQL> @ddl_disable.sql

SQL> @ddl_enable.sql

Reconfiguration

GGSCI> stop reptxx/pumpxx/captxx

SQL> @ddl_disable.sql
SQL> @ddl_remove.sql
SQL> @marker_remove.sql
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;
SQL> @ddl_enable.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
SQL> @ddl_pin.sql goldengate

GGSCI> start reptxx/pumpxx/captxx