我们做如下测试

在源端执行表的truncate

SQL> truncate table tcustmer;
Table truncated.

在目的端，查看表数据

SQL> select count(*) from tcustmer;
?
? COUNT(*)
----------
? ? 2

数据并没有同步

我们如何让Goldengate实现DDL操作的同步呢？

要想支持DDL同步，需要在源数据库上做些设置，使得可以跟踪DDL操作。包括如下内容Trigger、marker和history table，一个用户角色和其他各式各样的数据库对象。

首先要配置GLOBALS参数，告诉gg使用那个schema来存储DDL操作。

GGSCI (localhost.localdomain) 19> edit params ./GLOBALS
?
-- GoldenGate GLOBALS parameter file
--
GGSCHEMA GGDDL

2. 使用sqlplus 创建ggddl用户，并安装ddl支持

SQL> conn / as sysdba
Connected.
SQL> create user ggddl identified by ggddl ;
?
User created.
?
SQL> grant connect,resource to ggddl;
?
Grant succeeded.
?
关闭数据回收站功能

运行marker_setup.sql (ogg的安装目录，进入sqlplus)

[oracle@localhost ogg]$ sqlplus / as sysdba
?
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 19:58:40 2015
?
Copyright (c) 1982, 2009, Oracle.? All rights reserved.
?
?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
?
SQL> @marker_setup.sql
?
Marker setup script
?
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
?
Enter Oracle GoldenGate schema name:GGDDL
?
?
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGDDL
?
MARKER TABLE
-------------------------------
OK
?
MARKER SEQUENCE
-------------------------------
OK
?
Script complete.

在弹出对话框输入GGSCHEMA的名称

运行ddl_setup.sql

SQL> @ddl_setup.sql

运行role_setup.sql

创建好角色后，并不是要把这个角色赋予ggddl用户，而是要将该角色赋予Extract，Replicat等使用的schema 。

我们之前配置的是system用户

SQL> GRANT GGS_GGSUSER_ROLE TO system;
?
Grant succeeded.

启用ddl，其实就是使trigger enable

SQL> @ddl_enable.sql
?
Trigger altered.

关闭Manager，Extract，Replicat

源端

GGSCI (localhost.localdomain) 1> stop Extract eorakk
GGSCI (localhost.localdomain) 5> stop mgr ！

目的端

GGSCI (localhost.localdomain) 1> stop replicat rorakk
Sending STOP request to REPLICAT RORAKK ...
Request processed.
GGSCI (localhost.localdomain) 2> stop manager

编辑Extract参数

GGSCI (localhost.localdomain) 5> edit params eorakk
?
--
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD Changes
--
EXTRACT EORAKK
USERID system, PASSWORD oracle
RMTHOST 192.168.199.104, MGRPORT 7809
EXTTRAIL ./dirdat/KK
DDL INCLUDE ALL
TABLE SCOTT.TCUSTMER;
TABLE SCOTT.TCUSTORD;

在原有基础上添加一行DDL INCLUDE ALL

编辑Replicat参数

GGSCI (localhost.localdomain) 4>? edit params rorakk
?
--
-- Change Delivery parameter file to apply
-- TCUSTMER and TCUSTORD Changes
--
REPLICAT RORAKK
USERID system, PASSWORD oracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORAKK.DSC, PURGE
DDL INCLUDE MAPPED
MAP scott.tcustmer, TARGET scott.tcustmer;
MAP scott.tcustord, TARGET scott.tcustord;

在原有基础上添加一行DDL INCLUDE MAPPED

?

启动Manager、Extract、Replicat

源端

GGSCI (localhost.localdomain) 6> start manager
?
Manager started.
?
GGSCI (localhost.localdomain) 7> start extract eorakk
?
Sending START request to MANAGER ...
EXTRACT EORAKK starting?

目的端

GGSCI (localhost.localdomain) 5> start mgr
?
Manager started.
?
GGSCI (localhost.localdomain) 16> start replicat rorakk
?
Sending START request to MANAGER ...
REPLICAT RORAKK starting

验证数据

源端