本文源自：http://blog.sina.com.cn/s/blog_14d5a51a90102wh3m.html
1.实验目的：
通过OGG测试sqlserver2012到oracle的表数据dml同步
2.实验环境：
| 源端 | 目标端 | 备注 | |
| 操作系统 | Windows2008x64 | Centos6.5 | |
| 数据库 | Sqlserver2012 | Oracle11.2.0.4 | |
| OGG版本 | 12.2.0.1.1 | 12.2.0.1.1 | 介质获取地址： http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html |
| 主机名 | w2008x64 | master | |
| IP | 192.168.89.136 | 192.168.89.118 | |
| 数据库名 | adjyc | db | |
| 用户 | jyc/jyc | jyc/1234 | |
| 同步的表 | ip | ip |
3.配置步骤
3.1源端sqlserver
3.1.1安装ogg软件
介质下载：
存放到f盘: ggs_Windows_x64_MSSQL_64bit.zip
解压到为OGG目录下。
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>f:
F:\>cd ogg
F:\ogg>ggsci
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 (w2008x64) 1> CREATE SUBDIRS
Creating subdirectories under current directory F:\ogg
Parameter files F:\ogg\dirprm: created
Report files F:\ogg\dirrpt: created
Checkpoint files F:\ogg\dirchk: created
Process status files F:\ogg\dirpcs: created
SQL script files F:\ogg\dirsql: created
Database definitions files F:\ogg\dirdef: created
Extract data files F:\ogg\dirdat: created
Temporary files F:\ogg\dirtmp: created
Credential store files F:\ogg\dircrd: created
Masterkey wallet files F:\ogg\dirwlt: created
Dump files F:\ogg\dirdmp: created
GGSCI (w2008x64) 2> exit
F:\ogg>INSTALL ADDSERVICE
Service 'GGSMGR' created.
Install program terminated normally.
F:\ogg>
检查管理服务可以看到：
3.1.2准备源库环境
配置数据源：
F:\ogg>ggsci
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 (w2008x64) 1> dblogin sourcedb demo
2016-01-29 12:12:48 INFO OGG-03036 Database character set identified as win
dows-936. Locale: zh_CN.
2016-01-29 12:12:48 INFO OGG-03037 Session character set identified as GBK.
Successfully logged into database.
GGSCI (w2008x64) 2>
GGSCI (w2008x64) 3> add trandata ip
Logging of supplemental log data is enabled for table dbo.ip
GGSCI (w2008x64) 4> edit params defgen
GGSCI (w2008x64) 5> view params defgen
defsfile F:\ogg\dirdef\ip.def
sourcedb demo –注意此处在后边需要修改增加用户名和密码
table dbo.ip;
F:\ogg>defgen paramfile F:\ogg\dirprm\defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator 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:16:15
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-01-29 12:23:21
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 1584
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile F:\ogg\dirdef\ip.def
sourcedb demo
2016-01-29 12:23:21 INFO OGG-03036 Database character set identified as win
dows-936. Locale: zh_CN.
2016-01-29 12:23:21 INFO OGG-03037 Session character set identified as GBK.
table dbo.ip;
Retrieving definition for dbo.ip.
Definitions generated for 1 table in F:\ogg\dirdef\ip.def.
F:\ogg>
查看创建表的脚本：
CREATE TABLE [dbo].[ip](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[onip] [bigint] NULL,
[offip] [bigint] NULL,
[addj] [nvarchar](50) NULL,
[addf] [nvarchar](250) NULL,
[onip1] [nvarchar](16) NULL,
[offip1] [nvarchar](16) NULL
) ON [PRIMARY]
GO
3.2目标端oracle
3.2.1安装ogg软件
介质下载：
http://download.oracle.com/otn/goldengate/122011/fbo_ggs_Linux_x64_shiphome.zip
通过winscp等工具上传介质到目标机器。
[root@master ogg]# chown -R oracle:dba /ogg
[root@master ogg]# pwd
/ogg
[root@master ogg]# ls -l
total 464472
-rw-r--r--. 1 oracle dba 475611228 Jan 28 19:08 fbo_ggs_Linux_x64_shiphome.zip
通过unzip解压。
通过xshell工具图形化安装ogg软件：
[oracle@master ogg]$ ls
fbo_ggs_Linux_x64_shiphome fbo_ggs_Linux_x64_shiphome.zip OGG-12.2.0.1.1-ReleaseNotes.pdf OGG-12.2.0.1-README.txt
[oracle@master ogg]$ pwd
/ogg
[oracle@master ogg]$ mv fbo_ggs_Linux_x64_shiphome soft
[oracle@master ogg]$ pwd
/ogg
[oracle@master ogg]$ cd soft
[oracle@master soft]$ ls
Disk1
[oracle@master soft]$ cd Disk1
[oracle@master Disk1]$ ls
install response runInstaller stage
[oracle@master Disk1]$ pwd
/ogg/soft/Disk1
[oracle@master Disk1]$ export DISPLAY=192.168.89.1:0.0
[oracle@master Disk1]$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 21434 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3994 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,
Continue? (y/n) [n] n
User Selected: No
Exiting Oracle Universal Installer, log for this session can be found at /home/oracle/app/oraInventory/logs/installActions2016-01-28_07-16-26PM.log
[oracle@master Disk1]$ exit
logout
[root@master ogg]# xhost +
access control disabled, clients can connect from any host
[root@master ogg]# su - oracle
[oracle@master ~]$ cd /ogg/soft/Disk1/
[oracle@master Disk1]$ export DISPLAY=192.168.89.1:0.0
[oracle@master Disk1]$ ./runInstaller
提示目录不为空直接忽略即可。
[oracle@master ogg]$ pwd
/ogg
[oracle@master ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (master) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
3.2.2初始化表结构
通过 WinSCP工具将 ip.def 从 Windows 系统复制到 Linux 系统上的 /ogg/dirdef 文件夹中。
[oracle@master dirdef]$ pwd
/ogg/dirdef
[oracle@master dirdef]$ ls -l
total 4
-rw-r--r--. 1 oracle dba 1769 Jan 28 20:23 ip.def
创建表脚本：
CREATE TABLE ip(
ID number NOT NULL,
onip number NULL,
offip number NULL,
addj varchar2(50) NULL,
addf varchar2(250) NULL,
onip1 varchar2(16) NULL,
offip1 varchar2(16) NULL
);
SQL> conn jyc/1234
Connected.
SQL> CREATE TABLE ip(
ID number NOT NULL,
onip number NULL,
offip number NULL,
addj varchar2(50) NULL,
addf varchar2(250) NULL,
onip1 varchar2(16) NULL,
offip1 varchar2(16) NULL
);
2 3 4 5 6 7 8 9
Table created.
SQL> desc ip;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ONIP NUMBER
OFFIP NUMBER
ADDJ VARCHAR2(50)
ADDF VARCHAR2(250)
ONIP1 VARCHAR2(16)
OFFIP1 VARCHAR2(16)
3.3源端初始化配置：
GGSCI (w2008x64) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (w2008x64) 2> edit params inext
添加内容：
SOURCEISTABLE
SOURCEDB demo
RMTHOST 192.168.89.118, MGRPORT 7809
RMTFILE /ogg/dirdat/ex
TABLE dbo.ip;
GGSCI (w2008x64) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (w2008x64) 4> edit param mgr
GGSCI (w2008x64) 5> view param mgr
port 7809
GGSCI (w2008x64) 6> start mgr
Starting Manager as service ('GGSMGR')...
Service started.
GGSCI (w2008x64) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (w2008x64) 8>
3.4目标端初始化配置：
[oracle@master ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (master) 1> edit params inload
GGSCI (master) 2> view param inload
SPECIALRUN
END RUNTIME
USERID jyc, PASSWORD 1234
EXTFILE /ogg/dirdat/ex
SOURCEDEFS /ogg/dirdef/ip.def
MAP dbo.ip, TARGET jyc.ip;
3.5源端执行初始化数据：
F:\ogg>extract paramfile dirprm\inext.prm reportfile dirrpt\inext.rpt
2016-01-29 12:44:34 INFO OGG-01017 Wildcard resolution set to IMMEDIATE bec
ause SOURCEISTABLE is used.
***********************************************************************
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 2016-01-29 12:44:34
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 2840
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-29 12:44:34 INFO OGG-03059 Operating system character set identifie
d as GBK.
2016-01-29 12:44:34 INFO OGG-02695 ANSI SQL parameter syntax is used for pa
rameter parsing.
2016-01-29 12:44:35 INFO OGG-03036 Database character set identified as win
dows-936. Locale: zh_CN.
2016-01-29 12:44:35 INFO OGG-03037 Session character set identified as GBK.
2016-01-29 12:44:35 INFO OGG-06509 Using the following key columns for sour
ce table dbo.ip: ID.
2016-01-29 12:44:35 INFO OGG-01851 filecaching started: thread ID: 51498192
.
2016-01-29 12:44:35 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: MapViewOfFile anon free: UnmapViewOfFile
file alloc: MapViewOfFile file free: UnmapViewOfFile
target directories:
F:\ogg\dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 4G
CACHEPAGEOUTSIZE (default): 4M
PROCESS VM AVAIL FROM OS (min): 6.35G
CACHESIZEMAX (strict force to disk): 5.67G
2016-01-29 12:44:35 WARNING OGG-01842 CACHESIZE PER DYNAMIC DETERMINATION (4G)
LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 6.35G
Check swap space. Recommended swap/extract: 128G (64bit system).
Database Version:
Microsoft SQL Server
Version 11.00.2100
ODBC Version 03.80.0000
Driver Information:
sqlncli11.dll
Version 11.00.2100
ODBC Version 03.80
2016-01-29 12:44:51 INFO OGG-01226 Socket buffer size set to 27985 (flush s
ize 27985).
2016-01-29 12:44:51 INFO OGG-01478 Output file /ogg/dirdat/ex is using form
at RELEASE 12.2.
2016-01-29 12:44:51 INFO OGG-02911 Processing table dbo.ip.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2016-01-29 12:44:52 (activity since 2016-01-29 12:44:35)
Output to /ogg/dirdat/ex:
From Table dbo.ip:
# inserts: 62296
# updates: 0
# deletes: 0
# discards: 0
F:\ogg>
3.6目标端执行加载数据：
[oracle@master ogg]$ sqlplus jyc/1234
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 20:45:43 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from ip;
COUNT(*)
----------
0
[oracle@master ogg]$ ./replicat paramfile dirprm/inload.prm
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 01:27:04
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-01-28 20:46:54
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64
Node: master
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 4425
Description:
2016-01-28 20:46:54 WARNING OGG-02904 Replication of PARTIAL XML containing NCHAR/NVARCHAR/NCLOB data may cause divergence.
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-28 20:46:54 INFO OGG-03059 Operating system character set identified as UTF-8.
2016-01-28 20:46:54 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2016-01-28 20:46:55 INFO OGG-03528 The source database character set, as determined from the table definition file, is windows-936.
SPECIALRUN
END RUNTIME
USERID jyc, PASSWORD ***
EXTFILE /ogg/dirdat/ex
SOURCEDEFS /ogg/dirdef/ip.def
MAP dbo.ip, TARGET jyc.ip;
2016-01-28 20:46:57 WARNING OGG-06471 Unable to disable trigger firing.
2016-01-28 20:46:57 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 4M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
2016-01-28 20:46:57 INFO OGG-02243 Opened trail file /ogg/dirdat/ex000000 at 2016-01-28 20:46:57.481518.
2016-01-28 20:46:57 INFO OGG-03522 Setting session time zone to source database time zone 'Asia/Shanghai'.
2016-01-28 20:46:57 INFO OGG-01014 Positioning with begin time: Jan 1, 1970 12:00:00 AM, starting record time: Jan 28, 2016 8:44:51 PM at extseqno 0, extrba 1854.
2016-01-28 20:46:57 INFO OGG-03506 The source database character set, as determined from the trail file, is windows-936.
Source Context :
SourceModule : [er.replicat.replicat]
SourceID : [/scratch/aime/adestore/views/aime_adc4150408/oggcore/OpenSys/src/app/er/replicat/replicat.cpp]
SourceFunction : [replicat_main]
SourceLine : [1481]
ThreadBacktrace : [10] elements
: [/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7ffda3714f1b]]
: [/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7ffda370f0a4]]
: [/ogg/libgglog.so(_MSG_ERR_NO_DB_EVENT_SET(CSourceContext*, CMessageFactory::MessageDisposition)+0x29) [0x7ffda36e5220]]
: [./replicat() [0x621db8]]
: [./replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6d9d50]]
: [./replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6dacdd]]
: [./replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x6dadc1]]
: [./replicat(main+0x3b) [0x624a6b]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x318a61ed1d]]
: [./replicat() [0x55b0a9]]
2016-01-28 20:46:57 ERROR OGG-02091 Operation not supported because enable_goldengate_replication is not set to true.
2016-01-28 20:46:57 ERROR OGG-01668 PROCESS ABENDING.
[oracle@master ogg]$
解决办法：
SQL> conn /as sysdba
Connected.
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
System altered.
重新执行加载：
[oracle@master ogg]$ ./replicat paramfile dirprm/inload.prm
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 01:27:04
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-01-28 20:48:48
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64
Node: master
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 4443
Description:
2016-01-28 20:48:48 WARNING OGG-02904 Replication of PARTIAL XML containing NCHAR/NVARCHAR/NCLOB data may cause divergence.
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-28 20:48:48 INFO OGG-03059 Operating system character set identified as UTF-8.
2016-01-28 20:48:48 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2016-01-28 20:48:48 INFO OGG-03528 The source database character set, as determined from the table definition file, is windows-936.
SPECIALRUN
END RUNTIME
USERID jyc, PASSWORD ***
EXTFILE /ogg/dirdat/ex
SOURCEDEFS /ogg/dirdef/ip.def
MAP dbo.ip, TARGET jyc.ip;
2016-01-28 20:48:49 INFO OGG-06451 Triggers will be suppressed by default.
2016-01-28 20:48:49 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 4M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
2016-01-28 20:48:49 INFO OGG-02243 Opened trail file /ogg/dirdat/ex000000 at 2016-01-28 20:48:49.400258.
2016-01-28 20:48:49 INFO OGG-03522 Setting session time zone to source database time zone 'Asia/Shanghai'.
2016-01-28 20:48:49 INFO OGG-01014 Positioning with begin time: Jan 1, 1970 12:00:00 AM, starting record time: Jan 28, 2016 8:44:51 PM at extseqno 0, extrba 1854.
2016-01-28 20:48:49 INFO OGG-03506 The source database character set, as determined from the trail file, is windows-936.
***********************************************************************
** Run Time Messages **
***********************************************************************
2016-01-28 20:48:49 INFO OGG-02243 Opened trail file /ogg/dirdat/ex000000 at 2016-01-28 20:48:49.678855.
2016-01-28 20:48:49 WARNING OGG-02761 Source definitions file, /ogg/dirdef/ip.def, is ignored because trail file /ogg/dirdat/ex000000 contains table definitions.
2016-01-28 20:48:49 INFO OGG-06505 MAP resolved (entry dbo.ip): MAP dbo.ip, TARGET jyc.ip.
2016-01-28 20:48:55 WARNING OGG-06439 No unique key is defined for table IP. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2016-01-28 20:48:55 INFO OGG-02756 The definition for table dbo.ip is obtained from the trail file.
2016-01-28 20:48:55 INFO OGG-06511 Using following columns in default map by name: ID, ONIP, OFFIP, ADDJ, ADDF, ONIP1, OFFIP1.
2016-01-28 20:48:55 INFO OGG-06510 Using the following key columns for target table JYC.IP: ID, ONIP, OFFIP, ADDJ, ADDF, ONIP1, OFFIP1.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : /ogg/dirdat/ex000000
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 78 (x004e) IO Time : 2016-01-28 20:44:51.587058
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2016-01-28 20:44:51.587058 Insert Len 78 RBA 11318542
TDR Index: 1
___________________________________________________________________
Reading /ogg/dirdat/ex000000, current RBA 11318672, 62296 records, m_file_seqno = 0, m_file_rba = 11318672
Report at 2016-01-28 20:49:03 (activity since 2016-01-28 20:48:55)
From Table dbo.ip to JYC.IP:
# inserts: 62296
# updates: 0
# deletes: 0
# discards: 0
[oracle@master ogg]$
目标端检查表记录：
SQL> select count(*) from ip;
COUNT(*)
----------
62296
3.7源端修改数据库模式
在 SQL Server 上另外执行两个步骤：确认数据库已设置为完全恢复，然后执行 adjyc 数据库的完整数据库备份。如果执行完整备份失败，将阻碍Extract 进程捕获实时数据更改。
通过右键单击 adjyc数据库，选择右键属性 并检查恢复模式的值，可以轻松检查该数据库是否处于完全恢复模式。
执行完整备份也只需几次单击即可完成。右键单击 adjyc数据库，选择任务，然后选择备份。这将启动 备份数据库 对话框。确认备份类型 配置为 Full，然后单击 OK。
确定备份完成。
3.8配置源端抽取进程(此处配置直接抽取推送到目标端)
F:\ogg>ggsci
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 (w2008x64) 1> add extract msext, tranlog, begin now
EXTRACT added.
GGSCI (w2008x64) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:00:06
GGSCI (w2008x64) 3> add rmttrail /ogg/dirdat/ms, extract msext
RMTTRAIL added.
GGSCI (w2008x64) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:00:54
GGSCI (w2008x64) 5> edit params msext
GGSCI (w2008x64) 6> view params msext
EXTRACT MSEXT
SOURCEDB demo
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.89.118, MGRPORT 7809
RMTTRAIL /ogg/dirdat/ms
TABLE dbo.ip;
GGSCI (w2008x64) 7> start msext
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT MSEXT starting
GGSCI (w2008x64) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:02:23
GGSCI (w2008x64) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:02:25
GGSCI (w2008x64) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:02:28
GGSCI (w2008x64) 11> start msext
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT MSEXT starting
GGSCI (w2008x64) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED MSEXT 00:00:00 00:02:43
GGSCI (w2008x64) 13> view report msext
***********************************************************************
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 2016-01-29 13:01:20
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 1468
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-29 13:01:20 INFO OGG-03059 Operating system character set identifie
d as GBK.
2016-01-29 13:01:20 INFO OGG-02695 ANSI SQL parameter syntax is used for pa
rameter parsing.
EXTRACT MSEXT
SOURCEDB demo
Source Context :
SourceModule : [ggdb.odbc.dbx]
SourceID : [../gglib/ggdbodbc/dbxodbc.c]
SourceFunction : [gl_check_odbc_err]
SourceLine : [1500]
ThreadBacktrace : [16] elements
: [F:\ogg\gglog.dll(??1CContextItem@@UEAA@XZ+0x2a76) [
0x000007FEE4E5E986]]
: [F:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QE
AAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x561) [0x000007FEE4E5EFD1]]
: [F:\ogg\gglog.dll(?_MSG_ERR_ODBC_OPERATION_FAILED@@Y
APEAVCMessage@@PEAVCSourceContext@@PEBD11HW4MessageDisposition@CMessageFactory@@
@Z+0x59) [0x000007FEE4DF1D89]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1a5b8) [0x0000
00013F5ABB58]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1aadb) [0x0000
00013F5AC07B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1be32) [0x0000
00013F5AD3D2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1c801) [0x0000
00013F5ADDA1]]
: [F:\ogg\extract.exe(ERCALLBACK+0x16b79) [0x000000013
F4A09D9]]
: [F:\ogg\extract.exe(ERCALLBACK+0x41f72) [0x000000013
F4CBDD2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x85c3) [0x00000
0013F599B63]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x79cb) [0x00000
0013F598F6B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x8463) [0x00000
0013F599A03]]
: [F:\ogg\extract.exe(ERCALLBACK+0x42f2b) [0x000000013
F4CCD8B]]
: [F:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitial
ized@1@@Z+0x42a) [0x000000013F711596]]
: [C:\Windows\system32\kernel32.dll(BaseThreadInitThun
k+0xd) [0x0000000077A1652D]]
: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x
21) [0x0000000077B4C521]]
2016-01-29 13:01:20 ERROR OGG-00551 Database operation failed: Couldn't conn
ect to demo. ODBC error: SQLSTATE IM002 native database error 0. [Microsoft][ODB
C 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序.
2016-01-29 13:01:20 ERROR OGG-01668 PROCESS ABENDING.
GGSCI (w2008x64) 14>
解决方法如下：
由于之前创建的用户dsn所以报上述错误，应该创建为系统DSN，如下：
GGSCI (w2008x64) 23> view report msext
***********************************************************************
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 2016-01-29 13:07:11
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 3152
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-29 13:07:11 INFO OGG-03059 Operating system character set identifie
d as GBK.
2016-01-29 13:07:11 INFO OGG-02695 ANSI SQL parameter syntax is used for pa
rameter parsing.
EXTRACT MSEXT
SOURCEDB demo
Source Context :
SourceModule : [ggdb.odbc.dbx]
SourceID : [../gglib/ggdbodbc/dbxodbc.c]
SourceFunction : [gl_check_odbc_err]
SourceLine : [1500]
ThreadBacktrace : [16] elements
: [F:\ogg\gglog.dll(??1CContextItem@@UEAA@XZ+0x2a76) [
0x000007FEE4E5E986]]
: [F:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QE
AAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x561) [0x000007FEE4E5EFD1]]
: [F:\ogg\gglog.dll(?_MSG_ERR_ODBC_OPERATION_FAILED@@Y
APEAVCMessage@@PEAVCSourceContext@@PEBD11HW4MessageDisposition@CMessageFactory@@
@Z+0x59) [0x000007FEE4DF1D89]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1a5b8) [0x0000
00013F58BB58]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1aadb) [0x0000
00013F58C07B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1be32) [0x0000
00013F58D3D2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1c801) [0x0000
00013F58DDA1]]
: [F:\ogg\extract.exe(ERCALLBACK+0x16b79) [0x000000013
F4809D9]]
: [F:\ogg\extract.exe(ERCALLBACK+0x41f72) [0x000000013
F4ABDD2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x85c3) [0x00000
0013F579B63]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x79cb) [0x00000
0013F578F6B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x8463) [0x00000
0013F579A03]]
: [F:\ogg\extract.exe(ERCALLBACK+0x42f2b) [0x000000013
F4ACD8B]]
: [F:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitial
ized@1@@Z+0x42a) [0x000000013F6F1596]]
: [C:\Windows\system32\kernel32.dll(BaseThreadInitThun
k+0xd) [0x0000000077A1652D]]
: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x
21) [0x0000000077B4C521]]
2016-01-29 13:07:12 ERROR OGG-00551 Database operation failed: Couldn't conn
ect to demo. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][
SQL Server Native Client 11.0][SQL Server]无法打开登录所请求的数据库"adjyc"。登
录失败。.
2016-01-29 13:07:12 ERROR OGG-01668 PROCESS ABENDING.
GGSCI (w2008x64) 24>
解决办法：必须采用SQLSERVER驱动，不能用native client驱动。
参考：https://community.oracle.com/thread/2553127?start=0&tstart=0
如下设置：
再次启动发现还是报错：
GGSCI (w2008x64 as jyc@DEMO) 38> view report msext
***********************************************************************
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 2016-01-29 13:16:23
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 3920
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-01-29 13:16:23 INFO OGG-03059 Operating system character set identifie
d as GBK.
2016-01-29 13:16:23 INFO OGG-02695 ANSI SQL parameter syntax is used for pa
rameter parsing.
EXTRACT MSEXT
SOURCEDB demo
Source Context :
SourceModule : [ggdb.odbc.dbx]
SourceID : [../gglib/ggdbodbc/dbxodbc.c]
SourceFunction : [gl_check_odbc_err]
SourceLine : [1500]
ThreadBacktrace : [16] elements
: [F:\ogg\gglog.dll(??1CContextItem@@UEAA@XZ+0x2a76) [
0x000007FEE4E5E986]]
: [F:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QE
AAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x561) [0x000007FEE4E5EFD1]]
: [F:\ogg\gglog.dll(?_MSG_ERR_ODBC_OPERATION_FAILED@@Y
APEAVCMessage@@PEAVCSourceContext@@PEBD11HW4MessageDisposition@CMessageFactory@@
@Z+0x59) [0x000007FEE4DF1D89]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1a5b8) [0x0000
00013FB3BB58]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1aadb) [0x0000
00013FB3C07B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1be32) [0x0000
00013FB3D3D2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x1c801) [0x0000
00013FB3DDA1]]
: [F:\ogg\extract.exe(ERCALLBACK+0x16b79) [0x000000013
FA309D9]]
: [F:\ogg\extract.exe(ERCALLBACK+0x41f72) [0x000000013
FA5BDD2]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x85c3) [0x00000
0013FB29B63]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x79cb) [0x00000
0013FB28F6B]]
: [F:\ogg\extract.exe(_ggTryDebugHook+0x8463) [0x00000
0013FB29A03]]
: [F:\ogg\extract.exe(ERCALLBACK+0x42f2b) [0x000000013
FA5CD8B]]
: [F:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitial
ized@1@@Z+0x42a) [0x000000013FCA1596]]
: [C:\Windows\system32\kernel32.dll(BaseThreadInitThun
k+0xd) [0x0000000077A1652D]]
: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x
21) [0x0000000077B4C521]]
2016-01-29 13:16:23 ERROR OGG-00551 Database operation failed: Couldn't conn
ect to demo. ODBC error: SQLSTATE 28000 native database error 18456. [Microsoft]
[ODBC SQL Server Driver][SQL Server]用户 '' 登录失败。.
2016-01-29 13:16:23 ERROR OGG-01668 PROCESS ABENDING.
解决办法：
增加userid jyc, password jyc
如下：
GGSCI (w2008x64 as jyc@DEMO) 46> view param msext
EXTRACT MSEXT
--SOURCEDB demo
sourcedb demo userid jyc, password jyc
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.89.118, MGRPORT 7809
RMTTRAIL /ogg/dirdat/ms
TABLE dbo.ip;
检查目标端收到了推送过来的队列文件：
3.9配置目标端复制进程：
[oracle@master dirdat]$ ls -l
total 11060
-rw-r-----. 1 oracle oinstall 11318672 Jan 28 20:44 ex000000
-rw-r-----. 1 oracle oinstall 1259 Jan 28 21:17 ms000000000
[oracle@master dirdat]$ cd ..
[oracle@master ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (master) 1> dblogin userid jyc, password 1234
Successfully logged into database.
GGSCI (master as jyc@db) 2> add checkpointtable jyc.chkpt
Successfully created checkpoint table jyc.chkpt.
GGSCI (master as jyc@db) 3> add replicat msrep, exttrail /ogg/dirdat/ms, checkpointtable jyc.chkpt
REPLICAT added.
GGSCI (master as jyc@db) 4> edit params msrep
GGSCI (master as jyc@db) 5> view param msrep
REPLICAT MSREP
SOURCEDEFS /ogg/dirdef/ip.def
USERID jyc, PASSWORD 1234
MAP dbo.ip, TARGET jyc.ip;
GGSCI (master as jyc@db) 9> start msrep
Sending START request to MANAGER ...
REPLICAT MSREP starting
GGSCI (master as jyc@db) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STARTING MSREP 00:00:00 00:04:27
GGSCI (master as jyc@db) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED MSREP 00:00:00 00:00:44
GGSCI (master as jyc@db) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING MSREP 00:00:00 00:00:07
GGSCI (master as jyc@db) 15> info msrep
REPLICAT MSREP Last Started 2016-01-28 21:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 4695
Log Read Checkpoint File /ogg/dirdat/ms000000000
First Record RBA 1259
3.10测试同步
源端删除数据：
delete from ip where id<</span>1000
go
select count(*) from ip
61533
目标端报错：
2016-01-28 21:34:52 ERROR OGG-01296 Error mapping from dbo.ip to JYC.IP.
解决办法：
开始怀疑表结构def需重新生成。于是在源端重新执行生成操作：
defgen paramfile F:\ogg\dirprm\defgen.prm
然后发现报错：
2016-01-29 14:27:29 ERROR OGG-00551 Database operation failed: Couldn't conn
ect to demo. ODBC error: SQLSTATE 28000 native database error 18456. [Microsoft]
[ODBC SQL Server Driver][SQL Server]用户 '' 登录失败。.
2016-01-29 14:27:29 ERROR OGG-01668 PROCESS ABENDING.
检查defgen.prm内容，需修改为：
defsfile F:\ogg\dirdef\ip.def
sourcedb demo userid jyc, password jyc
table dbo.ip;
因为之前修改过系统DSN的驱动程序为SQLSERVER了。
重新生成文件后再传输到目标端，执行启动复制进程，依然报错。
于是想通过logdump查看一下ms000000000队列内容，发现如下：
[oracle@master ogg]$ ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >open ./dirdat/ms000000000
Current LogTrail is /ogg/dirdat/ms000000000
Logdump 2 >detail on
Logdump 3 >pos 0
Reading forward from RBA 0
Logdump 4 >n
2016/01/28 21:17:20.166.000 FileHeader Len 1251 RBA 0
Name: *FileHeader*
3000 02fb 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
0005 3200 0004 2000 0000 3300 0008 02f2 50a5 9b00 | ..2... ...3.....P...
9070 3400 0020 001e 7572 693a 7732 3030 3878 3634 | .p4.. ..uri:w2008x64
3a64 7269 7665 2d46 3a6f 6767 3a4d 5345 5854 3600 | :drive-F:ogg:MSEXT6.
0019 0017 2f6f 6767 2f64 6972 6461 742f 6d73 3030 | ..../ogg/dirdat/ms00
3030 3030 3030 3037 0000 0101 3800 0004 0000 0000 | 00000007....8.......
39ff 0008 0000 0000 0000 0000 3a00 0081 1630 3030 | 9...........:....000
Logdump 5 >n
2016/01/28 21:34:44.878.000 Metadata Len 84 RBA 1259
Name:
*
DDR Version: 1
Database type: MSSQL
Character set ID: windows-936
National character set ID: UTF-16
Locale: zh_CN
Case sensitivity: 00 00 00 00 00 00 00 00 00 00 00 00 11 00 00 00
TimeZone: China Standard Time
*
Logdump 6 >n
2016/01/28 21:34:44.878.001 Metadata Len 414 RBA 1394
Name: dbo.ip
*
1)Name 2)Data Type 3)External Length 4)Fetch Offset 5)Scale 6)Level
7)Null 8)Bump if Odd 9)Internal Length 10)Binary Length 11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision 15)Low Precision 16)Elementary Item 17)Occurs 18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set 22)Character Length 23)LOB Type 24)Partial Type
*
TDR version: 1
Definition for table dbo.ip
Record Length: 722
Columns: 7
ID 134 23 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 0 -5 -1 0 0 0
onip 134 23 11 0 0 1 0 8 8 8 0 0 0 0 1 0 0 0 -5 -1 0 0 0
offip 134 23 22 0 0 1 0 8 8 8 0 0 0 0 1 0 0 0 -5 -1 0 0 0
addj 64 100 34 0 0 1 0 100 100 0 0 0 0 0 1 0 0 1 -9 1 0 0 0
addf 64 500 140 0 0 1 0 500 500 0 0 0 0 0 1 0 0 1 -9 1 0 0 0
onip1 64 32 646 0 0 1 0 32 32 0 0 0 0 0 1 0 0 1 -9 1 0 0 0
offip1 64 32 684 0 0 1 0 32 32 0 0 0 0 0 1 0 0 1 -9 1 0 0 0
End of definition
Logdump 7 >n
2016/01/28 21:34:43.074.079 Delete Len 14 RBA 1865
Name: dbo.ip (TDR Index: 1)
Before Image: Partition 12 G b
0000 000a 0000 0000 0000 0000 03e7 | ..............
Column 0 (x0000), Len 10 (x000a)
Logdump 9 >reclen 1024
Reclen set to 1024
Logdump 10 >n
2016/01/28 21:34:43.074.079 Delete Len 14 RBA 1982
Name: dbo.ip (TDR Index: 1)
Before Image: Partition 12 m
0000 000a 0000 0000 0000 0000 03e6 | ..............
Column 0 (x0000), Len 10 (x000a)
Logdump 11 >n
2016/01/28 21:34:43.074.079 Delete Len 14 RBA 2048
Name: dbo.ip (TDR Index: 1)
Before Image: Partition 12 m
0000 000a 0000 0000 0000 0000 03e5 | ..............
Column 0 (x0000), Len 10 (x000a)
Logdump 12 >n
2016/01/28 21:34:43.074.079 Delete Len 14 RBA 2114
Name: dbo.ip (TDR Index: 1)
Before Image: Partition 12 m
0000 000a 0000 0000 0000 0000 03e4 | ..............
Column 0 (x0000), Len 10 (x000a)
Logdump 13 >detail data on
Logdump 14 >n
2016/01/28 21:34:43.074.079 Delete Len 14 RBA 2180
Name: dbo.ip (TDR Index: 1)
Before Image: Partition 12 m
0000 000a 0000 0000 0000 0000 03e3 | ..............
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 03e3 | ..........
竟然只有1列，所以怀疑是只对应id主键列。
于是考虑目标端的表是不是之前没有创建主键的缘故。
检查发现目标端缺少主键：
alter table ip add primary key (ID);
再次启动解决。
GGSCI (master) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING MSREP 00:00:00 00:00:03
GGSCI (master) 10> info msrep
REPLICAT MSREP Last Started 2016-01-29 15:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 5610
Log Read Checkpoint File /ogg/dirdat/ms000000000
2016-01-28 21:34:43.120467 RBA 52274
通过测试insert,delete,update均成功同步。
测试脚本参考如下：
update ip set addf='电信 testguangzhou' where id=68066
go
insert into ip(onip,offip,addj,addf) select onip,offip,addj,addf from ip62000 where id>62500
go
select count(*) from ip
源端测试truncate table ip，报错如下：
消息 4711，级别 16，状态 1，第 1 行
无法截断表 'ip'，因为该表已为复制发布或者已启用了变更数据捕获。
处理办法：
方法1：禁用库
EXECUTE sys.sp_cdc_disable_db;
Truncate table ip
EXECUTE sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo', --源表架构
@source_name = 'ip', --源表
@role_name = 'OracleGG_626101271' --角色（将自动创建）
GO
方法2：禁用表
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'ip',
@capture_instance = N'dbo_ip'
GO
检查：EXEC sys.sp_cdc_help_change_data_capture
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo', --源表架构
@source_name = 'ip', --源表
@role_name = 'OracleGG' --角色（将自动创建）
GO
EXTRACT MSEXT
--SOURCEDB demo
sourcedb demo userid jyc, password jyc
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.89.118, MGRPORT 7809
RMTTRAIL /ogg/dirdat/ms
gettruncates
TABLE dbo.ip;
SELECT is_cdc_enabled ,
CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END
FROM sys.databases
WHERE NAME = 'adjyc'
is_cdc_enabled (无列名)
1 CDC功能启用
SELECT * FROM msdb.dbo.cdc_jobs
database_id job_type job_id maxtrans maxscans continuous pollinginterval retention threshold
5 cleanup FDA45127-C0C5-489F-A099-477C8DC87503 0 0 0 0 4320 5000
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1
name is_tracked_by_cdc
ip 1
EXEC sys.sp_cdc_help_change_data_capture
1. -- 对表禁用变更数据捕获
2. USE adjyc
3. GO
4.
5. EXEC sys.sp_cdc_disable_table
6. @source_schema = N'dbo',
7. @source_name = N'ip',
8. @capture_instance = N' OracleGG_626101271'
9. GO
10.
11.
12.-- 对数据库禁用变更数据捕获
13.USE adjyc
14.GO
15.EXECUTE sys.sp_cdc_disable_db;
16.GO
17.-- 对当前数据库启用cdc
18.USE adjyc
19.GO
20.EXECUTE sys.sp_cdc_enable_db;
21.GO
参考文档：
http://blog.sina.com.cn/s/blog_a32eff280101e1si.html
7285
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
