Oracle技术专区 收藏本版 已有160 收藏本版 发新帖

dongxujian

高级会员

  1. 84 主题
  2. 168 评论
  3. 7 关注
  • 1今日版块发帖数
  • 11昨日版块发帖数
  • 1823昨日版块发帖数
  • 160板块关注人数

相关帖子

鸟哥的Linux私房菜服务器架设篇(第三版)
Linux6.5安装Oracle-11g-RAC(1)【视频教
Oracle.11g.从入门到精通
linux下安装oracle
Linux6.5安装Oracle-11g-RAC(2)【视频教

客服中心

010-63942882

服务时段:9:00-20:00

[分享] sqlserver 2016 winserver 2012 goldengate 12.3 cdc capterue

[复制链接]
发表于 2017-10-2 11:00:35 | 显示全部楼层 |阅读模式
1.建立文件夹,提取软件至相应目录
2.新建数据库source,target
3.
源端:
USE [source]

GO

CREATE SCHEMA [ogg] AUTHORIZATION [dbo]

GO

目标端:
USE [source]

GO

CREATE SCHEMA [ogg] AUTHORIZATION [dbo]

GO

4.点击source,target数据库架构,确认ogg用户建立成功


5.创建测试表

use source;

create table dbo.test1 (id int primary key, name varchar(50));

create table dbo.test2( id int, name varchar(50), age int);

use target;

create table dbo.test1 (id int primary key, name varchar(50));

create table dbo.test2( id int, name varchar(50), age int);


6.配置并启动mgr on node1:

PS C:\Users\Administrator> cmd
Microsoft Windows [版本 6.3.9600]
(c) 2013 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>cd /d c:\ogg



c:\ogg>ggsci.exe

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug  5 2017 10:02:48
Operating system character set identified as GBK.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1) 1> create subdirs

Creating subdirectories under current directory c:\ogg

Parameter file                 c:\ogg\dirprm: created.
Report file                    c:\ogg\dirrpt: created.
Checkpoint file                c:\ogg\dirchk: created.
Process status files           c:\ogg\dirpcs: created.
SQL script files               c:\ogg\dirsql: created.
Database definitions files     c:\ogg\dirdef: created.
Extract data files             c:\ogg\dirdat: created.
Temporary files                c:\ogg\dirtmp: created.
Credential store files         c:\ogg\dircrd: created.
Masterkey wallet files         c:\ogg\dirwlt: created.
Dump files                     c:\ogg\dirdmp: created.


GGSCI (node1) 2> edit params mgr


GGSCI (node1) 3> view params mgr
port 7809


GGSCI (node1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (node1) 5> start mgr
Manager started.


GGSCI (node1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node1) 7>


配置并启动mgr on node2:
PS C:\Users\Administrator> cmd
Microsoft Windows [版本 6.3.9600]
(c) 2013 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>cd /d c:\ogg

c:\ogg>ggsci.exe

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug  5 2017 10:02:48
Operating system character set identified as GBK.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (node2) 1> create subdirs

Creating subdirectories under current directory c:\ogg

Parameter file                 c:\ogg\dirprm: created.
Report file                    c:\ogg\dirrpt: created.
Checkpoint file                c:\ogg\dirchk: created.
Process status files           c:\ogg\dirpcs: created.
SQL script files               c:\ogg\dirsql: created.
Database definitions files     c:\ogg\dirdef: created.
Extract data files             c:\ogg\dirdat: created.
Temporary files                c:\ogg\dirtmp: created.
Credential store files         c:\ogg\dircrd: created.
Masterkey wallet files         c:\ogg\dirwlt: created.
Dump files                     c:\ogg\dirdmp: created.


GGSCI (node2) 2> edit params mgr


GGSCI (node2) 3> view params mgr
port 7809


GGSCI (node2) 4> start mgr
Manager started.


GGSCI (node2) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node2) 6>


7

GGSCI (node1) 8> edit params ./GLOBALS


GGSCI (node1) 9> view params ./GLOBALS
GGSCHEMA OGG


GGSCI (node1) 10>


