2014-03-08 Created By BaoXinjian
一、摘要
1. 不支持和有限支持的DDL类型
(1). Oracle数据库保留的Schema，GoldenGate是不支持的，如SYS、SYSTEM、PUBLIC等等
(2). GoldenGate10g DDL不支持Oracle回收站功能
(3). GoldenGate支持不超高2M长度的DDL语句
(4). GoldenGate只支持单向的DDL复制，不支持双向的DDL复制
(5). GoldenGate只支持源端和目标端结构一直的
2. DDL处理方法
(1). 不支持DDL类型的处理方法
a. 不支持的Schema，OGG默认不复制，不许更多设定
b. 关闭Oracle回收站：alter system set recyclebin=off scope=both
(2). 受限支持DDL类型的处理费方法
a. 通过脚本ddl_ddl2file.sql获取被忽略的Oracle DDL操作，将其获取的操作放在USER_DUMP_DEST目录一个文本文件中
b. 投递过程中必须使用ASSUMETARGETDEFS参数
3. DDL复制的配置
(1). Oracle DDL复制的原理
(2). 安装GoldenGate DDL对象
(3). 配置DDL支持
(4). DDL异常与错误处理
二、Oracle DDL复制的原理
1. DDL复制原理
基于数据库全局Trigger的复制，在源数据建立一个Oracle全库级别的Trigger捕捉DDL操作到中间表，Extract读取中间表DDL语句并与DML语句根据csn排序，Pump投递到目标端，目标端Replicat在重现该DDL语句
2. DDL复制和DML复制的区别
(1). DDL复制是基于Trigger
(2). DML复制是基于日志
(3). 只是在主Extract进程中通过scn号安装发生顺序进行组装，保证DDL操作和DML操作按照原来的顺序之下你给
3. DDL复制和DML复制的关系
DDL复制和DML复制可以项目独立，可以只启动DDL复制，也可以只启动DML复制，相互并不影响，他们之间只是在Extract进行组装时根据scn号进行排序
三、安装GoldenGate DDL对象
Step1. 停止Extract进程组
[ggate@gavinprod ~]$ cd $GGATE[ggate@gavinprod ggate]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (gavinprod.com) 1> stop E*EXTRACT EXT1 is already stopped.EXTRACT EXT2 is already stopped.
Step2. 指定源端数据库的模式
GGSCI (gavinprod.com) 2> edit params ./GLOBALSGGSCHEMA ggate
Step3. 停止Manager进程
GGSCI (gavinprod.com) 5> stop MGR !Manager is already stopped.
Step4. 关闭Oracle的recyclebin
[oracle@gavinprod bin]$ sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:50:10 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> show parameter recyclebinNAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string OFF SQL> alter system set recyclebin = off scope = both;
Step5. 安装DDL对象
[oracle@gavinprod ggate]$ sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:54:49 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> @/opt/oracle/ggate/marker_setup.sql SQL> @/opt/oracle/ggate/ddl_setup.sql SQL> @/opt/oracle/ggate/role_setup.sql SQL> @/opt/oracle/ggate/ddl_enable.sql
Step6. 验证DDL对象
[oracle@gavinprod ggate]$ sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:54:49 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> @/opt/oracle/ggate/marker_status.sql
四、配置DDL支持
Step1. 停止Extract 和 Replicat进程组
--Extract 端GGSCI (gavinprod.com) 1> stop E*EXTRACT EXT1 is already stopped.EXTRACT EXT2 is already stopped.GGSCI (gavinprod.com) 2> stop D*EXTRACT DMP2 is already stopped.--Replicat 端GGSCI (odellprod.com) 1> stop R*REPLICAT REP1 is already stopped.REPLICAT REP2 is already stopped.
Step2. 修改Extract配置文件
GGSCI (gavinprod.com) 6> view params EXT2extract ext2userid ggate@gavinprod, password oracleexttrail /opt/oracle/ggate/dirdat/ldDDL INCLUDE ALLDDLOPTIONS ADDTRANDATA, REPORTtable source_prod.*;
Step3. 修改Replicat配置文件
GGSCI (odellprod.com) 2> view params REP2replicat rep2ASSUMETARGETDEFSuserid ggate@odellprod,password oraclediscardfile /opt/oracle/ggate/dirdat/rep2_discard.txt, append, megabytes 10DDL INCLUDE MAPPEDDDLOPTIONS REPORTmap source_prod.*, target target_prod.*;
Step4. 重启进程
--Extract 端GGSCI (gavinprod.com) 1> start E*GGSCI (gavinprod.com) 2> start D*--Replicat 端GGSCI (odellprod.com) 1> start R*
Step5. 验证结果
1. 目标端建立table bxj_ogg_ddl
create table bxj_ogg_ddl (id number, name varchar2 (100) );
2. 目标端查看所创建的table是否存在
select * from dba_objects where object_name = 'BXJ_OGG_DDL';
3. 如果存在，测试成功
Thanks and Regards
