Oracle GoldenGate的DDL复制本质是基于数据库全局Trigger的复制,sql的执行
在Oracle源库建立全局的Trigger捕捉DDL操作到中间ggs_marker表,Extract进程读取中间表DDL语句并与DML语句根据scn排序,Pump投递到目标端,
目标端Replicat再重现该DDL语句

DDL与DML复制完全不同,DDL基于Trigger,DML基于日志,其数据捕捉没有联系,只有在主Extract进程中根据scn号顺序组装,保证DDL,DML按照原来的顺序执行

DDL复制与DML复制是独立的,DDL复制的Trigger建立和启用后,无论DML复制是否运行,该Trigger一直发生作用,捕捉DDL语句到中间表.
因此DML的复制启停不影响DDL的捕捉,换句话说DDL Trigger的启停也不影响DML的复制,只是该Trigger被禁止后不再抓取DDL操作

 

DDL限制
DDL不支持Oracle recyclebin
DDL不支持超过2m的DDL语句
DDL只支持单向的DDL,不支持双向的DDL复制
DDL只支持源和目标segment段结构一致的DDL复制

==================================================
 
OGG版本号 平台 Oracle版本号 复制类型 Master(Source)   Slave(Target)   
11.1.1.0.0 AIX(6) 10.2.0.4 DDL+单向复制 peppdb1 IP Address test   
  Linux(AS5) 10.2.0.5   192.168.131.129   192.168.131.126 

准备安装

1安装
1.1下载软件for oracle版本的
ggsX86-64(AIX,Linux)
为例
=====================
1.2在源,目标库上创建用户ggate
useradd -g oinstall -G dba -u 2000 ggate
==================
1.3 创建ggate软件的目录
源,目标都创建
#mkdir -p /ggs
#chown ggate.dba -R /ggs

=========================
1.4.源备都更改.bash_profile设置环境变量
(oggv11)  11g必须创建连接文件
$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so

vi .bash_profile
export GGATE=/ggs
export ORACLE_BASE=/opt/
export ORACLE_HOME=$ORACLE_BASE/ora_home
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GGATE
export PATH=$GGATE:$ORACLE_HOME/bin:$PATH
=====================
1.5.源备库ggsci界面创建必要的目录
帮助为help +command的方式,如
GGSCI (master) 1> help create
———–
1.5.1源库创建
[ggate@master ~]$ /ggs/ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 ggateCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 10g on Apr 21 2011 22:19:02
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (master) 2> create subdirs
Creating subdirectories under current directory /ggs
Parameter files                /ggs/dirprm: created
Report files                   /ggs/dirrpt: created
Checkpoint files               /ggs/dirchk: created
Process status files           /ggs/dirpcs: created
SQL script files               /ggs/dirsql: created
Database definitions files     /ggs/dirdef: created
Extract data files             /ggs/dirdat: created
Temporary files                /ggs/dirtmp: created
Veridata files                 /ggs/dirver: created
Veridata Lock files            /ggs/dirver/lock: created
Veridata Out-Of-Sync files     /ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /ggs/dirver/oosxml: created
Veridata Parameter files       /ggs/dirver/params: created
Veridata Report files          /ggs/dirver/report: created
Veridata Status files          /ggs/dirver/status: created
Veridata Trace files           /ggs/dirver/trace: created
Stdout files                   /ggs/dirout: created

————
1.5.2目标库创建
GGSCI (standby) 1> create subdirs
Creating subdirectories under current directory /ggs
Parameter files                /ggs/dirprm: created
Report files                   /ggs/dirrpt: created
Checkpoint files               /ggs/dirchk: created
Process status files           /ggs/dirpcs: created
SQL script files               /ggs/dirsql: created
Database definitions files     /ggs/dirdef: created
Extract data files             /ggs/dirdat: created
Temporary files                /ggs/dirtmp: created
Veridata files                 /ggs/dirver: created
Veridata Lock files            /ggs/dirver/lock: created
Veridata Out-Of-Sync files     /ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /ggs/dirver/oosxml: created
Veridata Parameter files       /ggs/dirver/params: created
Veridata Report files          /ggs/dirver/report: created
Veridata Status files          /ggs/dirver/status: created
Veridata Trace files           /ggs/dirver/trace: created
Stdout files                   /ggs/dirout: created

至此GoldenGate安装完毕

 

======================
2源,目标库都必须配置

———–
2.1设置为归档模式

———-
2.2设置开启minimal supplemental lggateing 以及foce logging