GGSCI (node2) 6> edit params ./GLOBALS


GGSCI (node2) 7> view params ./GLOBALS
GGSCHEMA OGG

windows 平台下会默认创建为./dirdat/globals.prm文件,切换到$OGG_HOME目录下手工创建正确的文件,创建后退出GGSCI,停止mgr,
重新登录ggsci并重新启动mgr

8.node1,node2 创建用户并赋权

node1:

USE [master]  
GO  
CREATE LOGIN [oggsrc] WITH PASSWORD=N'oggpsw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  
GO  
EXEC master..sp_addsrvrolemember @loginame = N'oggsrc', @rolename = N'sysadmin'  
GO  


node2:

USE [master]  
GO  
CREATE LOGIN [oggdest] WITH PASSWORD=N'oggpsw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  
GO  
EXEC master..sp_addsrvrolemember @loginame = N'oggdest', @rolename = N'sysadmin'  
GO  


赋权部分参考截图

9.node1,node2 启动agent


10:
源库上启用cdc

use source

EXECUTE sys.sp_cdc_enable_db

OGG trandata

11.node1 node2 分别建立数据源source,target
12.源端启用cdc,开启表级别附加日志
GGSCI>dblogin sourcedb source, userid oggsrc, password oggpsw

GGSCI>add trandata dbo.test1

GGSCI>add trandata dbo.test2
执行完成之后,可以看到多了一张配置表

ogg.OracleGGTranTables will be added automatically.


13 node1:

创建OGG clean job

需要先删除DB自带的clean job

EXECUTE sys.sp_cdc_drop_job 'cleanup'

然后在OGG安装目录下,进入命令行,执行如下语句,其中(local)是默认的sqlserver实例

ogg_cdc_cleanup_setup.bat createjob oggsrc oggpsw source (local) ogg
GGSCI (node1 as oggsrc@SOURCE) 9> exit

c:\ogg>ogg_cdc_cleanup_setup.bat createjob oggsrc oggpsw source (local) ogg

Oracle GoldenGate CDC cleanup job setup script
==============================================

Microsoft (R) SQL Server 命令行工具
版本 13.0.1601.5 NT
版权所有(C) 2015 Microsoft。保留所有权利。

用法: Sqlcmd            [-U 登录 ID]          [-P 密码]
  [-S 服务器]            [-H 主机名]          [-E 信任连接]
  [-N 加密连接][-C 信任服务器证书]
  [-d 使用数据库名称] [-l 登录超时值]     [-t 查询超时值]
  [-h 标题]           [-s 列分隔符]      [-w 屏幕宽度]
  [-a 数据包大小]        [-e 回显输入]        [-I 允许带引号的标识符]
  [-c 命令结束]            [-L[c] 列出服务器[清除输出]]
  [-q "命令行查询"]   [-Q "命令行查询" 并退出]
  [-m 错误级别]        [-V 严重级别]     [-W 删除尾随空格]
  [-u unicode 输出]    [-r[0|1] 发送到 stderr 的消息]
  [-i 输入文件]         [-o 输出文件]        [-z 新密码]
  [-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出]
  [-k[1|2] 删除[替换]控制字符]
  [-y 可变长度类型显示宽度]
  [-Y 固定长度类型显示宽度]
  [-p[1] 打印统计信息[冒号格式]]
  [-R 使用客户端区域设置]
  [-K 应用程序意向]
  [-M 多子网故障转移]
  [-b 出错时中止批处理]
  [-v 变量 = "值"...]  [-A 专用管理连接]
  [-X[1] 禁用命令、启动脚本、环境变量[并退出]]
  [-x 禁用变量替换]
  [-j 打印原始错误消息]
  [-? 显示语法摘要]
Command: createjob

Oracle GoldenGate CDC cleanup job and its relevant table(s) and procedure(s) are created.

c:\ogg>





14.源端配置抽取进程

GGSCI (node1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node1) 2> edit params ext01


GGSCI (node1) 3> view params ext01
extract ext01
sourcedb source, userid oggsrc, password oggpsw
exttrail ./dirdat/aa
table dbo.*;


