2015-06-22 端午节放了三天假，啥都没干 浪费了2天时间。 今天就剩下最后一天了，准备做一个放了很久的实验！真是几天不用手生，敲几个命令竟然如此生疏。不过还算顺利，经过一番折腾终于好了。
环境： os : redhat 5.5
db ：oracle 11.2.0.3
一. source 端 oracle 准备工作
1. 安装好oracle
2. 安装好ogg
3.创建测试用户
--source database
SQL> create tablespace ggs datafile '/oradata/ggs01.dbf' size 200m autoextend off;
这里的表空间不能自动扩展。ogg11g可以使用默认的user表空间，12c需要独立的表空间，否则报错。
SQL> create user ggs identified by ggs default tablespace ggs temporary tablespace temp;
SQL> create tablespace ggs datafile '/oradata/ggs01.dbf' size 200m autoextend off;
这里的表空间不能自动扩展。ogg11g可以使用默认的user表空间，12c需要独立的表空间，否则报错。
SQL> create user ggs identified by ggs default tablespace ggs temporary tablespace temp;
SQL> grant connect,resource to ggs;
SQL> grant execute on utl_file to ggs;
SQL> grant execute on utl_file to ggs;
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp; --创建用户
User created.
SQL> grant connect,resource,dba to sender;
Grant succeeded.
由于异构数据库之间不支持ddl复制，mysql也没有Oracle使用的sequence，所以数据库上不需要启用DDL
复制支持和Sequence复制支持。
二. target 端（Mysql）准备工作
2.1 安装Mysql数据库(略)
Grant succeeded.
由于异构数据库之间不支持ddl复制，mysql也没有Oracle使用的sequence，所以数据库上不需要启用DDL
复制支持和Sequence复制支持。
二. target 端（Mysql）准备工作
2.1 安装Mysql数据库(略)
2.1.1 配置Mysql参数
Mysql 的引擎很多，但是OGG只支持Innodb 的引擎，所以需要修改mysql的配置文件开启binary logging,
在/etc/my.conf 文件中添加如下内容
log-bin=/var/lib/mysql/binary-log
log-bin-index=/var/lib/mysql/binary-log.index
binlog_format=row
default-storage-engine=INNODB
说明：1.设为row使得dml语句以binary格式记入日志，任何其他日志格式（mixed或statement）将引起extract 进程停止。因为ogg支持InfoDB。
2.这里直接将Mysql默认的引擎改成INNODB了，当然也可以在创建表的时候修改。
[root@node1 ~] cat/etc/my.conf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 1
log-bin=mysql-bin
log-bin=/var/lib/mysql/binary-log
log-bin-index=/var/lib/mysql/binary-log.index
binlog_format=row
default-storage-engine=INNODB
--查看修改是否生效：
mysql>show variableslike'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
--重启mysql数据库：
[root@node1 lib] service mysqld restart
--设置成功
mysql> show variableslike'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> showbinary logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| binary-log.000001 | 120 |
+-------------------+-----------+
1 row in set (0.00 sec)
mysql> show variableslike'%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binary-log |
| log_bin_index | /var/lib/mysql/binary-log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)
mysql> show engines;
+-------------------+--------+---------------------------------------------------------------+-------------+------+----------+
| Engine | Support| Comment | Transactions| XA |Savepoints|
+-------------------+--------+---------------------------------------------------------------+-------------+------+----------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables |NO |NO |NO |
| CSV | YES | CSV storage engine |NO |NO |NO |
| MyISAM | YES | MyISAM storage engine |NO |NO |NO |
| BLACKHOLE | YES | /dev/null storage engine (anything youwriteto it disappears) |NO |NO |NO |
| MEMORY | YES | Hash based, stored in memory, usefulfortemporary tables |NO |NO |NO |
| InnoDB | DEFAULT| Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine |NO |NO |NO |
| PERFORMANCE_SCHEMA| YES | Performance Schema |NO |NO |NO |
| FEDERATED | NO | Federated MySQL storage engine |NULL |NULL |NULL |
+-------------------+--------+----------------------------------------------------------------+------------+------+----------+
9 rows in set (0.00 sec)
2.1.2创建测试数据库zw 和表 zw
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.04 sec)
mysql> createdatabase zw;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zw |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zw
Database changed
mysql> createtable zw(idintegerprimarykey,namevarchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> show variableslike'%table_type%';
Empty set (0.00 sec)
查看zw表的属性
mysql> showtable statusfrom zwlike'zw';
----------+-------------+------+-------------+------------+-------------------+----------+----------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time |Collation | Checksum | Create_options |Comment |
---------+--------+-------------+------------+-------------------+----------+----------------+-------------+
| zw | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL |2015-05-2115:53:54 |NULL | NULL | latin1_swedish_ci | NULL | | |
+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
2.2 target mysql端安装ogg配置
2.2.1 target mysql端安装ogg
这里把ogg安装到 /ggs02目录下
[root@node1/]# mkdir ggs02
[root@node1 /]# ls
apps bin boot data dev etc ggs02 home lib lib64 lost+found media misc mnt net opt proc root sbin selinux srv sys tftpboot tmp usr var
拷贝ogg for mysql 介质到/ggs目录，然后解压
[root@node1 /]# cd ggs02/
[root@node1 ggs02]# ls
121200_ggs_Linux_x64_MySQL_64bit.zip
[root@node1 ggs02]# unzip 121200_ggs_Linux_x64_MySQL_64bit.zip
Archive: 121200_ggs_Linux_x64_MySQL_64bit.zip
[root@node1 ggs02]# ll
total 503504
-rwxr-xr-x1 root root137620895 May2115:58121200_ggs_Linux_x64_MySQL_64bit.zip
-rw-rw-r--1 root root376893440 Sep25 2013 ggs_Linux_x64_MySQL_64bit.tar
-rw-r--r--1 root root 144384 Oct 7 2013 OGG_WinUnix_Rel_Notes_12.1.2.0.0.doc
-rw-r--r--1 root root 282049 Oct 7 2013 OGG_WinUnix_Rel_Notes_12.1.2.0.0.pdf
-rw-r--r--1 root root 93696 Oct 7 2013 Oracle-GoldenGate-12.1.2.0-README.doc
-rw-r--r--1 root root 23528 Oct 7 2013 Oracle-GoldenGate-12.1.2.0-README.txt
[root@node1 ggs02]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar
--进入ogg控制台，添加ogg目录
[root@node1 ggs02]# ./ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.0.017185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Linux, x64, 64bit (optimized), MySQL Enterpriseon Sep24201315:32:47
Operating system characterset identifiedas UTF-8.
Copyright (C) 1995,2013, Oracleand/or its affiliates.All rights reserved.
GGSCI (node1) 1>
GGSCI (node1) 1>create subdirs
Creating subdirectories under current directory /ggs02
Parameter files /ggs02/dirprm: alreadyexists
Report files /ggs02/dirrpt: created
Checkpoint files /ggs02/dirchk: created
Process status files /ggs02/dirpcs: created
SQL script files /ggs02/dirsql: created
Database definitions files /ggs02/dirdef: created
Extract data files /ggs02/dirdat: created
Temporary files /ggs02/dirtmp: created
Credential store files /ggs02/dircrd: created
Masterkey wallet files /ggs02/dirwlt: created
Dump files /ggs02/dirdmp: created
GGSCI (node1) 2>
2.2.2 修改参数
我们用root用户安装ogg,配置环境变量
[root@node1 ~]# vi .bash_profile
export GGHOME=/ggs02
export PATH=$PATH:$GGHOME
export LD_LIBRARY_PATH=/ggs02:$LD_LIBRARY_PATH
[root@node1 ~]# . ./.bash_profile --使环境变量生效
--测试连接
GGSCI (node1) 3> dblogin sourcedb zw@localhost:3306,userid root,password root
Successfully logged into database.
GGSCI (node1) 7>view params mgr
ERROR: PARAMS file mgr does not exist. 没有mgr进程
GGSCI (node1) 10> edit params mgr 编辑一个
port 7808
GGSCI (node1) 12>view params mgr--查看
port 7808
GGSCI (node1) 14> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (node1) 15>start mgr
Manager started.
GGSCI (node1) 16> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNIN
三、配置同步
3.1 source端创建extract进程
GGSCI (ogg02)1>addextract ext3,tranlog,begin now
EXTRACT added.
GGSCI (ogg02) 2>add exttrail/u01/ggs02/dirdat/E3,extract ext3
EXTTRAIL added.
GGSCI (ogg02) 2>view param ext3
extract ext3
userid ggs,password ggs
exttrail /u01/ggs02/dirdat/E3
table sender.zw;
--fetchoptions fetchpkupdatecols
3.2 在source 端添加 datapump进程
GGSCI (ogg02)1>addextract pump3,exttrailsource/u01/ggs02/dirdat/E3
EXTRACT added.
GGSCI (ogg02) 7>add rmttrail/ggs02/dirdat/R3,extract pump3
RMTTRAIL added.
GGSCI (ogg02) 8>view param pump3
extract pump3
rmthost 192.168.56.87,mgrport7808
rmttrail /ggs02/dirdat/R3
passthru
gettruncates
table sender.zw
GGSCI (ogg02) 11> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT3 00:00:00 00:13:28
EXTRACT STOPPED PUMP3 00:00:00 00:03:11
3.3 配置 defegn
因为这里是oracle到mysql的同步，所以要生成异构的文件，并将文件复制到target端的dirdef目录下
在Oracle数据库执行
GGSCI (ogg02) 12> edit param defgen
userid ggs,password ggs
defsfile /u01/ggs02/dirdef/oracle.def
table sender.zw;
--根据参数生成 defgen文件
--在ogg目录执行defgen命令
[oracle@ogg02 ggs02]$ pwd
/u01/ggs02
[oracle@ogg02 ggs02]$ defgen paramfile /u01/ggs02/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generatorfor Oracle
Version 12.1.2.0.017185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Linux, x64, 64bit (optimized), Oracle 11g on Sep 24 2013 18:22:24
Copyright (C) 1995,2013, Oracleand/or its affiliates.All rights reserved.
Starting at 2015-02-0416:45:36
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Mar1621:52:39 EDT2010, Release2.6.18-194.el5
Node: ogg02
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: 2726
***********************************************************************
** Runningwith the followingparameters **
***********************************************************************
userid ggs,password ***
defsfile /u01/ggs02/dirdef/oracle.def
table sender.zw;
Retrieving definition for SENDER.ZW.
Definitions generated for 1 tablein/u01/ggs02/dirdef/oracle.def.
[oracle@ogg02 dirdef]$ ls --生成了如下文件
oracle.def
把文件scp到target端的dirdef目录下
[oracle@ogg02 dirdef]$ scp oracle.def root@192.168.56.87:/ggs02/dirdef
The authenticity of host '192.168.56.87 (192.168.56.87)' can 't be established.
RSA key fingerprint is 79:53:06:e4:e5:3b:27:f5:c9:a6:a6:13:5e:0a:29:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.87 ' (RSA) to the list of known hosts.
root@192.168.56.87's password:
oracle.def 100%1094 1.1KB/s 00:00
3.4 target 端配置replicat进程
--创建checkpointtable
GGSCI (node1) 1> dblogin sourcedb zw@localhost:3306,userid root,password root
Successfully logged into database.
--创建checkpointtable
GGSCI (node1) 1> dblogin sourcedb zw@localhost:3306,userid root,password root
Successfully logged into database.
GGSCI (node1) 5> add checkpointtable zw.checkpoint
Successfully created checkpoint table zw.checkpoint.
GGSCI (node1) 6>add replicat r3,exttrail/ggs02/dirdat/R3,checkpointtable zw.checkpoint
REPLICAT added.
--修改参数
GGSCI (node1) 7>view param R3
replicat R3
dboptions host localhost,connectionport 3306
targetdb zw,userid root,password root
sourcedefs /ggs02/dirdef/oracle.def
handlecollisions
assumetargetdefs
discardfile /ggs02/dirrpt/R3.dsc,append,megabytes50
map sender.zw,target zw.zw;
四、测试同步
4.1 启动source的进程
GGSCI (ogg02) 1> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT3 00:00:00 00:33:42
EXTRACT STOPPED PUMP3 00:00:00 00:23:26
GGSCI (ogg02) 2>start ext3
Sending START request to MANAGER ...
EXTRACT EXT3 starting
GGSCI (ogg02) 3>start pump3
Sending START request to MANAGER ...
EXTRACT PUMP3 starting
GGSCI (ogg02) 11> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT3 00:00:00 00:00:04
EXTRACT RUNNING PUMP3 00:00:00 00:31:23
4.2 启动 target进程
GGSCI (node1) 16>start R3
Sending START request to MANAGER ...
REPLICAT R3 starting
GGSCI (node1) 17> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R3 00:23:04 00:00:03
4.3 开始测试
SQL> conn sender/oracle --oracle 数据库有两条数据
Connected.
SQL> select * from zw;
ID NAME
---------------
1 zw
2 oracle
mysql> select*from zw; --mysql数据库也有两条数据
+----+--------+
| id | name |
+----+--------+
| 1 | zw |
| 2 | oracle |
+----+--------+
2 rows in set (0.00 sec)
4.3.1 insert 测试
--source
conn sender/oracle
SQL> insert into zw values(3,'ctm'); --插入一条数据
1 row created.
SQL> commit;
Commit complete.
SQL> select * from zw; --此时有3条数据了
ID NAME
---------------
1 zw
2 oracle
3 ctm
--target
mysql> select*from zw;
+----+--------+
| id | name |
+----+--------+
| 1 | zw |
| 2 | oracle |
| 3 | ctm | --insert 数据同步过来啦！！
+----+--------+
3 rows in set (0.00 sec)
4.2.2 delete 测试
--source
SQL> delete from zw where id=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from zw; --此时只剩下第二和第三条啦
ID NAME
----------------
2 oracle
3 ctm
--target --可以看到mysql 也只剩下第二和第三条啦
mysql> select*from zw;
+----+--------+
| id | name |
+----+--------+
| 2 | oracle |
| 3 | ctm |
+----+--------+
2 rows in set (0.00 sec)
4.4.3 update 测试
--srouce
SQL>update zwset id=4wherename='oracle';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from zw;
ID NAME
------------------------------
4 oracle --改过来了
3 ctm
--target 我们看看mysql端
mysql> select*from zw;
+----+--------+
| id | name |
+----+--------+
| 3 | ctm |
| 4 | oracle |
+----+--------+
2 rows in set (0.00 sec)
注意：这里很可能更新出现异常，成功的原因是加了，handlecollisions 和 fetchoptions fetchpkupdatecols 参数
update会出现丢失问题，因为对于oracle来说update实际上分为2个步骤完成，先delete 再insert
对于这个问题的解决方法
对于这个问题的解决方法
在 replicat 参数中添加： handlecollisions;
在 extract参数中添加： fetchoptions fetchpkupdatecols
handlecollisions 该参数依赖于主键或唯一索引处理冲突数据，常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突，且可能导致重复记录。
注意，打开此参数则所有的数据错误不管reperror 如何配置均不再写discard文件，即所有数据冲突信息被默认规则处理，没有任何日志，因此日常
复制不建议使用该参数；可以使用的场景 为： 只需新增数据，无需复制历史数据。
在初始化完成后，一般可动态 取消：
send rep,nohandlecollisions
对于逐渐的更新操作，若在target使用handleiscollisions且update丢失，会在转换为insert该主键的操作，注意默认情况下插入的记录不完整，fetchoptions
fetchpkupdatecols 将捕获完整的redo image镜像到trail中，从而保证parmary key 的更新通过handlecollisions转换为对target的一个完整记录的插入
oracle 到mysql 这种异构，对update的支持并不好
在 extract参数中添加： fetchoptions fetchpkupdatecols
handlecollisions 该参数依赖于主键或唯一索引处理冲突数据，常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突，且可能导致重复记录。
注意，打开此参数则所有的数据错误不管reperror 如何配置均不再写discard文件，即所有数据冲突信息被默认规则处理，没有任何日志，因此日常
复制不建议使用该参数；可以使用的场景 为： 只需新增数据，无需复制历史数据。
在初始化完成后，一般可动态 取消：
send rep,nohandlecollisions
对于逐渐的更新操作，若在target使用handleiscollisions且update丢失，会在转换为insert该主键的操作，注意默认情况下插入的记录不完整，fetchoptions
fetchpkupdatecols 将捕获完整的redo image镜像到trail中，从而保证parmary key 的更新通过handlecollisions转换为对target的一个完整记录的插入
oracle 到mysql 这种异构，对update的支持并不好
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
