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 2012 to sqlserver 2012 goldengate 12.1.2.0.1 复制实践操作

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

os: Microsoft Windows Server 2012 Datacenter
介质:cn_windows_server_2012_vl_x64_dvd_917962.iso
sqlserver:
        Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
        Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
介质:cn_sql_server_2012_enterprise_edition_with_sp1_x64_dvd_1234495.iso

goldengate:
          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
介质:V42688.zip



调整虚拟机的以下配置:

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

node配置启动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 (node) 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 (node) 2> edit params mgr


GGSCI (node) 3> view params mgr
port 7809


GGSCI (node) 4> start mgr
Manager started.






源库配置:


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


目标库target:

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




node,node 分别创建数据源

node source
node target

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




调整相关表属性:

GGSCI (node) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node) 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 (node) 2> add trandata tcustmer

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

GGSCI (node) 3> add trandata tcustord

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




GGSCI (node) 10> edit params ./GLOBALS


GGSCI (node) 7> view params ./GLOBALS
MGRSERVNAME OGG MGR Service

GGSCI (node) 8> shell install addservice


There's already a service 'OGG MGR Service' for this installation.
INSTALL ADDSERVICE command is ignored.


Install program terminated normally.



GGSCI (node) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING





GGSCI (node) 7> edit params ext2012


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


GGSCI (node) 9> add extract ext2012,tranlog,begin now
EXTRACT added.


GGSCI (node) 10> add exttrail ./dirdat/et,extract ext2012
EXTTRAIL added.

GGSCI (node) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT2016     00:00:00      00:00:21

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


GGSCI (node) 13>

GGSCI (node) 13> view params pmp2016
EXTRACT PMP2016
PASSTHRU
RMTHOST 192.168.1.112 MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE dbo.*;


GGSCI (node) 14> ADD EXTRACT PMP2016,EXTTRAILSOURCE ./dirdat/et
EXTRACT added.


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

GGSCI (node) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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



GGSCI (node) 17>



查看各进程状态正常:

GGSCI (node) 37> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2012    00:00:00      00:00:03



配置目标端:

建库 TargetDB

注意日志模式,可为simple


执行demo_mss_create.sql

选定新建库,创建测试表


创建数据源







GGSCI (node) 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 (node) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


编辑复制进程:

GGSCI (node) 18> EDIT PARAMS REP2012


GGSCI (node) 11> view params rep2012
REPLICAT REP2012
DBOPTIONS USEREPLICATIONUSER
TARGETDB TARGET
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;


目标端添加检查点表:

GGSCI (node) 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 (node) 21> add checkpointtable dbo.OGGCHKPT

Successfully created checkpoint table dbo.OGGCHKPT.




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


GGSCI (node) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT2012     00:00:00      00:00:03
REPLICAT    STOPPED     REP2012     00:00:00      00:00:02



启动抽取、复制进程:

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2012     00:00:00      00:00:03
REPLICAT    RUNNING     REP2012     00:00:00      00:00:02








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







问题一:

GGSCI (node) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT2012     00:00:00      01:24:08
REPLICAT    STOPPED     REP2012     00:00:00      01:24:08


GGSCI (node) 16> view report ext2012

***********************************************************************
               Oracle GoldenGate Capture 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:32:54

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


                    Starting at 2017-10-01 00:12:47
***********************************************************************

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

Process id: 2412

Description:

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

2017-10-01 00:12:47  INFO    OGG-03059  Operating system character set identified as GBK.

2017-10-01 00:12:47  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT EXT2012
SOURCEDB source

Source Context :
  SourceModule            : [ggdb.odbc.dbx]
  SourceID                : [../gglib/ggdbodbc/dbxodbc.c]
  SourceFunction          : [gl_check_odbc_err]
  SourceLine              : [1437]
  ThreaDBAcktrace         : [15] elements
                          : [c:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ+
0x886) [0x000007F957E209D6]]
                          : [c:\ogg\gglog.dll(?_MSG_ERR_ODBC_OPERATION_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@PEBD1
1HW4MessageDisposition@CMessageFactory@@@Z+0x59) [0x000007F957DD1D19]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x22a8f) [0x000007F6A1F8F5AF]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x2303f) [0x000007F6A1F8FB5F]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x245a0) [0x000007F6A1F910C0]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x24f32) [0x000007F6A1F91A52]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x11250) [0x000007F6A1E8F6B0]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x385ee) [0x000007F6A1EB6A4E]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x111b3) [0x000007F6A1F7DCD3]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x10610) [0x000007F6A1F7D130]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x1105f) [0x000007F6A1F7DB7F]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x39349) [0x000007F6A1EB77A9]]
                          : [c:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitialized@1@@Z+0x436) [0x000007F6A20C2BE2]]
                          : [C:\Windows\system32\KERNEL32.DLL(BaseThreadInitThunk+0x1a) [0x000007F96790167E]]
                          : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000007F968A2C3F1]]
