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

[分享] goldengate 12.2.0.1.1 捕获sql server 2012 标准版 (不同于企业版)

[复制链接]
发表于 2017-10-3 20:39:39 | 显示全部楼层 |阅读模式

os: Microsoft Windows Server 2012 r2 Datacenter
介质:cn_windows_server_2012_r2_vl_with_update_x64_dvd_6052729.iso
sqlserver:
        Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
        Feb 10 2012 19:39:15
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

介质:cn_sql_server_2012_standard_edition_x86_x64_dvd_813404.iso

goldengate:
          Oracle GoldenGate Command Interpreter for SQL Server
          Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
          Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
          Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
介质:V100722-01.zip



调整虚拟机的以下配置:

调整主机名
调整ip地址
关闭防火墙,或打开对应端口
建立目录c:\ogg并上传ogg软件至此目录下,解压选择提取至ogg 目录

主机配置启动mgr:

Windows PowerShell
版权所有 (C) 2014 Microsoft Corporation。保留所有权利。

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

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

c:\>cd ogg

c:\ogg>ggsci.exe




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.



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> start mgr
Manager started.






源库配置:


建库
node1:
源库:source
注意日志模式 full
备份
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SourceDB.bak

node1:
目标库:target:

source ,target 分别执行demo_mss_create.sql创建测试用表执行前分别添加
use source
use target

配置复制 右击复制 选择配置发布,一路next


node1分别创建数据源

source
target

开始--管理工具---ODBC 数据源(64位)
注意选项
集成Windows身份验证
sql server native client 11.0




调整相关表属性:

GGSCI (node1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node1) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node1) 1> dblogin sourcedb source

2017-10-01 09:40:32  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.

2017-10-01 09:40:32  INFO    OGG-03037  Session character set identified as GBK.
Successfully logged into database.

GGSCI (node1) 2> add trandata tcustmer

Logging of supplemental log data is enabled for table dbo.tcustmer

GGSCI (node1) 3> add trandata tcustord

Logging of supplemental log data is enabled for table dbo.tcustord


添加附加日志之前确认表都有主键

执行ADD TRANDATA 命令后系统自动添加  复制---本地发布---[source]:GoldenGate source Publisher

sql server 代理-----作业----node11-source-1 右击stop,右击disabled



GGSCI (node1) 10> edit params ./GLOBALS


GGSCI (node1) 7> view params ./GLOBALS
MGRSERVNAME OGG

GGSCI (node1) 8> shell install addservice




Install program terminated normally.



GGSCI (node1) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING





GGSCI (node1) 7> edit params extlocal


GGSCI (node1) 10> view params extlocal
EXTRACT extlocal
SOURCEDB source
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL ./dirdat/et
TABLE dbo.tcustmer;
TABLE dbo.tcustord;


GGSCI (node1) 9> add extract extlocal,tranlog,begin now
EXTRACT added.


GGSCI (node1) 10> add exttrail ./dirdat/et,extract extlocal
EXTTRAIL added.

GGSCI (node1) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     extlocal    00:00:00      00:00:21

同一实例内的两个库,pmp未配置略掉
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
GGSCI (node1) 12> edit params pmp2016


GGSCI (node1) 13>

GGSCI (node1) 13> view params pmplocal
EXTRACT PMPLOCAL
PASSTHRU
RMTHOST 192.168.1.112 MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE dbo.*;


GGSCI (node1) 14> ADD EXTRACT PMPLOCAL,EXTTRAILSOURCE ./dirdat/et
EXTRACT added.


GGSCI (node1) 15> ADD RMTTRAIL ./dirdat/rt, EXTRACT PMP2016
RMTTRAIL added.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

GGSCI (node1) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     extlocal    00:00:00      00:02:28



GGSCI (node1) 17>



查看各进程状态正常:

GGSCI (node1) 37> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     extlocal    00:00:00      00:00:03









GGSCI (node1) 16> dblogin sourcedb target

2014-05-07 23:01:10  INFO    OGG-03036  Database character set identified as win
dows-936. Locale: zh_Hans_CN.

2014-05-07 23:01:10  INFO    OGG-03037  Session character set identified as GBK.

Successfully logged into database.

GGSCI (node1) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


编辑复制进程:

GGSCI (node1) 18> EDIT PARAMS replocal