SQL> alter database add supplemental log data;
SQL>alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL>alter database force logging;
SUPPLEME
——–
YES

——–
2.3关闭数据库的recyclebin
SQL> alter system set recyclebin=off scope=both;
System altered.

————-
2.4配置复制的DDL支持
——–
2.4.1 sys/system 账户创建用户并授权
test:
SQL> create user ggate identified by ggate default tablespace ts_ggate temporary tablespace temp01;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
peppdb1:
SQL> create user ggate identified by ggate default tablespace ts_ggate temporary tablespace temp01;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;

——————–不需要授予dba权限

 

———–
2.4.2 sys 账户执行相关的sql,源,目标库都执行
sqlplus sys/sys@master as sysdba

SQL> @marker_setup.sql;
SQL> @ddl_setup.sql;                               —–sysdba执行
SQL> @role_setup.sql;
GRANT ALTER SESSION TO ggate;
–GRANT RESOURCE TO ggate;
–GRANT CONNECT TO ggate;
grant dba to ggate;
GRANT SELECT ANY DICTIONARY TO ggate;
GRANT FLASHBACK ANY TABLE TO ggate;
GRANT SELECT ANY TABLE TO ggate;
GRANT EXECUTE ON dbms_flashback TO ggate;
GRANT GGS_GGSUSER_ROLE TO ggate;
GRANT ALTER ANY TABLE TO ggate;
GRANT DELETE ANY TABLE TO ggate;
GRANT EXECUTE ON UTL_FILE TO ggate;

–@ddl_enable.sql
–@ddl_pin.sql

SQL> grant ggs_ggsuser_role to ggate;
SQL> @ddl_enable.sql;
———
目标库创建用户
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ggate;

GRANT ALTER SESSION TO ggate;
GRANT RESOURCE TO ggate;
GRANT CONNECT TO ggate;
grant dba to ggate;
GRANT SELECT ANY DICTIONARY TO ggate;
GRANT FLASHBACK ANY TABLE TO ggate;
GRANT SELECT ANY TABLE TO ggate;
GRANT EXECUTE ON dbms_flashback TO ggate;
GRANT GGS_GGSUSER_ROLE TO ggate;
GRANT ALTER ANY TABLE TO ggate;
GRANT DELETE ANY TABLE TO ggate;
GRANT EXECUTE ON UTL_FILE TO ggate;

 

 

—————–
2.5创建源,目标端的测试账户

——–
2.5.1创建源账户
SQL> create user sender identified by sender default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;

———-
2.5.1创建目标数据库的账户
SQL> create user receiver identified by receiver default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to receiver;

 

————–
2.6 开启源库事务数据的的跟进(注意抽取schema的所有表都要包含唯一约束)add trandata(很关键,否则update同步不了,insert ,delete可以)

GGSCI (master) 3> dblogin userid ggate@peppdb1,password ggate
Successfully logged into database.
GGSCI (master) 4> info trandata sender.*
Logging of supplemental redo log data is disabled for table SENDER.T1.
GGSCI (master) 5> add trandata sender.*
Logging of supplemental redo log data is already enabled for table SENDER.T1.

 

***********************************************************************************
不一致的告警信息
GGSCI (master) 7> dblogin userid ggate@master,password ggate
Successfully logged into database.
GGSCI (master) 8> info trandata sender.*
Logging of supplemental redo log data is disabled for table SENDER.T1.
GGSCI (master) 9> add trandata sender.*
2011-12-26 14:32:55  WARNING OGG-00869  No unique key is defined for table T1. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SENDER.T1.

**************************************************************************

 

=========================================
配置
1.源库配置
——
1.1源头mgr配置
PORT 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 14

lagreporthours 1
laginfominutes 3
lagcriticalminutes 45

—–
1.2源extract 抽取进程extddl

1添加2个threads的extract
add extract ext1,tranlog,begin now,threads 2
(——-threads 2表示,extract是由RAC的两个节点中抽取数据)
GGSCI (ractest1) 16> add exttrail /ggs/dirdat/e1,extract ext1,megabytes 200
 add exttrail /ggs/dirdat/e1,extract ext1,megabytes 200