###############################################################################################################################3
2017-10-01 00:12:47  ERROR   OGG-00551  Database operation failed: Couldn't connect to source. ODBC error: SQLSTATE 3700
0 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]无法打开登录所请求的数据库 "source"
。登录失败。
#################################################################################################################################33
2017-10-01 00:12:47  ERROR   OGG-01668  PROCESS ABENDING.




解决方式:

SOLUTION

1. If Manager is installed as service. Go to SQL Server Management studio,

Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -->Properties-->Server Role -->Enable sysadmin role

2. ggsci>stop mgr

3. ggsci>stop mgr

4. ggsci>start extract <extract-name>


调整后重新启动成功
GGSCI (node) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT2012     00:00:00      01:31:49
REPLICAT    STOPPED     REP2012     00:00:00      01:31:49


GGSCI (node) 18> start *

Sending START request to MANAGER ('OGG MGR Service') ...
EXTRACT EXT2012 starting

Sending START request to MANAGER ('OGG MGR Service') ...
REPLICAT REP2012 starting


GGSCI (node) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2012     01:31:56      00:00:02
REPLICAT    RUNNING     REP2012     00:00:00      00:00:01


GGSCI (node) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2012     00:00:00      00:00:04
REPLICAT    RUNNING     REP2012     00:00:00      00:00:04


GGSCI (node) 21>


官方参考文档:

Extract Abends With Error OGG-00551 Database Operation Failed: Couldn't Connect ODBC error: SQLSTATE 37000 native database error 4060. (文档 ID 1633138.1)        转到底部转到底部       

In this Document
Symptoms
Cause
Solution

APPLIES TO:

Oracle GoldenGate - Version 12.1.2.0.0 and later
Information in this document applies to any platform.
Checked for relevance on 10-Feb-2016
SYMPTOMS

ERROR OGG-00551 Database operation failed: Couldn't connect to ogg. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Can not open database "ogg" requested by the login. Login Failure.


dblogin sourcedb OGGTEST  and Add trandata works fine. Also extract can run from command prompt and when manager is not installed as service


CAUSE

The issue is caused by the following setup: NT AUTORITY/SYSTEM user does not have SQL Server fixed server role sysadmin

According to Oracle GoldenGate Installation Guide for SQL Server, if manager is installed as service then NT AUTORITY/SYSTEM or BUILTIN/adminstrators should have SQL Server fixed server role sysadmin.


SOLUTION

1. If Manager is installed as service. Go to SQL Server Management studio,

Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -->Properties-->Server Role -->Enable sysadmin role

2. ggsci>stop mgr

3. ggsci>stop mgr

4. ggsci>start extract <extract-name>





问题二:

2017-09-30 22:22:31  WARNING OGG-00091  VAM Client Report <[mssqlvam::TruncMgr::Timer] 另一个连接已经在当前数据库
更数据捕获运行 'sp_replcmds'。 Error (-2147217900): 另一个连接已经在当前数据库中为变更数据捕获运行 'sp_replcmds'


2012-02-01 16:30:31  WARNING OGG-00091  VAM Client Report <[TruncMgr::Timer]
一次只能有一个日志读取器代理或日志相关过程(sp_repldone、sp_replcmds 和 sp_replshowcmds)连接到某个数据库。如果执行了一个日志相关过程,
那么在启动日志读取器代理或者执行另一个日志相关过程之前,请删除执行第一个过程时所用的连接,或者在该连接上执行 sp_replflush。
Error (-2147217900): 一次只能有一个日志读取器代理或日志相关过程(sp_repldone、sp_replcmds 和 sp_replshowcmds)连接到某个数据库。
如果执行了一个日志相关过程,那么在启动日志读取器代理或者执行另一个日志相关过程之前,
请删除执行第一个过程时所用的连接,或者在该连接上执行 sp_replflush。







回复

使用道具 举报

精彩评论 1

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

本版积分规则

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

Copyright © 2017 Bosenrui Inc.   All Rights Reserved.

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