GGSCI (node1) 11> view params replocal
REPLICAT replocal
DBOPTIONS USEREPLICATIONUSER
TARGETDB TARGET
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;


目标端添加检查点表:

GGSCI (node1) 20> DBLOGIN SOURCEDB target

2014-05-07 23:05:50  INFO    OGG-03036  Database character set identified as win
dows-936. Locale: zh_Hans_CN.

2014-05-07 23:05:50  INFO    OGG-03037  Session character set identified as GBK.

Successfully logged into database.

GGSCI (node1) 21> add checkpointtable dbo.OGGCHKPT

Successfully created checkpoint table dbo.OGGCHKPT.




GGSCI (node1) 23> ADD REPLICAT replocal, EXTTRAIL ./dirdat/et, CHECKPOINTTABLE dbo.OGGCHKPT
REPLICAT added.


GGSCI (node1) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     extlocal     00:00:00      00:00:03
REPLICAT    STOPPED     replocal     00:00:00      00:00:02



启动抽取、复制进程:

GGSCI (node1) 14> start *
GGSCI (node1) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     extlocal     00:00:00      00:00:03
REPLICAT    RUNNING     replocal     00:00:00      00:00:02








测试:源端source运行 demo_mss_insert.sql (注意切换数据库use source),目标端targetdb 查看相应表的行数,同步成功。



添加新表复制步骤:
lag * 确认 eof
stop *
source 端建表 注意要有主键
dblogin sourcedb source
add trandata dbo.tablename
target 端建表 注意要有主键
编辑extract ,replicat 参数文件,确认新表在抽取范围内
start *
执行新表事务
stats * 确认事务抽取正常




问题:
重启mgr,extract,replicat进程报错
***********************************************************************
               Oracle GoldenGate Capture for SQL Server
      Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 16:22:53

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


                    Starting at 2017-10-03 17:09:27
***********************************************************************

Operating System Version:
Microsoft Windows Server 2012 , on x64
Version 6.2 (Build 9200)

Process id: 120

Description:

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

2017-10-03 17:09:27  INFO    OGG-03059  Operating system character set identified as GBK.

2017-10-03 17:09:27  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT extlocal
SOURCEDB source

Source Context :
  SourceModule            : [ggdb.odbc.dbx]
  SourceID                : [../gglib/ggdbodbc/dbxodbc.c]
  SourceFunction          : [gl_check_odbc_err]
  SourceLine              : [1500]
  ThreaDBAcktrace         : [16] elements
                          : [c:\ogg\gglog.dll(??1CContextItem@@UEAA@XZ+0x2a76) [0x00007FF81CBAE986]]
                          : [c:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x561) [0x00007FF81CBAEFD1]]
                          : [c:\ogg\gglog.dll(?_MSG_ERR_ODBC_OPERATION_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@PEBD11HW4MessageDisposition@CMessageFactory@@@Z+0x59) [0x00007FF81CB41D89]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x1a5b8) [0x00007FF7209ABB58]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x1aadb) [0x00007FF7209AC07B]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x1be32) [0x00007FF7209AD3D2]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x1c801) [0x00007FF7209ADDA1]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x16b79) [0x00007FF7208A09D9]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x41f72) [0x00007FF7208CBDD2]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x85c3) [0x00007FF720999B63]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x79cb) [0x00007FF720998F6B]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x8463) [0x00007FF720999A03]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x42f2b) [0x00007FF7208CCD8B]]
                          : [c:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitialized@1@@Z+0x42a) [0x00007FF720B11596]]
                          : [C:\Windows\system32\KERNEL32.DLL(BaseThreadInitThunk+0x22) [0x00007FF8361713D2]]
                          : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x34) [0x00007FF8384003C4]]

2017-10-03 17:09:27  ERROR   OGG-00551  Database operation failed: Couldn't connect to source. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]无法打开登录所请求的数据库 "source"。登录失败。.

2017-10-03 17:09:27  ERROR   OGG-01668  PROCESS ABENDING.




解决:

登录ssms------安全性----登录名----右击 NT AUTHORITY\SYSTEM---属性-------服务器角色------选中sysadmin---点击确定


然后启动成功










回复

使用道具 举报

精彩评论 2

跳转到指定楼层
dongxujian 发表于 2017-10-3 20:41:11
详见GIMSS_12.2.0.1.1官档附录说明
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

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

Copyright © 2017 Bosenrui Inc.   All Rights Reserved.

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