如图所示：源端采用Mysql库，目标端采用Oracle库
一、OGG安装配置（源端）
1、OGG下载
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841438
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440
[oracle@ogg ogg_ms]$ uname -a
Linux ogg 2.6.32-71.el6.i686 #1 SMP Wed Sep 1 01:26:34 EDT 2010 i686 i686 i386 GNU/Linux
|
1
2
3
4
|
[oracle@ogg ~]$ cd /u01/ogg_ms/
[oracle@ogg ogg_ms]$ ls
ggs_Linux_x86_MySQL_32bit.tar
[oracle@ogg ogg_ms]$ tar xvf ggs_Linux_x86_MySQL_32bit.tar
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[oracle@ogg ogg_ms]$ ./ggsci
Oracle GoldenGate Command Interpreter
for
MySQL
Version
11.2.
1.0.
1
OGGCORE_11
.2.1.
0.
1_PLATFORMS_120423
.0230
Linux, x86, 32bit (optimized), MySQL Enterprise
on
Apr
23
2012
04
:
29
:
30
Copyright (C)
1995
,
2012
, Oracle
and
/
or
its affiliates. All rights reserved.
GGSCI (ogg)
1
> create subdirs
Creating subdirectories under current directory /u01/ogg_ms
Parameter files /u01/ogg_ms/dirprm: already exists
Report files /u01/ogg_ms/dirrpt: created
Checkpoint files /u01/ogg_ms/dirchk: created
Process status files /u01/ogg_ms/dirpcs: created
SQL script files /u01/ogg_ms/dirsql: created
Database definitions files /u01/ogg_ms/dirdef: created
Extract data files /u01/ogg_ms/dirdat: created
Temporary files /u01/ogg_ms/dirtmp: created
Stdout files /u01/ogg_ms/dirout: created
|
二、数据库配置
源端：mysql库配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
数据库配置文件：
[root@ogg ~]# cat /etc/my.cnf
# Replication Master Server (default)
# binary logging is required
for
replication
log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
binlog_format=row
启动数据库服务
[root@ogg ~]# service mysql start
Starting MySQL
连接数据库 [ OK ]
[root@ogg ~]# mysql -h localhost -u mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end
with
;
or
\g.
Your MySQL connection id is
1
Server version:
5.6.
4
-m7-log Source distribution
Copyright (c)
2000
,
2010
, Oracle
and
/
or
its affiliates. All rights reserved.
This software comes
with
ABSOLUTELY NO WARRANTY. This is free software,
and
you are welcome to modify
and
redistribute it under the GPL v2 license
Type
'help;'
or
'\h'
for
help. Type
'\c'
to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4
rows
in
set (
0.00
sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (
0.00
sec)
创建测试表（存储引擎采用innodb）
mysql> create table test2 (id int,name char(
10
)) engine=innodb;
Query OK,
0
rows affected (
0.02
sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2 |
+----------------+
1
row
in
set (
0.00
sec)
mysql> select *
from
test2;
Empty set (
0.00
sec)
|
目标端：Oracle 库配置：
|
1
2
3
4
5
6
7
8
9
10
11
|
16
:
06
:
46
SYS@ prod >create user test identified by test ;
User created.
16
:
08
:
05
SYS@ prod >grant connect,resource to test;
Grant succeeded.
16
:
08
:
12
SYS@ prod >conn test/test
Connected.
16
:
08
:
18
TEST@ prod >create table test2 (id
int
,name varchar2(
10
));
Table created.
|
三、OGG同步配置
源端：Mysql库配置
[oracle@ogg ogg_ms]$ ./ggsci
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
Oracle GoldenGate Command Interpreter
for
MySQL
Version
11.2.
1.0.
1
OGGCORE_11
.2.1.
0.
1_PLATFORMS_120423
.0230
Linux, x86, 32bit (optimized), MySQL Enterprise
on
Apr
23
2012
04
:
29
:
30
Copyright (C)
1995
,
2012
, Oracle
and
/
or
its affiliates. All rights reserved.
用户登录（root）：
GGSCI (ogg)
2
> dblogin sourcedb test@localhost:
3306
,userid root,password oracle
Successfully logged
into
database.
配置mgr：
GGSCI (ogg)
3
> edit param mgr
port
7809
dynamicportlist
7800
-8000
autorestart extract *,waitminutes
2
,resetminutes
5
GGSCI (ogg)
4
> start mgr
Manager started.
GGSCI (ogg)
5
> info mgr
Manager is running (IP port ogg
.7809
).
GGSCI (ogg)
6
> info all
Program Status Group Lag
at
Chkpt Time Since Chkpt
MANAGER RUNNING
配置extract进程组：
GGSCI (ogg)
7
> edit param ext_1
extract ext_1
setenv (MYSQL_HOME=”/
var
/lib/mysql”)
tranlogoptions altlogdest /
var
/lib/mysql/mysql-bin.index
sourcedb test@localhost:
3306
,userid root,password oracle
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.test2;
GGSCI (ogg)
11
> add extract ext_1,tranlog,begin now
EXTRACT added.
GGSCI (ogg)
12
> add exttrail ./dirdat/e2,extract ext_1
EXTTRAIL added.
配置pump进程组：
GGSCI (ogg)
13
> edit params pump_1
extract pump_1
rmthost
192.168.
8.249
,mgrport
7809
rmttrail /u01/ogg/dirdat/e2
passthru
gettruncates
table test.test2;
GGSCI (ogg)
14
> add extract pump_1,exttrailsource ./dirdat/e2
EXTRACT added.
GGSCI (ogg)
15
> add rmttrail /u01/ogg/dirdat/e2,extract pump_1
RMTTRAIL added.
异构平台配置defgen：
GGSCI (ogg)
16
> edit params defgen
defsfile /u01/ogg_ms/dirdef/defgen.prm
sourcedb test@localhost:
3306
, userid root,password oracle
table test.test2;
[oracle@ogg ogg_ms]$ ./defgen paramfile dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator
for
MySQL
Version
11.2.
1.0.
1
OGGCORE_11
.2.1.
0.
1_PLATFORMS_120423
.0230
Linux, x86, 32bit (optimized), MySQL Enterprise
on
Apr
23
2012
04
:
59
:
19
Copyright (C)
1995
,
2012
, Oracle
and
/
or
its affiliates. All rights reserved.
Starting
at
2014
-09
-26
16
:
01
:
05
***********************************************************************
Operating System Version:
Linux
Version #
1
SMP Wed Sep
1
01
:
26
:
34
EDT
2010
, Release
2.6.
32
-71.
el6.i686
Node: ogg
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id:
2606
***********************************************************************
** Running
with
the following parameters **
***********************************************************************
defsfile /u01/ogg_ms/dirdef/defgen.prm
sourcedb test@localhost:
3306
, userid root,password ******
table test.test2;
Retrieving definition
for
test.test2
Definitions generated
for
1
table
in
/u01/ogg_ms/dirdef/defgen.prm
传送defgen文件到目标端：
[oracle@ogg ogg_ms]$ scp dirdef/defgen.prm rh6:/u01/ogg/dirdef
GGSCI (ogg)
5
> info all
Program Status Group Lag
at
Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EXT_1
00
:
00
:
00
00
:
21
:
03
EXTRACT RUNNING PUMP_1
00
:
00
:
00
00
:
00
:
06
启动extract和pump进程：
GGSCI (ogg)
6
> start extract ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (ogg)
7
> info all
Program Status Group Lag
at
Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EXT_1
00
:
00
:
00
00
:
21
:
17
EXTRACT RUNNING PUMP_1
00
:
00
:
00
00
:
00
:
09
|
extract进程启动异常（abended）！
查看日志：
GGSCI (ogg) 36> view ggsevt
2014-09-26 17:24:56 INFO OGG-01053 Oracle GoldenGate Capture for MySQL, ext_1.prm: Recovery completed for target file ./dirdat/e20
00026, at RBA 961.
2014-09-26 17:24:56 INFO OGG-01057 Oracle GoldenGate Capture for MySQL, ext_1.prm: Recovery completed for all targets.
2014-09-26 17:24:56 INFO OGG-00182 Oracle GoldenGate Capture for MySQL, ext_1.prm: VAM API running in single-threaded mode.
2014-09-26 17:24:56 INFO OGG-01515 Oracle GoldenGate Capture for MySQL, ext_1.prm: Positioning to begin time Sep 26, 2014 3:52:01
PM.
2014-09-26 17:24:56 ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, ext_1.prm: VAM function VAMInitialize returned unexpected
result: error 600 - VAM Client Report <CAUSE OF FAILURE : ERROR NO 13 - Failed to access index file : Check File PATH/EXISTENCE/PERMISSI
ONS - /var/lib/mysql/mysql-bin.index
WHEN FAILED : While initializing binary log configuration
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
2014-09-26 17:24:56 ERROR OGG-01668 Oracle GoldenGate Capture for MySQL, ext_1.prm: PROCESS ABENDING.
附录：解决方法（参考网络文档）
GoldenGate的官方文档明确表示，GoldenGate需要将MySQL的日志格式（binlog_format）设置为ROW，其他两种格式（MIXED or STATEMENT）是不支持。
########################################################################官方描述如下：
binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Any other log format (MIXED or STATEMENT) causes Extract to abend.
########################################################################
但是MySQL在版本5.1.5之前是不支持ROW和MIXED格式（MySQL 5.1.5引入ROW，5.1.8引入MIXED）
#### 官方描述如下：http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html
Support for row-based logging was added in MySQL 5.1.5. Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12,MIXED become the default logging mode; in 5.1.29, the default was changed back to STATEMENT for compatibility with MySQL 5.0.
########################################################################
目前客户的MySQL版本是5.0.6-beta版：
D:\MySQL Server 5.0\bin>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 111 to server version: 5.0.6-beta-nt-log
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.0.6-beta-nt-log|
+-------------------+
1 row in set (0.02 sec)
这个版本是不支持binlog_format=ROW，所以安装好GolenGate后，启动抽取进程时，进程abend
########################################################################下面是出错信息：
2014-09-16 16:55:16 ERROR OGG-00146 VAM function VAMRead returned unexpected result: error 600 - VAM Client Report <CAUSE OF FAILURE : STATEMENT or MIXED level logging found so abending
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>
因此如果想要采用GoldenGate来实现MySQL的数据实时同步，需要将MySQL版本升级到5.1.5以上.
四、目标端ogg配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
配置MGR
GGSCI (rh6.cuug.net)
4
> edit params mgr
port
7809
dynamicportlist
7800
-8000
autorestart extract *,waitminutes
2
,resetminutes
5
~
GGSCI (rh6.cuug.net)
5
> start mgr
Manager started.
GGSCI (rh6.cuug.net)
6
> info mgr
Manager is running (IP port rh6.cuug.net
.7809
).
配置replicat进程组：
[oracle@rh6 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter
for
Oracle
Version
11.2.
1.0.
1
OGGCORE_11
.2.1.
0.
1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g
on
Apr
23
2012
08
:
09
:
25
Copyright (C)
1995
,
2012
, Oracle
and
/
or
its affiliates. All rights reserved.
GGSCI (rh6.cuug.net)
1
>
GGSCI (rh6.cuug.net)
1
> edit param rep_1
replicat rep_1
sourcedefs /u01/ogg/dirdef/defgen.prm
userid ogg,password ogg
reperror default,discard
discardfile /u01/ogg/dirrpt/rep_1.dsc,append,megabytes
50
dynamicresolution
map test.test2, target test.test2;
GGSCI (rh6.cuug.net)
1
> add replicat rep_1,exttrail /u01/ogg/dirdat/e2
REPLICAT added.
启动mgr和replicat进程：
GGSCI (rh6.cuug.net)
3
> start mgr
Manager started.
GGSCI (rh6.cuug.net)
4
> info all
Program Status Group Lag
at
Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP_1
00
:
00
:
00
00
:
00
:
34
REPLICAT ABENDED RORA_1
00
:
00
:
00
383
:
08
:
47
GGSCI (rh6.cuug.net)
5
> start replicat rep_1
Sending START request to MANAGER ...
REPLICAT REP_1 starting
GGSCI (rh6.cuug.net)
6
> info all
Program Status Group Lag
at
Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_1
00
:
00
:
00
00
:
00
:
00
|
由于源端extract进程异常，数据不能同步，问题未解决，欢迎指导，待续...
问题解决：
更换mysql-5.5.12的版本，目前Golden Gate 版本不支持mysql-5.6
GGSCI (ogg) 1> edit param ext_1
配置/etc/my.cnf文件：
|
1
2
3
4
5
6
|
# binary logging is required
for
replication
log-bin=/var/lib/mysql/mysql-bin
log-bin-index=/var/lib/mysql/mysql-bin.index
# binary logging format - mixed recommended
#binlog_format=mixed
binlog_format=row
|
并编辑extract进程组配置文件
GGSCI (ogg) 1> edit param ext_1
extract ext_1
setenv (MYSQL_HOME=”/var/lib/mysql”)
tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
sourcedb test@localhost:3306,userid root,password oracle
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.test2;
启动extract 进程
|
1
2
3
4
5
6
7
8
|
GGSCI (ogg)
2
> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (ogg)
3
> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EXT_1
00
:
00
:
00
72
:
19
:
40
EXTRACT RUNNING PUMP_1
00
:
00
:
00
00
:
00
:
01
|
仍然失败，以下为ogg日志：
[root@ogg ogg_ms]# tail ggserr.log
|
1
2
3
4
5
6
7
8
9
10
|
2014
-
09
-
29
16
:
29
:
09
INFO OGG-
01026
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Rolling over remote file ./dirdat/e2000143.
2014
-
09
-
29
16
:
29
:
09
INFO OGG-
01053
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Recovery completed
for
target file ./dirdat/e2000144, at RBA
959
.
2014
-
09
-
29
16
:
29
:
09
INFO OGG-
01057
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Recovery completed
for
all targets.
2014
-
09
-
29
16
:
29
:
09
INFO OGG-
00182
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: VAM API running in single-threaded mode.
2014
-
09
-
29
16
:
29
:
09
INFO OGG-
01515
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Positioning to begin time Sep
26
,
2014
3
:
52
:
01
PM.
2014
-
09
-
29
16
:
29
:
09
ERROR OGG-
00146
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: VAM function VAMInitialize returned unexpected result: error
600
- VAM Client Report <CAUSE OF FAILURE : Position time is prior then earliest time available in the log : Earliest time available in the log is
2014
-
09
-
29
15
:
34
:
40
WHEN FAILED : SetInitialPosition by time stamp
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
2014
-
09
-
29
16
:
29
:
09
ERROR OGG-
01668
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: PROCESS ABENDING.
|
重新删除extract进程组，重新添加
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
bash-
4.1
$ ./ggsci
Oracle GoldenGate Command Interpreter
for
MySQL
Version
11.2
.
1.0
.
1
OGGCORE_11.
2.1
.
0
.1_PLATFORMS_120423.
0230
Linux, x86, 32bit (optimized), MySQL Enterprise on Apr
23
2012
04
:
29
:
30
Copyright (C)
1995
,
2012
, Oracle and/or its affiliates. All rights reserved.
GGSCI (ogg)
1
> delete extract ext_1
Deleted EXTRACT EXT_1.
GGSCI (ogg)
2
> add extract ext_1,tranlog,begin now
EXTRACT added.
GGSCI (ogg)
3
> add exttrail ./dirdat/e2,extract ext_1
EXTTRAIL added.
GGSCI (ogg)
4
> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (ogg)
5
> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_1
00
:
00
:
23
00
:
00
:
03
EXTRACT RUNNING PUMP_1
00
:
00
:
00
00
:
00
:
01
|
extract进程，启动成功！
查看日志：
-bash-4.1$ tail ggserr.log
|
1
2
3
4
5
6
7
8
9
|
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
00993
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: EXTRACT EXT_1 started.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01055
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Recovery initialization completed
for
target file ./dirdat/e2000155, at RBA
959
.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01478
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Output file ./dirdat/e2 is using format RELEASE
11.2
.
2014
-
09
-
29
16
:
55
:
12
WARNING OGG-
01438
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Checkpoint marked as from graceful shutdown, but records found after checkpoint in trail ./dirdat/e2. Expected EOF Seqno
0
, RBA
0
. Found Seqno
155
, RBA
959
.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01026
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Rolling over remote file ./dirdat/e2000155.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01053
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Recovery completed
for
target file ./dirdat/e2000156, at RBA
959
.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01057
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Recovery completed
for
all targets.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
00182
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: VAM API running in single-threaded mode.
2014
-
09
-
29
16
:
55
:
12
INFO OGG-
01515
Oracle GoldenGate Capture
for
MySQL, ext_1.prm: Positioning to begin time Sep
29
,
2014
4
:
54
:
48
PM.
|
数据同步，任务继续...