——————-
2.配置extract
extract ext1
–oracle enviroment
SETENV (NLS_LANG = AMERICAN_AMERICA.UTF-8 )
–SETENV (ORACLE_SID = “peppdb1”)
–Username:Password
userid ggate@peppdb1,password ggate
–report
report at 00:59
–report quenue switch time
reportrollover  at 01:00
–Report rate
reportcount every 1 minutes,rate
–Number of files
numfiles 20000
–Warn log transaction :last 2hours ,checkpoint interval 2 min
warnlongtrans 2h,checkinterval 3m
–Memory Cache For transaction
–transmemory directory (/ggs/durtmp,8G,4G),ram 2G,transram 500M
–extract进程单个事务最大500M,extract最大2G,超过在/oracle/bak/xxx下分配2G,最大8G作为缓存
–Tanstaction log options
tranlogoptions rawdeviceoffset 0
tranlogoptions convertucs2clobs
tranlogoptions altarchivelogdest primary instance peppdb1 /oracle/archive/arch1, altarchivelogdest instance peppdb2  /oracle/archive/arch2
–Thread Options
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY  30000 IOLATENCY 30000
–Db Options
dboptions allowunusedcolumn
–Fetch Data Mode:no use undotablespace,extract data direct from tables
fetchoptions nousesnapshot
–Extrail Trailfile:Local Enqueue File
exttrail /ggs/dirdat/e1
–DiscardFile
discardfile /ggs/dirrpt/ext1.dsc,append,megabytes 50
–Dynamicresolution:Dynamic resolution table_name when extract,speed up extract
dynamicresolution
–Extract get truncates
gettruncates
getupdatebefores
ignorereplicates
BR BROFF
–11g BR put log transaction in Cache
–DDL support
ddl include all
ddloptions addtrandata,report
table sender.*;
–tableexclude ctais2.KJ_*;

 

———-
1.3源创建pump进程pumpext1(Pump不需要2个thread,因为extract已经抽取出来了)
add extract pumpext1,exttrailsource  /ggs/dirdat/e1
–add rmttrail /ggs/dirdat/r1,extract pumpext1,megabytes  200
add rmttrail /arch/ggstrail/rmttrail/pepp/r2,extract pumpext1,megabytes 200

GGSCI (master) 55> edit params pumpext1
extract pumpext1
dynamicresolution
passthru
TRANLOGOPTIONS  RAWDEVICEOFFSET  0
rmthost 192.168.131.126,mgrport 7809,compress
rmttrail /ggs/dirdat/e1
tranlogoptions rawdeviceoffset 0
–Pump禁止与用户交互
passthru
table sender.*;
=========================================
2.目标库配置

————
2.1.1目标库mgr参数
GGSCI (fsxybak.com) 7> edit params mgr
PORT 7809
dynamicportlist 7800-8000
 
autorestart replicat  *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5

PURGEOLDEXTRACTS /ggs/dirdat/rep1*,usecheckpoints,minkeepdays 7

————————
2.1.2添加checkpoint(推荐)
GGSCI (fsxybak.com) 1> edit params ./GLOBALS                                                                                       ———注意是GLOBALS

CHECKPOINTTABLE ggate.checkpoint
GGSCI (fsxybak.com) 1> dblogin userid ggate@test,password ggate
Successfully lggateed into database.
GGSCI (fsxybak.com) 2> add checkpointtable ggate.checkpoint;
Successfully created checkpoint table GGATE.CHECKPOINT;.

——————–
2.1.3在目标端创建同步队列
——–
2.1.3目标库添加replicat进程repddl
GGSCI (fsxybak.com) 3> add replicat rep1,exttrail /ggs/dirdat/r1,checkpointtable ggate.checkpoint

GGSCI (fsxybak.com) 10> edit params rep1
replicat rep1
userid ggate@test,password ggate
assumetargetdefs
SETENV (NLS_LANG = AMERICAN_AMERICA.UTF-8 )
reperror default,discard
discardfile /ggs/rep1.dsc,append,megabytes 50
dynamicresolution
–TRANLOGOPTIONS  RAWDEVICEOFFSET  0
–Map gg_test.dml,target gg_test.dml
–TABLEEXCLUDE SCHEMA.TMP*
grouptransops 100
maxtransops 1000
batchsql
map sender.*, target receiver.*;

**********************************************************************************************************
2011-12-20 03:41:49  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [1435], ORA-01435: user does n
ot exist, SQL ALTER SESSION SET CURRENT_SCHEMA=SENDER ], no error handler present.

