CNDBA社区
OGG部署 ORACLE - SQL SERVER
一、环境介绍
OGG搭建若环境是RAC集群，只需要在其中一个节点上搭建OGG软件环境即可
实验规划
项目 源环境 目标环境
操作系统 Centos 7.3 Windows server 2008 R2 Enterprise
主机名 RAC2 SQLSERVER2008
IP地址 172.41.176.102 172.41.176.113
数据库版本 11.2.0.4 SQL SERVER 2008 R2
OGG用户 ogg 当前环境用户
OGG版本 123012_fbo_ggs_Linux_x64_shiphome.zip 123013_ggs_Windows_x64_MSSQL_64bit_CDC.zip
OGG介质下载链接：http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
二、源端环境
2.1、创建ogg操作系统用户
useradd -u 1003 -g oinstall -G dba ogg
passwd ogg
2.2、修改OGG用户环境变量
切换到ogg用户
su – ogg
修改 .bash_profile，增加如下内容：设置对应的ORACLE_BASE、ORACLE_HOME、ORACLE_SID
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11.2.0
export ORACLE_SID=orcl2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/oracle/ggate
export GGATE=/ogg #ogg安装目录
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$PATH:$ORACLE_HOME/bin:$GGATE
运行下面的命令使变量生效。
source ~/.bash_profile
2.3、创建OGG安装目录，并上传介质解压
mkdir /ogg
chown -R ogg:oinstall /ogg
chmod -R 777 /ogg
[root@rac2 ogg]# ll
total 330800
-rw-r--r-- 1 root root 338735172 Apr 4 11:49 123012_fbo_ggs_Linux_x64_shiphome.zip
[root@rac2 ogg]# chown ogg:oinstall 123012_fbo_ggs_Linux_x64_shiphome.zip
[root@rac2 ogg]# ll
total 330800
-rw-r--r-- 1 ogg oinstall 338735172 Apr 4 11:49 123012_fbo_ggs_Linux_x64_shiphome.zip
[ogg@rac2 ogg]$ unzip 123012_fbo_ggs_Linux_x64_shiphome.zip
[ogg@rac2 ogg]$ ls
123012_fbo_ggs_Linux_x64_shiphome.zip fbo_ggs_Linux_x64_shiphome OGG-12.3.0.1-README.txt OGG_WinUnix_Rel_Notes_12.3.0.1.pdf
编辑应答文件
.......................................................
####################################################################
## Copyright(c) Oracle Corporation 2017. All rights reserved. ##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## can help to populate the variables with the appropriate ##
## values. ##
## ##
## IMPORTANT NOTE: This file should be secured to have read ##
## permission only by the oracle user or an administrator who ##
## own this installation to protect any sensitive input values. ##
## ##
###################################################################
#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
################################################################################
## ##
## Oracle GoldenGate installation option and details ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/ogg
#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=false
#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=
#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=
################################################################################
## ##
## Specify details to Create inventory for Oracle installs ##
## Required only for the first Oracle product install on a system. ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=
#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=
..........................................................
静默安装
[ogg@rac2 Disk1]$ ./runInstaller -silent -responseFile /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 6382 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1969 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-04-04_05-08-29PM. Please wait ...[ogg@rac2 Disk1]$ [WARNING] [INS-75003] The specified directory /ogg is not empty.
CAUSE: The directory specified /ogg contains files.
ACTION: Clean up the specified directory or enter a new directory location.
[WARNING] [INS-75017] Cluster detected: Unable to determine if the specified software location is shared.
It is recommended to install Oracle GoldenGate entirely on shared storage.
CAUSE: One or more cluster nodes may be unreachable or the specified software location may not be accessible from one or more nodes.
ACTION: Specify the software location on a shared storage.
You can find the log of this install session at:
/g01/oraInventory/logs/installActions2018-04-04_05-08-29PM.log
WARNING:OUI-10030:You have specified a non-empty directory to install this product. It is recommended to specify either an empty or a non-existent directory. You may, however, choose to ignore this message if the directory contains Operating System generated files or subdirectories like lost+found.
Do you want to proceed with installation in this Oracle Home?
The installation of Oracle GoldenGate Core was successful.
Please check '/g01/oraInventory/logs/silentInstall2018-04-04_05-08-29PM.log' for more details.
Successfully Setup Software.
2.4、登录OGG测试
[ogg@rac2 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 8 2017 21:13:00
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (rac2) 1>
GGSCI (rac2) 1>
GGSCI (rac2) 1>
2.5、创建OGG表空间
ORACLE建议使用单独的表空间存放OGG数据，表空间大小50M就可以，但是最好设置数据文件的自动扩展。
SQL> create tablespace ogg datafile '+DATA/orcl/datafile/ogg01.dbf' size 50M autoextend on;
Tablespace created.
2.6、创建OGG用户,并授权
SQL> create user ogg identified by ogg default tablespace ogg;
User created
OGG用户需要以下权限，也有人为了省事，直接给OGG用户DBA权限
grant CONNECT, RESOURCE to ogg;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;
grant ALTER ANY TABLE to ogg;
grant FLASHBACK ANY TABLE to ogg;
grant EXECUTE on DBMS_FLASHBACK to ogg;
grant insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;
不同的需求，OGG用户需要的权限也不一样，上面的权限基本可以支持大多数情况的数据复制，如上面的权限不足，需根据实际需求授权
2.7、打开数据库附加日志以及force log
SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME OPEN_MODE FOR SUPPLEME
--------- -------------------- --- --------
ORCL READ WRITE NO NO
上面显示数据库的附加日志和force log都没有开启，使用下面的命令开启。
SQL> alter database force logging;
Database altered.
SQL> alter database add SUPPLEMENTAL log data;
Database altered.
SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME OPEN_MODE FOR SUPPLEME
--------- -------------------- --- --------
ORCL READ WRITE YES YES
2.8、运行OGG支持DDL脚本
如果要让OGG支持DDL操作，还需要额外运行几个脚本，这些脚本是OGG带的而不是ORACLE带的，在OGG的安装目录都可以找到，为此需要cd到指定目录下，直接运行脚本，SQL SERVER OGG未有这些脚本
oracle@rac2:/home/oracle> cd /ogg
oracle@rac2:/ogg>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 8 11:35:42 2018
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>@ddl_enable.sql
2.9、创建OGG工作管理目录
GGSCI (rac2) 1> create subdirs
Creating subdirectories under current directory /ogg
Creating subdirectories under current directory /ogg
Parameter files /ogg/dirprm: created
Report files /ogg/dirrpt: created
Checkpoint files /ogg/dirchk: created
Process status files /ogg/dirpcs: created
SQL script files /ogg/dirsql: created
Database definitions files /ogg/dirdef: created
Extract data files /ogg/dirdat: created
Temporary files /ogg/dirtmp: created
Stdout files /ogg/dirout: created
2.10、创建测试数据表
1、
SQL> create table marvin.marvin (
2 id number not null,
3 first_name varchar2(50) not null,
4 last_name varchar2(50) not null,
5 constraint emp_pk primary key(id)
6 )
7 /
Table created.
INSERT INTO marvin.marvin (id, first_name, last_name) VALUES (1,'Dave','Mustaine');
INSERT INTO marvin.marvin (id, first_name, last_name) VALUES (2,'Chris','Broderick');
INSERT INTO marvin.marvin (id, first_name, last_name) VALUES (3,'David','Ellefson');
INSERT INTO marvin.marvin (id, first_name, last_name) VALUES (4,'Shawn','Drover');
INSERT INTO marvin.marvin (id, first_name, last_name) VALUES (5,'Marvin','Drover')
2、
SQL> conn scott/tiger;
Connected.
SQL> create table emp_ogg as select * from emp;
Table created.
由于OGG使用，数据表上要求存在唯一键 或者 主键
SQL> alter table SCOTT.EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO);
Table altered.
否则，生成定义文件时，会有警告
2018-04-10 11:39:55 WARNING OGG-06439 No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2.11、数据类型转换，生成定义文件
因为 Oracle 和 SQL Server 中的数据类型不同，所以您必须建立数据类型转换。GoldenGate 提供了一个名为 DEFGEN 的专用工具，用于生成数据定义，当源表和目标表中的定义不同时，Oracle GoldenGate 进程将引用该专用工具。在运行 DEFGEN 之前，需要为其创建一个参数文件，指定该工具应检查哪些表以及在检查表之后存放类型定义文件的位置。可以在 GGSCI 内使用 EDIT PARAMS 命令创建这样一个参数文件
GGSCI (rac2) 2> EDIT PARAMS DEFGEN
defsfile /ogg/dirdef/marvin.def
defsfile /ogg/dirdef/scott.def
userid ogg,password ogg
table marvin.*;
table scott.emp_ogg;
注意：如果有多个表，需要每个都列出来，如果这个用户所有的表都需要同步，直接就table marvin.*;
如果没有列出来，复制进程启动会报如下错误
ERROR OGG-00423 Oracle GoldenGate Delivery for SQL Server, RPL01.prm: Could not find definition for MARVIN.T02.
ERROR OGG-01668 Oracle GoldenGate Delivery for SQL Server, RPL01.prm: PROCESS ABENDING.
生成定义文件
生成定义文件
[ogg@rac2 ogg]$ defgen paramfile /ogg/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
Linux, x64, 64bit (optimized), Oracle 11g on Dec 8 2017 07:20:42
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Starting at 2018-04-10 11:39:51
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Nov 22 16:42:41 UTC 2016, Release 3.10.0-514.el7.x86_64
Node: rac2
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: 8345
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /ogg/dirdef/marvin.def
defsfile /ogg/dirdef/scott.def
userid ogg,password ***
table marvin.*;
Expanding wildcard table specification marvin.*:
Retrieving definition for MARVIN.MARVIN.
table scott.emp_ogg;
Retrieving definition for SCOTT.EMP_OGG.
2018-04-10 11:39:55 WARNING OGG-06439 No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Definitions generated for 2 tables in /ogg/dirdef/scott.def.
由于OGG使用，数据表上要求存在唯一键 或者 主键
SQL> alter table SCOTT.EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO);
Table altered.
否则，生成定义文件时，会有警告
2018-04-10 11:39:55 WARNING OGG-06439 No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
删除对应生成的文件，重新执行即可
[ogg@rac2 dirdef]$ rm -rf /ogg/dirdef/*
[ogg@rac2 dirdef]$ defgen paramfile /ogg/dirprm/defgen.prm
将生成的定义文件marvin.def以及scott.def拷贝到sql server ogg下的dirdef目录下
2.12、添加补充日志
添加补充日志(表级TRANDATA)
添加表级的trandata可以理解为需要将哪些用户的哪些表和目标库同步，其实也是添加表级的supplemental log，但是只有上文打开的minimal supplemental log后，这个才生效
GGSCI (rac2) 1>DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (rac2 as ogg@orcl2) 9> add trandata marvin.*
2018-04-10 14:24:56 INFO OGG-15132 Logging of supplemental redo data enabled for table MARVIN.MARVIN.
2018-04-10 14:24:56 INFO OGG-15133 TRANDATA for scheduling columns has been added on table MARVIN.MARVIN.
2018-04-10 14:24:56 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table MARVIN.MARVIN.
GGSCI (rac2 as ogg@orcl2) 10> add trandata scott.emp_ogg
2018-04-10 14:25:38 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.
2018-04-10 14:25:39 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.EMP_OGG.
2018-04-10 14:25:39 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.EMP_OGG
2.13、配置MGR管理进程
源端:
GGSCI (rac2) 1> EDIT PARAMS MGR
PORT 8088
PURGEOLDEXTRACTS /ogg/dirdat,USECHECKPOINTS
目标端OGG 12.2新特性：
GGSCI (SQLSERVER2008) 3>EDIT PARAMS MGR
PORT 8088
PURGEOLDEXTRACTS /ogg/dirdat
ACCESSRULE, PROG *, IPADDR 172.41.176.102 ALLOW
注意IPADDR为源端服务器IP地址
2.14、添加抽取进程(初始化数据extract进程)
由于在创建测试表的时候，源端的测试表有数据，而目标端新建数据库没有数据，所以需要初始化目标端的数据，所谓初始化，就是让目标端的数据和源端的数据在这个时间点是一模一样的，所以初始化工作对于ORACLE数据库来说并不需要一定使用OGG，也可以使用EXP、EXPDP、SQLLOAD等其他工具，但是对于异构平台，使用OGG进程是无可厚非的。这里异构平台使用OGG进程进行数据初始化
查看ORACLE数据库字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII
编辑EXT进程参数文件
这里我测试写两个extract进程(可以写成一个，只需要将TABLE scott.emp_ogg放入到ext_1即可)
GGSCI (rac2 as ogg@orcl2) 11> EDIT PARAMS ext_1
EXTRACT ext_1
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
USERID ogg@172.41.176.122/orcl, PASSWORD ogg
RMTHOST 172.41.176.113, MGRPORT 8088
RMTTASK REPLICAT, GROUP1 RINI_1
DYNAMICRESOLUTION
GETTRUNCATES
TABLE marvin.*;
GGSCI (rac2 as ogg@orcl2) 11> EDIT PARAMS ext_2
EXTRACT ext_2
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
USERID ogg@172.41.176.122/orcl, PASSWORD ogg
RMTHOST 172.41.176.113, MGRPORT 8088
RMTTASK REPLICAT, GROUP2 RINI_2
DYNAMICRESOLUTION
GETTRUNCATES
TABLE scott.emp_ogg;
添加EXT抽取进程
GGSCI (rac2 as ogg@orcl2) 15> ADD EXTRACT ext_1, SOURCEISTABLE
EXTRACT added.
GGSCI (rac2 as ogg@orcl2) 16> ADD EXTRACT ext_2, SOURCEISTABLE
EXTRACT added.
查看EXT进程状态
GGSCI (rac2 as ogg@orcl2) 17> INFO EXTRACT *, TASKS
EXTRACT EXT_1 Initialized 2018-04-10 14:43 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
EXTRACT EXT_2 Initialized 2018-04-10 14:43 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
由于只是编辑添加了捕获进程EXT，还没有启动这个进程，所以现在的状态是STOPPED状态
三、目标端环境
3.1、介质上传以及解压
创建目录存放介质并解压
打开cmd,cd到ogg解压目录
cd c:/ogg
执行ggsci
c:/ogg>ggsci
3.2、OGG运行测试并创建工作目录
起初报错运行计算机缺失msvcr100.dll文件，尝试各种方法后，通过安装360安全卫士-人工服务-缺少*.dll文件修复功能多次修复重启 还是报错-然后问题验证未解决-
使用360系统急救箱全盘扫描-360安全卫士弹框出-下载安装微软常用运行库合集-最后问题解决
或者WINDOWS Server 服务器下载安装vcredist_x64补丁包
参考链接地址：https://www.microsoft.com/en-us/download/details.aspx?id=40784
其实可以尝试直接网上下载安装 微软常用运行库合集 可能就可以解决问题
创建OGG工作目录
GGSCI (SQLSERVER2008) 1> CREATE SUBDIRS
Creating subdirectories under current directory C:/GG
Parameter files C:/GG/dirprm: created
Report files C:/GG/dirrpt: created
Checkpoint files C:/GG/dirchk: created
Process status files C:/GG/dirpcs: created
SQL script files C:/GG/dirsql: created
Database definitions files C:/GG/dirdef: created
Extract data files C:/GG/dirdat: created
Temporary files C:/GG/dirtmp: created
Veridata files C:/GG/dirver: created
Veridata Lock files C:/GG/dirver/lock: created
Veridata Out-Of-Sync files C:/GG/dirver/oos: created
Veridata Out-Of-Sync XML files C:/GG/dirver/oosxml: created
Veridata Parameter files C:/GG/dirver/params: created
Veridata Report files C:/GG/dirver/report: created
Veridata Status files C:/GG/dirver/status: created
Veridata Trace files C:/GG/dirver/trace: created
Stdout files C:/GG/dirout: created
GGSCI (MSSQL) 2> EXIT
根据官方文档，GGSCI支持每个Oracle GoldenGate实例最多 300 个并发的 Extract 和 Replicat 进程。不过，有一个进程负责控制其他进程；这个进程被称作 Manager 进程。虽然您可以手动运行此进程，但最好将其安装为服务，否则当启动该进程的用户注销时，该进程将停止。
要将 Manager 进程添加为 Windows 服务，请在 GoldenGate 安装目录中执行 INSTALL ADDSERVICE 命令。
CMD命令窗口下执行
C:/OGG>INSTALL ADDSERVICE
Service 'GGSMGR' created.
Install program terminated normally.
至此，WinDows 平台下安装OGG基本完成.
3.3、创建新的数据库
3.3.1、创建目标数据库
下一步是在 SQL Server 中新建一个数据库并用一些示例数据进行填充。该数据库名为EMP。可以通过启动 SQL Server Management Studio，右键单击 Databases 并选择 New Database 来创建该数据库
在 Database name 域中键入 EMP，然后单击 OK，其他选项保留默认值。
3.3.2、OGG链接数据库
现在，为使 Oracle GoldenGate 能够访问 EMP 数据库，必须为其创建一个 ODBC 数据源。转到计算机 控制面板 ->系统安全 -> 管理工具 -> 数据源(ODBC)，添加一个新的 系统DSN。选择 SQL Server Native Client 1.0作为数据库驱动程序并将该数据源命名为 Oracle。将来源指向本地 SQL Server (MSSQL) 并填写登录凭证并测试，数据源摘要应类似如下所示：
3.4、配置初始化REPLICAT进程
GGSCI (SQLSERVER2008) 1>DBLOGIN SOURCEDB Oracle USERID sa password sa123456
编辑配置文件
GGSCI (SQLSERVER2008 as sa@ORACLE) 2>EDIT PARAMS RINI_1
REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
ASSUMETARGETDEFS
TARGETDB Oracle USERID sa, PASSWORD sa123456
DISCARDFILE /ogg/dirrpt/RINIaa.dsc, PURGE
MAP marvin.*, TARGET mvschema.marvin;
GGSCI (SQLSERVER2008 as sa@ORACLE) 3>EDIT PARAMS RINI_2
REPLICAT RINI_2
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
ASSUMETARGETDEFS
TARGETDB Oracle USERID sa, PASSWORD sa123456
DISCARDFILE /ogg/dirrpt/RINIaaa.dsc, PURGE
MAP scott.emp_ogg, TARGET mvschema.emp_ogg;
添加REP进程
GGSCI (SQLSERVER2008 as sa@ORACLE) 4>ADD replicat RINI_1,specialrun
GGSCI (SQLSERVER2008 as sa@ORACLE) 5>ADD replicat RINI_2,specialrun
启动MGR进程
GGSCI (SQLSERVER2008 as sa@ORACLE) 5>START MGR
若报错，查看报错日志
命令 VIEW REPORT MGR
3.5、数据库中创建对应表结构的空表
USE EMP
GO
CREATE TABLE [MVSCHEMA].[EMP_OGG]
( [EMPNO] numeric(4,0),
[ENAME] VARCHAR(10),
[JOB] VARCHAR(9),
[MGR] numeric(4,0),
[HIREDATE] DATE,
[SAL] numeric(7,2),
[COMM] numeric(7,2),
[DEPTNO]numeric(2,0),
CONSTRAINT "PK_EMPNO_OGG" PRIMARY KEY ([EMPNO]))
CREATE TABLE [MVSCHEMA].[MARVIN]
( [id] int not null,
[first_name] varchar(50) not null,
[last_name] varchar(50) not null,
constraint [emp_pk] primary key clustered ([id])
))
四、源端环境
4.1、初始化数据
配置好目标端的应用进程RINI_1以及RINI_2后，就可以启动源端的捕获进程进行捕获数据了，而目标端的应用进程RINI_1以及RINI_2不需要手动去启动，也就是说目标端RINI_1以及RINI_2进程不需要管。
GGSCI (rac2 as ogg@orcl2) 14> START EXTRACT ext_1
Sending START request to MANAGER …
EXTRACT ext_1 starting
GGSCI (rac2 as ogg@orcl2) 14> START EXTRACT ext_2
Sending START request to MANAGER …
EXTRACT ext_2 starting
启动源端的捕获进程ext_1以及ext_2后，正常情况下(如果配置没问题)，源端的数据已经传送到目标端了，可以通过VIEW命令查看源端捕获进程ext_1以及ext_2的工作状态。
GGSCI (rac2 as ogg@orcl2) 11> VIEW REPORT ext_1
–在最后部分会看到
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2018-04-10 17:30:24 (activity since 2018-04-10 17:30:12)
Output to RINI_1:
From Table MARVIN.MARVIN:
# inserts: 5
# updates: 0
# deletes: 0
# discards: 0
REDO Log Statistics
Bytes parsed 0
Bytes output 954
GGSCI (rac2 as ogg@orcl2) 11> VIEW REPORT ext_2
输出省略......
如果配置正确，会看到上面的日志，日志会告诉你在目标端MVSCHEMA.MARVIN表插入5条数据以及MVSCHEMA.EMP_OGG表插入14行数据
如果在上面日志的最后部分出现ERROR，就需要去检查OGG的安装目录下的ggserr.log日志，这个日志相当于数据库的告警日志
4.2、目标端验证初始化数据
SQL SERVER EMP数据库中执行
USE EMP
GO
select * from MVSCHEMA.MARVIN
select * from MVSCHEMA.EMP_OGG
输出截图省略，初始化结果是成功的
初始化之后，上文提到的初始化进程（EXT_1和EXT_2、RINI_1以及RINI_2）自动停止，因为通常情况下初始化数据工作只会做一次。可以通过INFO命令查看进程的状态
源端：
GGSCI (rac2 as ogg@orcl2) 9> INFO EXTRACT ext_1
EXTRACT EXT_1 Last Started 2018-04-10 17:30 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table MARVIN.MARVIN
2018-04-10 17:30:24 Record 5
Task SOURCEISTABLE
GGSCI (rac2 as ogg@orcl2) 10> INFO EXTRACT ext_2
EXTRACT EXT_2 Last Started 2018-04-10 17:30 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SCOTT.EMP_OGG
2018-04-10 17:30:25 Record 14
Task SOURCEISTABLE
目标端：
GGSCI (SQLSERVER2008 as sa@Oracle) 50>INFO REPLICAT RINI_1
GGSCI (SQLSERVER2008 as sa@Oracle) 50>INFO REPLICAT RINI_2
状态都是STOPPED停止的
五、源端和目标配置检查点
此步骤不是必须的，但是为了让OGG网络中断、服务器宕机、掉电等在突发情况也能正确断点续传，ORACLE建议配置OGG的检查点队列。
源端：
GGSCI (rac2) 1> EDIT PARAMS ./GLOBALS
添加如下一行
CHECKPOINTTABLE ogg.ggschkpt
OGG检查点存放到OGG用户下的GGSCHKPT表中，但是还需要使用OGG用户登录数据库，创建检查点表
GGSCI (rac2) 2> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI (rac2 as ogg@orcl2) 3> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification
退出OGG，重新登录，并使用OGG用户登录数据库，
GGSCI (rac2) 1> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI (rac2 as ogg@orcl2) 2>
GGSCI (rac2 as ogg@orcl2) 2> ADD CHECKPOINTTABLE
No checkpoint table specified. Using GLOBALS specification ogg.ggschkpt...
Successfully created checkpoint table ogg.ggschkpt.
目标端：
GGSCI (SQLSERVER2008) 1> EDIT PARAMS ./GLOBALS
添加如下一行，注意这里架构用户是mvschema，因为我们SQL SERVER中没有创建OGG用户架构，当前你也可以创建OGG用户架构
CHECKPOINTTABLE mvschema.ggschkpt
OGG检查点存放到mvschema架构下的GGSCHKPT表中，但是还需要使用OGG用户登录数据库，创建检查点表
退出OGG，重新登录，并使用数据库sa用户重新登录数据库
GGSCI (SQLSERVER2008) 1>DBLOGIN SOURCEDB Oracle USERID sa password sa123456
GGSCI (SQLSERVER2008) 1>ADD CHECKPOINTTABLE
六、OGG异构同步
之前前五步主要是数据初始化准备工作，从当前开始，正式开始配置异构平台OGG同步
6.1、源端捕获进程EXTRACT
GGSCI (rac2 as ogg@orcl2) 3> EDIT PARAMS EORA_1
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
USERID ogg@172.41.176.122/orcl, PASSWORD ogg
EXTTRAIL /ogg/dirdat/mr
TRANLOGOPTIONS DBLOGREADER
TABLE marvin.*;
GGSCI (rac2 as ogg@orcl2) 5> EDIT PARAMS EORA_2
EXTRACT EORA_2
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
USERID ogg@172.41.176.122/orcl, PASSWORD ogg
EXTTRAIL /ogg/dirdat/st
TRANLOGOPTIONS DBLOGREADER
TABLE scott.EMP_OGG;
GGSCI (rac2 as ogg@orcl2) 7> ADD EXTTRAIL /ogg/dirdat/marvin, EXTRACT EORA_1, MEGABYTES 5
file portion must be two characters.
GGSCI (rac2 as ogg@orcl2) 8> DELETE EXTRACT EORA_2, TRANLOG, BEGIN NOW
Deleted EXTRACT EORA_2.
GGSCI (rac2 as ogg@orcl2) 9> DELETE EXTRACT EORA_1, TRANLOG, BEGIN NOW
Deleted EXTRACT EORA_1.
重新编辑EORA_1以及EORA_2，修改TRAIL命令格式为2个字符
GGSCI (rac2 as ogg@orcl2) 12> ADD EXTRACT EORA_1, TRANLOG, THREADS 2, BEGIN NOW
EXTRACT added.
GGSCI (rac2 as ogg@orcl2) 13> ADD EXTRACT EORA_2, TRANLOG, THREADS 2, BEGIN NOW
EXTRACT added.
GGSCI (rac2 as ogg@orcl2) 14> ADD EXTTRAIL /ogg/dirdat/mr, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added
GGSCI (rac2 as ogg@orcl2) 15> ADD EXTTRAIL /ogg/dirdat/st, EXTRACT EORA_2, MEGABYTES 5
EXTTRAIL added.
上面的两个命名告诉OGG，捕获进程从启动起开始捕获，捕获数据保存到TRAIL文件，及TRAIL文件的路径、命名格式，单个TRAIL文件最大大小
启动源端捕获进程EORA_1以及EORA_2
GGSCI (rac2 as ogg@orcl2) 50> START EXTRACT EORA_1
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
GGSCI (rac2 as ogg@orcl2) 50> START EXTRACT EORA_2
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
查看状态
GGSCI (rac2 as ogg@orcl2) 51> INFO EXTRACT EORA_1
EXTRACT EORA_1 Initialized 2018-04-10 22:26 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:32 ago)
Process ID 30479
Log Read Checkpoint Oracle Redo Logs
2018-04-10 22:26:23 Thread 1, Seqno 0, RBA 0
SCN 0.0 (0)
Log Read Checkpoint Oracle Redo Logs
2018-04-10 22:26:23 Thread 2, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (rac2 as ogg@orcl2) 52> INFO EXTRACT EORA_2
EXTRACT EORA_2 Initialized 2018-04-10 22:23 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:03:19 ago)
Log Read Checkpoint Oracle Redo Logs
2018-04-10 22:23:37 Thread 1, Seqno 0, RBA 0
SCN 0.0 (0)
Log Read Checkpoint Oracle Redo Logs
2018-04-10 22:23:37 Thread 2, Seqno 0, RBA 0
SCN 0.0 (0)
6.2、源端配置PUMP传输进程
此步骤也是非必须的，如果不配置传输进程，OGG会通过EXTRACT进程传输TRAIL队列文件，但是和检查点队列一样，为了保证断点续传ORACLE建议配置PUMP传输进程
GGSCI (rac2 as ogg@orcl2) 78> EDIT PARAMS PORA_1
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
PASSTHRU
RMTHOST 172.41.176.113, MGRPORT 8088
RMTTRAIL c:/ogg/dirdat/pm
TABLE marvin.*;
GGSCI (rac2 as ogg@orcl2) 79> EDIT PARAMS PORA_2
EXTRACT PORA_2
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
PASSTHRU
RMTHOST 172.41.176.113, MGRPORT 8088
RMTTRAIL c:/ogg/dirdat/ps
TABLE scott.EMP_OGG;
添加PUMP进程PORA_1跟2到OGG，并指定本地的TRAIL文件
GGSCI (rac2 as ogg@orcl2) 80> ADD EXTRACT PORA_1, EXTTRAILSOURCE /ogg/dirdat/mr
EXTRACT added.
GGSCI (rac2 as ogg@orcl2) 81> ADD EXTRACT PORA_2, EXTTRAILSOURCE /ogg/dirdat/st
EXTRACT added.
查看状态
GGSCI (rac2 as ogg@orcl2) 85> INFO EXTRACT PORA_1
EXTRACT PORA_1 Initialized 2018-04-10 22:50 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:02:54 ago)
Log Read Checkpoint File /ogg/dirdat/mr000000000
First Record RBA 0
GGSCI (rac2 as ogg@orcl2) 86> INFO EXTRACT PORA_2
EXTRACT PORA_2 Initialized 2018-04-10 22:50 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:02:47 ago)
Log Read Checkpoint File /ogg/dirdat/st000000000
First Record RBA 0
为PUMP进程PORA_1、2指定将本地TRAIL文件传输到目标端后保存成目标端TRAIL文件的名字
GGSCI (rac2 as ogg@orcl2) 87> ADD RMTTRAIL C:/ogg/dirdat/pm, EXTRACT PORA_1, MEGABYTES 5
RMTTRAIL added.
GGSCI (rac2 as ogg@orcl2) 88> ADD RMTTRAIL C:/ogg/dirdat/ps, EXTRACT PORA_2, MEGABYTES 5
RMTTRAIL added.
启动PUMP进程
GGSCI (rac2 as ogg@orcl2) 89> START EXTRACT PORA_1
Sending START request to MANAGER ...
EXTRACT PORA_1 starting
GGSCI (rac2 as ogg@orcl2) 93> START EXTRACT PORA_2
Sending START request to MANAGER ...
EXTRACT PORA_2 starting
查看状态
GGSCI (rac2 as ogg@orcl2) 24> INFO EXTRACT PORA_1
EXTRACT PORA_1 Last Started 2018-04-11 09:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 17436
Log Read Checkpoint File /ogg/dirdat/mr000000000
First Record RBA 1368
GGSCI (rac2 as ogg@orcl2) 25> INFO EXTRACT PORA_2
EXTRACT PORA_2 Last Started 2018-04-11 09:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 17527
Log Read Checkpoint File /ogg/dirdat/st000000000
First Record RBA 1368
此时在目标端C:/ogg/dirdat/目录下，就会看到由源端PUMP进程PORA_1、2传输过来的TRAIL文件
6.3、目标端配置同步REPLICAT进程
GGSCI (SQLSERVER2008) 1>EDIT PARAMS RORA_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
TARGETDB Oracle USERID sa, PASSWORD sa123456
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE /ogg/dirrpt/RORA_pm.DSC, PURGE
MAP marvin.*, TARGET mvschema.*;
GGSCI (SQLSERVER2008) 2>EDIT PARAMS RORA_2
REPLICAT RORA_2
SETENV (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
TARGETDB Oracle USERID sa, PASSWORD sa123456
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE /ogg/dirrpt/RORA_ps.DSC, PURGE
MAP scott.emp_ogg, TARGET mvschema.emp_ogg;
GGSCI (SQLSERVER2008) 3>ADD REPLICAT RORA_1, exttrail c:/ogg/dirdat/pm,CHECKPOINTTABLE mvschema.ggschkpt
GGSCI (SQLSERVER2008) 4>ADD REPLICAT RORA_2, exttrail c:/ogg/dirdat/ps,CHECKPOINTTABLE mvschema.ggschkpt
开启同步复制进程
GGSCI (SQLSERVER2008) 4>START REPLICAT RORA_1
GGSCI (SQLSERVER2008) 4>START REPLICAT RORA_2
查看状态是否运行RUNNING状态
GGSCI (SQLSERVER2008) 6>INFO REPLICAT RORA_1
GGSCI (SQLSERVER2008) 7>INFO REPLICAT RORA_2
6.4、验证同步
源端：
插入数据
INSERT INTO MARVIN.MARVIN VALUES(6,'Marvinn','Marvinn')
INSERT INTO SCOTT.EMP_OGG VALUES(15,'Marvinn','Marvinn',1,sysdate,12000,1,3);
COMMIT;
目标端：
查询
use EMP
go
select * from MVSCHEMA.MARVIN
select * from MVSCHEMA.EMP_OGG
查询结果省略...是同步的.
源端：
新增表
create table marvin.marvinn (
id number not null,
first_name varchar2(50) not null,
last_name varchar2(50) not null,
constraint emp_pk1 primary key (id)
)
SQL> INSERT INTO MARVIN.MARVINN VALUES(1,'Marvinn','Marvinn');
1 row created.
SQL> commit;
Commit complete.
GGSCI (rac2 as ogg@orcl2) 31> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:05
EXTRACT RUNNING EORA_2 00:00:01 00:00:10
EXTRACT RUNNING PORA_1 00:00:00 00:00:01
EXTRACT RUNNING PORA_2 00:00:00 00:00:02
目标端：
GGSCI (SQLSERVER2008 as sa@Oracle) 3> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RORA_1 00:00:00 00:00:05
REPLICAT RUNNING RORA_2 00:00:01 00:00:10
同步应用进程RORA_1状态ABENDED
查看原因
GGSCI (SQLSERVER2008 as sa@Oracle) 4>VIEW REPLICAT EORA_1
报错原因：
ERROR OGG-00199 Table mvschema.MARVINN does not exits in target database
数据库中创建空表
CREATE TABLE [MVSCHEMA].[MARVINN]
( [id] int not null,
[first_name] varchar(50) not null,
[last_name] varchar(50) not null,
constraint [emp_pk1] primary key clustered ([id])
)
再次开启REP进程
GGSCI (SQLSERVER2008 as sa@Oracle) 4>START REPLICAT RORA_1
GGSCI (SQLSERVER2008 as sa@Oracle) 5> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA_1 00:00:00 00:00:05
REPLICAT RUNNING RORA_2 00:00:01 00:00:10
查询数据库
select * from mvshema.marvinn
数据表同步正常
注意当前，我同步的使用marvin.*,这种情况下源端，一有表创建，异构目标端可能会ABENDED，需要目标端创建对应的表结构表.
至此…OGG异构平台 ORACLE - SQL SERVER 搭建完成…
版权声明：本文为博主原创文章，未经博主允许不得转载。
