sqlserver ogg 手动ddl

一、版本信息
1.1 数据库版本
Sqlserver 2008 R2
1.2 OGG版本信息
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_131206.0309
Windows x64 (optimized), Microsoft SQL Server on Dec  6 2013 12:06:08
Operating system character set identified as GBK.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.


二、我已经做了哪些事情
源端到目标端的OGG已经配置好，源端到目标端的DML正常同步
2.1源端OGG的参数文件
2.1.1 
EXTRACT extest
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
SOURCEDB OGGTEST
EXTTRAIL D:\OGG\dirdat\lt
DISCARDFILE D:\OGG\dirrpt\extest.dsc, APPEND
table dbo.*;
2.1.2
EXTRACT pump_t
SOURCEDB OGGTEST
RMTHOST 10.10.10.154, MGRPORT 7809
RMTTRAIL D:\OGG\dirdat\rt
DISCARDFILE D:\OGG\\dirrpt\pump_t.dsc, APPEND
TABLE dbo.*;


2.2目标端OGG的参数文件
2.2.1
REPLICAT rep_t
ASSUMETARGETDEFS
HANDLECOLLISIONS 
TARGETDB OGGTEST2
DISCARDFILE D:\OGG\dirrpt\rep_t.dsc, APPEND
reperror default,discard
MAP dbo.*, TARGET dbo.*;




###################################################################
####以下开始测试
###################################################################
1、在源端创建测试表
create table dbo.test007(id integer primary key)
go


2、为这张表创建补充日志
GGSCI (WIN-B4VSMHA6GV9) 7> dblogin SOURCEDB OGGTEST
2015-09-30 15:45:17  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.
2015-09-30 15:45:17  INFO    OGG-03037  Session character set identified as GBK.
Successfully logged into database.
GGSCI (WIN-B4VSMHA6GV9) 8> add trandata dbo.test007
Logging of supplemental log data is enabled for table dbo.test007
GGSCI (WIN-B4VSMHA6GV9) 9>
3、在目标端创建测试表
create table dbo.test007(id integer primary key)
go


4、关闭源端ogg的抽取进程extest和pump进程pump_t
GGSCI (WIN-B4VSMHA6GV9) 10> stop extest
Sending STOP request to EXTRACT EXTEST ...
Request processed.
GGSCI (WIN-B4VSMHA6GV9) 11> stop pump_t
Sending STOP request to EXTRACT PUMP_T ...
Request processed.
GGSCI (WIN-B4VSMHA6GV9) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXTEST      00:00:00      00:00:05
EXTRACT     STOPPED     PUMP_T      00:00:00      00:00:01




5、将新的表添加到抽取进程extest和pump进程pump_t的参数文件中
TABLE dbo.*; 中已经包含dbo.test007


6、关闭目标端OGG的应用进程rep_t
GGSCI (WIN-B4VSMHA6GV9) 61> stop rep_t
Sending STOP request to REPLICAT REP_T ...
Request processed.
GGSCI (WIN-B4VSMHA6GV9) 62> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     REP_T       00:00:00      00:00:01




7、将新建的表添加到目标端OGG的应用进程rep_t的参数文件中
MAP dbo.*, TARGET dbo.*;中已经包含新建的表dbo.test007


8、启动源端ogg的抽取进程extest和pump进程pump_t
GGSCI (WIN-B4VSMHA6GV9) 13> start extest
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT EXTEST starting
GGSCI (WIN-B4VSMHA6GV9) 14> start pump_t
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT PUMP_T starting
GGSCI (WIN-B4VSMHA6GV9) 15>


9、启动目标端OGG的应用进程rep_t
GGSCI (WIN-B4VSMHA6GV9) 63> start rep_t
Sending START request to MANAGER ('GGSMGR') ...
REPLICAT REP_T starting




10、在源端向新创建的表中插入数据
BEGIN
DECLARE @i INT
set @i=1
WHILE @i < 100
BEGIN
INSERT INTO dbo.test007 values(@i)
set @i = @i + 1
END
END
GO 


11、在目标端查看数据是否同步
select COUNT(*) from dbo.test007
go

99

#####################################################

####总结

####################################################

将新建的表添加到OGG的同步中去，有三个地方很关键(以下仅代表个人观点)

1 为新建的表添加补充日志，不然OGG抽取不到这张表的数据

2 重启源端的抽取进程和目标端的复制进程(即使你进程参数文件中用的是通配符*， 也必须重启)

   进程缓存的表机构信息需要被重新加载

3 在目标端手动创建表