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。
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