sqlserver 2014 to sqlserver 2014 goldengate 12.2.0.1.1 ALO模式


os: Microsoft Windows Server 2012 r2_Datacenter
介质:cn_windows_server_2012_r2_vl_with_update_x64_dvd_6052729.iso
sqlserver:
        Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
介质:cn_sql_server_2014_enterprise_edition_x64_dvd_3932882.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

概述:

   node1上源端数据库source,node2上目标端数据库target,node1计划任务备份数据库source日志至共享文件夹,node2 extlocal ALO 模式抽取共享文件夹上的日志实现
数据同步,replocal进程同步数据至target数据库

调整两台虚拟机的以下配置:

node1:192.168.1.113
node2:192.168.1.114

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


node2主机配置启动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:\ogg

c:\ogg>ggsci.exe

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
Operating system character set identified as GBK.

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



GGSCI (node2) 1>





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.






源库配置:


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



node2:
目标库target:

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




node2创建数据源 source 、target



开始--管理工具---ODBC 数据源(64位)--系统DSN--Microsoft SQL Server Native Client 版本 11.00.6518
---添加

远程数据源 source:
名称:source 服务器:node1 next
集成Windows身份验证       next
更改默认数据库 source     next
缺省值                    finish

本地数据源 target:
名称:target 服务器:node2 next
集成Windows身份验证       next
更改默认数据库 target     next
缺省值                    finish



node1 主机上创建source库日志备份目录c:\backup,调整属性为共享,配置归档日志备份计划,每3分钟备份一次
参考<<日志备份维护计划及共享文件夹设置.fbr>>录像



node2:
调整相关表属性:

c:\ogg>ggsci.exe

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
Operating system character set identified as GBK.

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




GGSCI (node2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node2) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (node2) 3> dblogin sourcedb source

2017-10-01 13:52:25  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.

2017-10-01 13:52:25  INFO    OGG-03037  Session character set identified as GBK.
Successfully logged into database.

GGSCI (node2) 4> add trandata dbo.tcustmer

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

GGSCI (node2) 5> add trandata dbo.tcustord

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

GGSCI (node2) 6>













GGSCI (node2) 37> edit params ./GLOBALS


GGSCI (node2) 38> view params ./GLOBALS
MGRSERVNAME OGG


GGSCI (node2) 39> shell install addservice


Service 'OGG' created.


Install program terminated normally.



GGSCI (node2) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING







GGSCI (node2) 7> edit params extlocal


GGSCI (node2) 10> view params extlocal
EXTRACT extlocal
SOURCEDB source
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST ("//node1/backup")
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL ./dirdat/rt
TABLE dbo.tcustmer;
TABLE dbo.tcustord;


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


GGSCI (node2) 10> add exttrail ./dirdat/rt,extract extlocal
EXTTRAIL added.

GGSCI (node2) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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



GGSCI (node2) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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



GGSCI (node2) 17>



查看各进程状态正常:

GGSCI (node2) 37> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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











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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


编辑复制进程:

GGSCI (node2) 18> edit params replocal


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


目标端添加检查点表:

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

Successfully created checkpoint table dbo.OGGCHKPT.




GGSCI (node2) 23> ADD REPLICAT REPLOCAL, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE dbo.OGGCHKPT
REPLICAT added.


GGSCI (node2) 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 (node2) 14> start *
GGSCI (node2) 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 查看相应表的行数,同步成功。







问题一:

node2抽取进程读取的日志备份来源于node1主机上的维护计划的每次备份,依靠sql server agent完成,所以
sql server agent 配置为开机自动启动(通过sqlserver 配置管理器)

问题二:

start extlocal 报错 OGG-05209  Failed to open the SQL Server process, 1,204, with Windows system error 87

2017-10-02 08:38:19  INFO    OGG-00178  VAM Client Report <Opening files for DSN: source, Server: NODE1, Da
e>.

Source Context :
  SourceModule            : [vam.sqlserver.LogInfo]
  SourceID                : [ggmssqlvam/LogInfo.cpp]
  SourceFunction          : [mssqlvam::OnlineLogInfo::OnlineLogInfo]
  SourceLine              : [236]
  ThreaDBAcktrace         : [20] elements
                          : [c:\ogg\gglog.dll(??1CContextItem@@UEAA@XZ+0x2a76) [0x00007FF941CDE986]]
                          : [c:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSource
0x561) [0x00007FF941CDEFD1]]
                          : [c:\ogg\gglog.dll(?_MSG_ERR_MSSQL_LI_CANNOT_OPEN_SQL_SERVER_PROCESS@@YAPEAVCMes
urceContext@@IIW4MessageDisposition@CMessageFactory@@@Z+0x48) [0x00007FF941CA2F28]]
                          : [c:\ogg\extract.exe(VAMControl+0x6d6c9) [0x00007FF7EB020539]]
                          : [c:\ogg\extract.exe(VAMControl+0x3d47b) [0x00007FF7EAFF02EB]]
                          : [c:\ogg\extract.exe(VAMControl+0x3ce73) [0x00007FF7EAFEFCE3]]
                          : [c:\ogg\extract.exe(VAMControl+0xa46c) [0x00007FF7EAFBD2DC]]
                          : [c:\ogg\extract.exe(VAMInitialize+0xe43) [0x00007FF7EAFB1763]]
                          : [c:\ogg\extract.exe(GGDataBufferGetNextChunk+0x3bc06) [0x00007FF7EAF2A356]]
                          : [c:\ogg\extract.exe(GGDataBufferGetNextChunk+0xdac) [0x00007FF7EAEEF4FC]]
                          : [c:\ogg\extract.exe(GGDataBufferGetNextChunk+0x56b) [0x00007FF7EAEEECBB]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x3c59c) [0x00007FF7EADE63FC]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x42dc5) [0x00007FF7EADECC25]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x85c3) [0x00007FF7EAEB9B63]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x79cb) [0x00007FF7EAEB8F6B]]
                          : [c:\ogg\extract.exe(_ggTryDebugHook+0x8463) [0x00007FF7EAEB9A03]]
                          : [c:\ogg\extract.exe(ERCALLBACK+0x42f2b) [0x00007FF7EADECD8B]]
                          : [c:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitialized@1@@Z+0x42a) [0x00007FF
                          : [C:\Windows\system32\KERNEL32.DLL(BaseThreadInitThunk+0x22) [0x00007FF9608413D2
                          : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x34) [0x00007FF9609F03C4]]

2017-10-02 08:38:19  ERROR   OGG-05209  Failed to open the SQL Server process, 1,204, with Windows system e

2017-10-02 08:38:19  ERROR   OGG-01668  PROCESS ABENDING.


处理:
extlocal 参数文件少加了下面的参数
TRANLOGOPTIONS ARCHIVEDLOGONLY








另一种模式:
源端库ALO模式抽取,日志备份维护计划不变
node1 上创建本地数据源source、远程数据源target


GGSCI (node1) 36> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTLOCAL    00:01:14      00:00:11
REPLICAT    RUNNING     REPLOCAL    00:00:00      00:00:02


GGSCI (node1) 37> view params extlocal
EXTRACT extlocal
SOURCEDB source
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST ("c:\backup")
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL ./dirdat/rt
TABLE dbo.tcustmer;
TABLE dbo.tcustord;


GGSCI (node1) 38> view params replocal
REPLICAT REPLOCAL
DBOPTIONS USEREPLICATIONUSER
TARGETDB TARGET
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
标签: 暂无标签
dongxujian

写了 86 篇文章,拥有财富 384,被 13 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