一、环境描述:

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 Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (ora11g) 1>  create subdirs

Creating subdirectories under current directory /u01/ogg/11.2

Parameter 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 default tablespace oggtbs; GRANT create table to ggs; GRANT CONNECT TO ggs; GRANT ALTER ANY TABLE TO ggs; GRANT ALTER SESSION TO ggs; GRANT CREATE SESSION TO ggs; GRANT FLASHBACK ANY TABLE TO ggs; GRANT SELECT ANY DICTIONARY TO ggs; GRANT SELECT ANY TABLE TO ggs; GRANT RESOURCE TO ggs; GRANT DELETE ANY TABLE TO ggs; GRANT INSERT ANY TABLE TO ggs; GRANT UPDATE ANY TABLE TO ggs; 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> use mysql
Database changed
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> 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.00 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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 Interpreter for MySQL
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 04:34:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


GGSCI (nosql2) 1> create subdirs

Creating subdirectories under current directory /u01/ogg/11.2

Parameter 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


GGSCI (nosql2) 2>