需要添加
ddl include all
ddlerror default ignore retryop
最后为
replicat repddl
userid ggate@gg,password ggate
assumetargetdefs
reperror default,discard
discardfile /ggs/rep1.dsc,append,megabytes 50
dynamicresolution
ddl include all
ddlerror default ignore retryop
–Map gg_test.dml,target gg_test.dml
map gg_test.*, target gg_test.*;
*********************************************************************************************************

 

=========================================
3.启动所有的进程,并测试
3.1源端
3.2目标端
(
在dml测试不成功的时候,注意看读取的日志
因为之前读取的是e1.xxx日志
而现在读取的是d1.xxx日志,因此添加replicat的时候应该为
add replicat repddl,exttrail /ggs/dirdat/d1,nodbcheckpoint            ———–是d1
)

测试成功

 

 
========================================================================
========================================================================
开启DDL支持
========================
4.源,目标关闭所有的进程

—–
1.1源关闭
GGSCI (master) 144> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     STOPPED                                          
EXTRACT     STOPPED     EXT1        00:00:00      25:58:02   
EXTRACT     STOPPED     EXTDDL      00:00:00      00:00:32   
EXTRACT     STOPPED     PUMPDDL     00:00:00      00:00:27   
EXTRACT     STOPPED     TSPT        00:00:00      25:53:22 

———
1.2目标关闭

GGSCI (fsxybak.com) 105> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     STOPPED                                          
REPLICAT    STOPPED     REP1        00:00:00      202:29:22  
REPLICAT    STOPPED     REPDDL      00:00:00      00:00:32   

——–
1.3源,目标库关闭recyclebin
alter system set recyclebin=off socpe=both

 
———
1.4指定源数据库的模式,需要gg用户来实现,同时要dba权限

edit params ./GLOBALS

GGSCHEMA ggate

 
=======================================
5.安装支持DDL对象的脚本在$GoldenGate_Home目录下

———
2.1安装脚本
@marker_setup.sql;
@ddl_setup.sql;
@role_setup.sql;
@ddl_enable.sql;

————
2.2验证脚本
@ddl_status.sql;

 

=======================================
6.配置DDL支持

———-
3.1源停止所有的extract,目标停止所有的replicat进程
GGSCI (master) 3> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     STOPPED     EXT1        00:00:00      00:18:10   
EXTRACT     STOPPED     TSPT        00:00:00      00:18:01   

GGSCI (fsxybak.com) 3> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP1        00:00:00      00:16:19  

 

———–
3.2源端修改extract配置

在源的extract进程table后添加如下,(不能再pump进程参数pumpddl添加,否则该进程启动不了)
ddl include all
ddloptions addtrandata, report

############################################################################
Ps:如果在pump进程中添加会出现错误
2011-12-28 14:29:47  ERROR   OGG-00528  The DDL parameter is not supported for this data source. Please remove this parameter and all DDL operat
ions will be propagated in pass-through (PASSTHRU) mode
###########################################################################

Eg:
………………………….
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT
table gg_test.*;

 

————–
3.3在目标端的map关键字前添加
ddl include mapped
ddloptions report
–ddlerror default ignore retryop                      —–忽略DDL错误

Eg:
…………………
DDL INCLUDE ALL
DDLOPTIONS REPORT
–ddlerror default ignore retryop

map gg_test.*,  target gg_test.*;

 

 
=======================================
4.重启进程,验证结果

——–
4.1重启进程
2011-12-26 17:40:56  ERROR   OGG-00528  The DDL parameter is not supported for this data source.
Please remove this parameter and all DDL operations will be propagated in pass-thr
ough (PASSTHRU) mode.
**********************************
解决方法,注释passthru的配置

**********************************
GGSCI (master) 18> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06   
EXTRACT     RUNNING     TSPT        00:00:00      00:26:21 
GGSCI (fsxybak.com) 19> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:18:09      00:00:02 

 

 

***********************************************************
当pump进程为
2011-12-28 14:29:47  ERROR   OGG-00528  The DDL parameter is not supported for this data source. Please remove this parameter and all DDL operat
ions will be propagated in pass-through (PASSTHRU) mode.

则需要注释掉pump进程pumpddl中的所有ddl参数,重启(pump不能解析)

***********************************************************

 

 

 

 

 

 

 

 

 
————
4.2测试DDL数据

源库
SQL> create table t_ddl(id number);
Table created.
目标库
SQL> select * from t_ddl;
no rows selected

 

 
=============================
DDL 错误
 
DDLERROR DEFAULT IGNORE

在通配符下,DDL错误尝试3次忽略操作

DDLERROR DEFAULT ABENDS