一、环境描述:
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>
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:配置ogg异构oracle-mysql(1)基础环境配置 - Python技术站