Goldengate 实现Oracle for Oracle 单向DDL操作同步

在http://lqding.blog.51cto.com/9123978/1695162 文章中我们实现了表的DML操作同步。

我们做如下测试

在源端执行表的truncate

SQL> truncate table tcustmer;Table truncated.

在目的端，查看表数据

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

数据并没有同步

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

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

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

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

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

SQL> conn / as sysdbaConnected.SQL> create user ggddl identified by ggddl ;User created.SQL> grant connect,resource to ggddl;Grant succeeded.

关闭数据回收站功能

SQL> ALTER SYSTEM SET RECYCLEBIN = OFF scope=spfile;System altered.SQL> startup forceORACLE instance started.Total System Global Area  835104768 bytesFixed Size             2217952 bytesVariable Size                574621728 bytesDatabase Buffers     255852544 bytesRedo Buffers                   2412544 bytesDatabase mounted.Database opened.

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

[oracle@localhost ogg]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 19:58:40 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @marker_setup.sqlMarker setup scriptYou 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:GGDDLMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GGDDLMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.

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

运行ddl_setup.sql

SQL> @ddl_setup.sql

运行role_setup.sql

SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)You will be prompted for the name of a schema for the GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:ggddlWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:GRANT GGS_GGSUSER_ROLE TO where  is the user assigned to the GoldenGate processes.

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

我们之前配置的是system用户

SQL> GRANT GGS_GGSUSER_ROLE TO system;Grant succeeded.

启用ddl，其实就是使trigger enable

SQL> @ddl_enable.sqlTrigger altered.

关闭Manager，Extract，Replicat

源端

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

目的端

GGSCI (localhost.localdomain) 1> stop replicat rorakkSending 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 EORAKKUSERID system, PASSWORD oracleRMTHOST 192.168.199.104, MGRPORT 7809EXTTRAIL ./dirdat/KKDDL INCLUDE ALLTABLE 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 RORAKKUSERID system, PASSWORD oracleHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORAKK.DSC, PURGEDDL INCLUDE MAPPEDMAP scott.tcustmer, TARGET scott.tcustmer;MAP scott.tcustord, TARGET scott.tcustord;

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

启动Manager、Extract、Replicat

源端

GGSCI (localhost.localdomain) 6> start managerManager started.GGSCI (localhost.localdomain) 7> start extract eorakkSending START request to MANAGER ...EXTRACT EORAKK starting

目的端

GGSCI (localhost.localdomain) 5> start mgrManager started.GGSCI (localhost.localdomain) 16> start replicat rorakkSending START request to MANAGER ...REPLICAT RORAKK starting

验证数据

源端

SQL> truncate table tcustmer;Table truncated.SQL> desc tcustmer  Name                                         Null?    Type ----------------------------------------- -------- ---------------------------- CUST_CODE                            NOT NULL VARCHAR2(4) NAME                                                  VARCHAR2(30) CITY                                                  VARCHAR2(20) STATE                                                 CHAR(2)SQL> alter table tcustmer add state_desc varchar2(30);Table altered.

目的端

SQL> select * from tcustmer;no rows selectedSQL> desc tcustmer Name                                         Null?    Type ----------------------------------------- -------- ---------------------------- CUST_CODE                            NOT NULL VARCHAR2(4) NAME                                                  VARCHAR2(30) CITY                                                  VARCHAR2(20) STATE                                                 CHAR(2) STATE_DESC                                            VARCHAR2(30)

数据已同步。