GGSCI (node1) 4> add extract ext01,tranlog,begin now
EXTRACT added.


GGSCI (node1) 5> add exttrail ./dirdat/aa,extract ext01
EXTTRAIL added.

GGSCI (node1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT01       00:00:00      00:00:19


GGSCI (node1) 7> start ext01

Sending START request to MANAGER ...
EXTRACT EXT01 starting


GGSCI (node1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT01       00:00:26      00:00:06


GGSCI (node1) 9> info extract ext01 detail

EXTRACT    EXT01     Last Started 2017-09-15 17:13   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           4568
VAM Read Checkpoint  2017-09-15 17:13:14.818000


  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  ./dirdat/aa                                          0       1275        500 EXTTRAIL

  Extract Source                          Begin             End

  Not Available                           2017-09-15 17:13  2017-09-15 17:13
  Not Available                           * Initialized *   2017-09-15 17:13


Current directory    c:\ogg

Report file          c:\ogg\dirrpt\EXT01.rpt
Parameter file       c:\ogg\dirprm\EXT01.prm
Checkpoint file      c:\ogg\dirchk\EXT01.cpe
Process file         c:\ogg\dirpcs\EXT01.pce


GGSCI (node1) 13> view report ext01

***********************************************************************
               Oracle GoldenGate Capture for SQL Server
                   SQL Server Log Mining Method: CDC
      Version 12.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug  5 2017 11:14:34

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2017-09-15 17:13:41
***********************************************************************

Operating System Version:
Microsoft Windows Server 2012 R2, on x64
Version 6.3 (Build 9600)

Process id: 4568

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2017-09-15 17:13:41  INFO    OGG-03059  Operating system character set identified as GBK.

2017-09-15 17:13:41  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
extract ext01
sourcedb source, userid oggsrc, password ***

2017-09-15 17:13:41  WARNING OGG-05236  ODBC Warning: The specified DSN 'source' uses a client driver that may be inc
atible with the database server. Microsoft SQL Server requires SQLNCLI11.DLL,MSODBCSQL11.DLL,MSODBCSQL13.DLL or a mor
ecent version.

2017-09-15 17:13:41  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.

2017-09-15 17:13:41  INFO    OGG-03037  Session character set identified as GBK.
exttrail ./dirdat/aa
table dbo.*;

2017-09-15 17:13:41  INFO    OGG-01851  filecaching started: thread ID: 54567200.

2017-09-15 17:13:41  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
    file alloc: MapViewOfFile  file free: UnmapViewOfFile
    target directories:
    c:\ogg\dirtmp.

2017-09-15 17:13:41  WARNING OGG-01842  CACHESIZE PER DYNAMIC DETERMINATION (16G) LESS THAN RECOMMENDED: 64G (64bit s
em)
vm found: 29.44G
Check swap space. Recommended swap/extract: 128G (64bit system).

Database Version:
Microsoft SQL Server
Version 13.00.4446
ODBC Version 03.80.0000

Driver Information:
SQLSRV32.DLL
Version 06.03.9600
ODBC Version 03.52

2017-09-15 17:13:41  INFO    OGG-01052  No recovery is required for target file ./dirdat/aa000000000, at RBA 0 (file
opened).

2017-09-15 17:13:41  INFO    OGG-01478  Output file ./dirdat/aa is using format RELEASE 12.3.

2017-09-15 17:13:41  INFO    OGG-00182  VAM API running in single-threaded mode.

2017-09-15 17:13:41  INFO    OGG-01515  Positioning to begin time 2017年9月15日 下午5:13:14.

2017-09-15 17:13:41  INFO    OGG-05264  Opening DSN connection: source, Server: NODE1, Database: source.

2017-09-15 17:13:41  INFO    OGG-05255  Current CDC Capture Settings - job name cdc.source_capture, maxtrans: 500, ma
ans: 10, continuous: 1, polling interval: 5.

2017-09-15 17:13:41  INFO    OGG-05257  For CDC tuning best practices, please see https://technet.microsoft.com/en-us
brary/dd266396%28v=sql.100%29.aspx.

2017-09-15 17:13:41  INFO    OGG-05281  Current OGG cleanup Job Settings - Job Name: OracleGGCleanup_source_Job, JobS
dRec: , JobSchedFreq: , DatabaseName: source, Tranlogoption managecdccleanup: 0, threshold: 500, retention: 4,320.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2017-09-15 17:15:14  INFO    OGG-01021  Command received from GGSCI: STATS.



15.配置投递进程

配置泵取进程:

GGSCI (node1) 14> edit params pmp01


GGSCI (node1) 15> view params pmp01
EXTRACT PMP01
PASSTHRU
RMTHOST 192.168.1.112 MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE dbo.*;


GGSCI (node1) 16> ADD EXTRACT PMP01, EXTTRAILSOURCE ./dirdat/aa
EXTRACT added.


GGSCI (node1) 17> ADD RMTTRAIL ./dirdat/rt, EXTRACT PMP01
RMTTRAIL added.

GGSCI (node1) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT01       00:00:00      00:00:09
EXTRACT     STOPPED     PMP01       00:00:00      00:00:19


GGSCI (node1) 19> start pmp01

Sending START request to MANAGER ...
EXTRACT PMP01 starting


GGSCI (node1) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT01       00:00:00      00:00:07
EXTRACT     RUNNING     PMP01       00:00:00      00:00:08

16 node2配置复制进程


GGSCI (node2) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING




GGSCI (node2) 21> dblogin sourcedb target, userid oggdest, password oggpsw

2017-09-15 17:31:06  WARNING OGG-05236  ODBC Warning: The specified DSN 'target' uses a client driver that may be incomp
atible with the database server. Microsoft SQL Server requires SQLNCLI11.DLL,MSODBCSQL11.DLL,MSODBCSQL13.DLL or a more r
ecent version.

2017-09-15 17:31:06  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.

2017-09-15 17:31:06  INFO    OGG-03037  Session character set identified as GBK.
Successfully logged into database.

GGSCI (node2 as oggdest@TARGET) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node2 as oggdest@TARGET) 23> edit params rep01


GGSCI (node2 as oggdest@TARGET) 24> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;


GGSCI (node2 as oggdest@TARGET) 25> add checkpointtable dbo.OGGCHKPT

Successfully created checkpoint table dbo.OGGCHKPT.

GGSCI (node2 as oggdest@TARGET) 26> ADD REPLICAT REP01, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE dbo.OGGCHKPT
REPLICAT added.


GGSCI (node2 as oggdest@TARGET) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP01       00:00:00      00:00:18


GGSCI (node2 as oggdest@TARGET) 28> start rep01

Sending START request to MANAGER ...
REPLICAT REP01 starting





GGSCI (node2 as oggdest@TARGET) 31> edit params rep01


GGSCI (node2 as oggdest@TARGET) 32> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;


GGSCI (node2 as oggdest@TARGET) 33> start rep01

Sending START request to MANAGER ...
REPLICAT REP01 starting






GGSCI (node2 as oggdest@TARGET) 64> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP01       00:00:00      00:00:03


GGSCI (node2 as oggdest@TARGET) 65> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;


GGSCI (node2 as oggdest@TARGET) 66>


注意:

1.重启后sqlserver agent 默认不启动,可以配置为自动启动或人工启动,cdc 抽取模式依赖代理,不启动会有问题
2.数据源配置
  源端数据源   odbc       抽取进程正常,无警告信息正常
               sqlserver  抽取进程正常,含有警告信息
  目标端数据源 sqlserver native clent 正常,有警告信息
               odbc      此模式配置报错,待以后测试
               sqlserver 此模式配置报错,待以后测试
回复

使用道具 举报

精彩评论 1

跳转到指定楼层
您需要登录后才可以回帖 登录 | 注册

本版积分规则

甲骨论是什么| 甲骨论   | ( 京ICP备12024223号 )

Copyright © 2017 Bosenrui Inc.   All Rights Reserved.

快速回复 返回顶部 返回列表