oracle ogg00423,OGG部署 ORACLE - SQL SERVER

最新推荐文章于 2022-01-26 14:51:27 发布

SquaringK

最新推荐文章于 2022-01-26 14:51:27 发布

阅读量617

点赞数

文章标签： oracle ogg00423

一、环境介绍

https://www.cndba.cn/Marvinn/article/2717

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

二、源端环境

2.1、创建ogg操作系统用户https://www.cndba.cn/Marvinn/article/2717

useradd -u 1003 -g oinstall -G dba ogg

passwd 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

编辑应答文件

.......................................................

####################################################################

## ##

## 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.

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

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

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 命令创建这样一个参数文件https://www.cndba.cn/Marvinn/article/2717

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

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.

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.

否则，生成定义文件时，会有警告

删除对应生成的文件，重新执行即可

[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管理进程

源端:

https://www.cndba.cn/Marvinn/article/2717

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安全卫士弹框出-下载安装微软常用运行库合集-最后问题解决

其实可以尝试直接网上下载安装微软常用运行库合集可能就可以解决问题

创建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

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])

))

四、源端环境https://www.cndba.cn/Marvinn/article/2717

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

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、验证同步https://www.cndba.cn/Marvinn/article/2717

源端：

插入数据

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

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 搭建完成…

SquaringK

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle ogg00423,OGG部署 ORACLE - SQL SERVER

一、环境介绍https://www.cndba.cn/Marvinn/article/2717OGG搭建若环境是RAC集群，只需要在其中一个节点上搭建OGG软件环境即可项目源环境目标环境操作系统 Centos 7.3 Windows server 2008 R2 Enterprise主机名 RAC2 SQLSERVER2008IP地址 172.41.176.102 172.41.176.113...
复制链接

扫一扫