ogg 11.2 for mysql_配置ogg异构oracle-mysql（1）基础环境配置

最新推荐文章于 2023-11-27 07:34:12 发布

戮萌

最新推荐文章于 2023-11-27 07:34:12 发布

阅读量250

点赞数

文章标签： ogg 11.2 for mysql

本文链接：https://blog.csdn.net/weixin_36382999/article/details/113266539

版权

一、环境描述：

192.168.0.164 ( Oracle ) —> 192.168.0.165 (Mysql )

版本：

操作系统：redhat5.8

Oracle： 11.2.0.3

Mysql: 5.5.37

goldgate:

11.2.0.1.3 for oracle

11.2.0.1.1 for mysql

测试用户：

在oracle的hr下面创建几张表，在mysql创建一个hr数据库，看数据能否同步过去。

二、Oracle to Mysql 需要注意的地方：

Supported data types for mysql：

CHAR

DOUBLE

TINYTEXT

VARCHAR

DATE

MEDIUMTEXT

INT

TIME

LONGTEXT

TINYINT

YEAR

BLOB

SMALL INT

DATETIME

TINYBLOB

MEDIUM INT

TIMESTAMP

MEDIUMBLOB

BIG INT

BINARY

LONGBLOB

DECIMAL

VARBINARY

ENUM

FLOAT

TEXT

BIT(M)

Oracle GoldenGate supports InnoDB storage engine for a source MySQL database

goldengate对mysql只支持innodb引擎

所以，在创建mysql端的表的时候，要指定表为innodb引擎。

create table mysql (name char(10)) engine=innodb;

当然5.5默认的存储引擎就是InnoDB。

三、Oracle端的基础配置

1.安装oracle11g略过

2.初始化ogg

将ogg压缩包(V34339-01.zip)解压到 /u01/ogg/11.2 下

[oracle@ora11g 11.2]$ ./ggsci

Oracle GoldenGate Command InterpreterforOracle

Version11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

GGSCI (ora11g)1>create subdirs

Creating subdirectories under current directory/u01/ogg/11.2Parameter files/u01/ogg/11.2/dirprm: already exists

Report files/u01/ogg/11.2/dirrpt: created

Checkpoint files/u01/ogg/11.2/dirchk: created

Process status files/u01/ogg/11.2/dirpcs: created

SQL script files/u01/ogg/11.2/dirsql: created

Database definitions files/u01/ogg/11.2/dirdef: created

Extract data files/u01/ogg/11.2/dirdat: created

Temporary files/u01/ogg/11.2/dirtmp: created

Stdout files/u01/ogg/11.2/dirout: created

3.修改oracle数据库的参数

3.1 修改数据库为归档模式

3.2 打开辅助日志

alter database add supplemental log data;

3.3 关闭回收站

alter system set recyclebin=off scope=both;

3.4 创建复制用的用户，并授权

--创建相应表空间

create tablespace oggtbs datafile '/u01/app/oracle/oradata/onimei1/oggtbs01.dbf' size 500M autoextend on;

--授权create user ggs identified by ggs defaulttablespace oggtbs;GRANT create table toggs;GRANT CONNECT TOggs;GRANT ALTER ANY TABLE TOggs;GRANT ALTER SESSION TOggs;GRANT CREATE SESSION TOggs;GRANT FLASHBACK ANY TABLE TOggs;GRANT SELECT ANY DICTIONARY TOggs;GRANT SELECT ANY TABLE TOggs;GRANT RESOURCE TOggs;GRANT DELETE ANY TABLE TOggs;GRANT INSERT ANY TABLE TOggs;GRANT UPDATE ANY TABLE TOggs;GRANT RESTRICTED SESSION TO ggs;

3.5 登陆到ogg，执行初始化

在源库上执行：

GGSCI (ora11g)2> edit params ./globals

在统计模式下输入并保存：ggschema ggs

在SQLPLUS 下去运行：

sql>@sequence.sql 根据提示输入：ggs

sqlplus/as sysdba

sql> alter system set recyclebin=off deferred scope=both; #必须，针对ddl复制

sql>@marker_setup.sql prompt: ggs

sql>@ddl_setup.sql prompt: ggs

sql>@role_setup.sql

sql>grant GGS_GGSUSER_ROLE to ggs;

SQL>@ddl_enable.sql

10g需要安装dbms_share_pool包：

sql> @?/rdbms/admin/dbmspool.sql

sql> @ddl_pin ggs;

四、Mysql端的基础配置

1.安装mysql5.5.37略过

2.给root配置密码：

mysql> usemysqlDatabasechanged

mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';

Query OK,2 rows affected (0.00sec)

Rows matched:2 Changed: 2 Warnings: 0mysql> show grants for root@localhost;+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

2 rows in set (0.00sec)

mysql> FLUSH PRIVILEGES;

Query OK,0 rows affected (0.00sec)

mysql>mysql> exit

3.因为复制需要二进制日志，所以启动mysql的时候需要启用二进制日志

mysqld_safe --user=mysql --log-bin=/tmp/binlog.001 --log-bin-index=/tmp/binlog.index --max-binlog-size=10M --binlog-do-db=mysql &

4.创建ogg的初始化目录

[mysql@nosql2 11.2]$ ./ggsci

Oracle GoldenGate Command InterpreterforMySQL

Version11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux, x64, 64bit (optimized), MySQL Enterprise on Apr23 2012 04:34:25Copyright (C)1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (nosql2)1>create subdirs