Oracle GoldenGate 之--异构平台同步（Mysql到Oracle）

电玩女神 2022-08-17 15:19 288阅读 0赞

Oracle GoldenGate 异构平台同步（Mysql到Oracle）

wKiom1QlL0ODAhNWAAFaapV-AeY413.jpg

如图所示：源端采用Mysql库，目标端采用Oracle库

一、OGG安装配置（源端）

1、OGG下载

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841438

wKiom1QlMAaxj0tFAAN-tUXU3d8212.jpg

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440

wKiom1QlL-jg9cRiAALo7IeF6zA121.jpg

[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. [oracle@ogg ~]$ cd /u01/ogg_ms/
  2. [oracle@ogg ogg_ms]$ ls
  3. ggs_Linux_x86_MySQL_32bit.tar
  4. [oracle@ogg ogg_ms]$ tar xvf ggs_Linux_x86_MySQL_32bit.tar
  6. [oracle@ogg ogg_ms]$ ./ggsci
  7. Oracle GoldenGate Command Interpreter for MySQL
  8. Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
  9. Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30
  10. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
  11. GGSCI (ogg) 1> create subdirs
  12. Creating subdirectories under current directory /u01/ogg_ms
  13. Parameter files                /u01/ogg_ms/dirprm: already exists
  14. Report files                   /u01/ogg_ms/dirrpt: created
  15. Checkpoint files               /u01/ogg_ms/dirchk: created
  16. Process status files           /u01/ogg_ms/dirpcs: created
  17. SQL script files               /u01/ogg_ms/dirsql: created
  18. Database definitions files     /u01/ogg_ms/dirdef: created
  19. Extract data files             /u01/ogg_ms/dirdat: created
  20. Temporary files                /u01/ogg_ms/dirtmp: created
  21. Stdout files                   /u01/ogg_ms/dirout: created

二、数据库配置

源端：mysql库配置

  1. 数据库配置文件：
  2. [root@ogg ~]# cat /etc/my.cnf
  3. # Replication Master Server (default)
  4. # binary logging is required for replication
  5. log-bin=mysql-bin
  7. # binary logging format - mixed recommended
  8. #binlog_format=mixed
  9.   binlog_format=row
  11. 启动数据库服务
  12. [root@ogg ~]# service mysql start
  13. Starting MySQL  
  15. 连接数据库                                           [  OK  ]
  16. [root@ogg ~]# mysql -h localhost -u mysql -p
  17. Enter password:
  18. Welcome to the MySQL monitor.  Commands end with ; or \g.
  19. Your MySQL connection id is 1
  20. Server version: 5.6.4-m7-log Source distribution
  21. Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
  22. This software comes with ABSOLUTELY NO WARRANTY. This is free software,
  23. and you are welcome to modify and redistribute it under the GPL v2 license
  24. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  25. mysql> show databases;
  26. +--------------------+
  27. | Database           |
  28. +--------------------+
  29. | information_schema |
  30. | mysql              |
  31. | performance_schema |
  32. | test               |
  33. +--------------------+
  34. 4 rows in set (0.00 sec)
  35. mysql> use test;
  36. Database changed
  37. mysql> show tables;
  38. Empty set (0.00 sec)
  40. 创建测试表（存储引擎采用innodb）
  41. mysql> create table test2 (id int,name char(10)) engine=innodb;
  42. Query OK, 0 rows affected (0.02 sec)
  43. mysql> show tables;
  44. +----------------+
  45. | Tables_in_test |
  46. +----------------+
  47. | test2          |
  48. +----------------+
  49. 1 row in set (0.00 sec)
  50. mysql> select * from test2;
  51. Empty set (0.00 sec)

目标端：Oracle 库配置：

[java] view plain copy print ?

  1. 16:06:46 SYS@ prod >create user test identified by test ;
  2. User created.
  4. 16:08:05 SYS@ prod >grant connect,resource to test;
  5. Grant succeeded.
  7. 16:08:12 SYS@ prod >conn test/test
  8. Connected.
  10. 16:08:18 TEST@ prod >create table test2 (id int,name varchar2(10));
  11. Table created.

三、OGG同步配置

源端：Mysql库配置

[oracle@ogg ogg_ms]$ ./ggsci

  1. Oracle GoldenGate Command Interpreter for MySQL
  2. Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
  3. Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30
  4. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
  5. 用户登录（root）：
  6. GGSCI (ogg) 2> dblogin sourcedb test@localhost:3306,userid root,password oracle
  7. Successfully logged into database.
  9. 配置mgr：
  10. GGSCI (ogg) 3> edit param mgr
  11. port 7809
  12. dynamicportlist 7800-8000
  13. autorestart extract *,waitminutes 2,resetminutes 5
  15. GGSCI (ogg) 4> start mgr
  16. Manager started.
  17. GGSCI (ogg) 5> info mgr
  18. Manager is running (IP port ogg.7809).
  20. GGSCI (ogg) 6> info all
  21. Program     Status      Group       Lag at Chkpt  Time Since Chkpt
  22. MANAGER     RUNNING
  24. 配置extract进程组：
  25. GGSCI (ogg) 7> edit param ext_1
  26. extract ext_1
  27. setenv (MYSQL_HOME=”/var/lib/mysql”)
  28. tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
  29. sourcedb test@localhost:3306,userid root,password oracle
  30. exttrail ./dirdat/e2
  31. dynamicresolution
  32. gettruncates
  33. table test.test2;
  35. GGSCI (ogg) 11> add extract ext_1,tranlog,begin now
  36. EXTRACT added.
  37. GGSCI (ogg) 12> add exttrail ./dirdat/e2,extract ext_1
  38. EXTTRAIL added.
  40. 配置pump进程组：
  41. GGSCI (ogg) 13> edit params pump_1
  42. extract pump_1
  43. rmthost 192.168.8.249,mgrport 7809
  44. rmttrail /u01/ogg/dirdat/e2
  45. passthru
  46. gettruncates
  47. table test.test2;
  49. GGSCI (ogg) 14> add extract pump_1,exttrailsource ./dirdat/e2
  50. EXTRACT added.
  52. GGSCI (ogg) 15> add rmttrail  /u01/ogg/dirdat/e2,extract pump_1
  53. RMTTRAIL added.
  55. 异构平台配置defgen：
  57. GGSCI (ogg) 16> edit params defgen
  58. defsfile /u01/ogg_ms/dirdef/defgen.prm
  59. sourcedb test@localhost:3306, userid root,password oracle
  60. table test.test2;
  62. [oracle@ogg ogg_ms]$ ./defgen paramfile dirprm/defgen.prm
  63. ***********************************************************************
  64.         Oracle GoldenGate Table Definition Generator for MySQL
  65.       Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
  66.  Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:59:19
  67. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
  68.                     Starting at 2014-09-26 16:01:05
  69. ***********************************************************************
  70. Operating System Version:
  71. Linux
  72. Version #1 SMP Wed Sep 1 01:26:34 EDT 2010, Release 2.6.32-71.el6.i686
  73. Node: ogg
  74. Machine: i686
  75.                          soft limit   hard limit
  76. Address Space Size   :    unlimited    unlimited
  77. Heap Size            :    unlimited    unlimited
  78. File Size            :    unlimited    unlimited
  79. CPU Time             :    unlimited    unlimited
  80. Process id: 2606
  81. ***********************************************************************
  82. **            Running with the following parameters                  **
  83. ***********************************************************************
  84. defsfile /u01/ogg_ms/dirdef/defgen.prm
  85. sourcedb test@localhost:3306, userid root,password ******
  86. table test.test2;
  87. Retrieving definition for test.test2
  88. Definitions generated for 1 table in /u01/ogg_ms/dirdef/defgen.prm
  90. 传送defgen文件到目标端：
  91. [oracle@ogg ogg_ms]$ scp dirdef/defgen.prm rh6:/u01/ogg/dirdef
  92. GGSCI (ogg) 5> info all
  93. Program     Status      Group       Lag at Chkpt  Time Since Chkpt
  94. MANAGER     RUNNING
  95. EXTRACT     ABENDED     EXT_1       00:00:00      00:21:03
  96. EXTRACT     RUNNING     PUMP_1      00:00:00      00:00:06
  98. 启动extract和pump进程：
  99. GGSCI (ogg) 6> start extract ext_1
  100. Sending START request to MANAGER ...
  101. EXTRACT EXT_1 starting
  102. GGSCI (ogg) 7> info all
  103. Program     Status      Group       Lag at Chkpt  Time Since Chkpt
  104. MANAGER     RUNNING
  105. EXTRACT     ABENDED     EXT_1       00:00:00      00:21:17
  106. 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. 配置MGR
  2. GGSCI (rh6.cuug.net) 4> edit params mgr
  3. port 7809
  4. dynamicportlist 7800-8000
  5. autorestart extract *,waitminutes 2,resetminutes 5
  6. ~
  7. GGSCI (rh6.cuug.net) 5> start mgr
  8. Manager started.
  9. GGSCI (rh6.cuug.net) 6> info mgr
  10. Manager is running (IP port rh6.cuug.net.7809).
  12. 配置replicat进程组：
  13. [oracle@rh6 ogg]$ ./ggsci
  14. Oracle GoldenGate Command Interpreter for Oracle
  15. Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
  16. Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
  17. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
  18. GGSCI (rh6.cuug.net) 1>
  19. GGSCI (rh6.cuug.net) 1> edit param rep_1
  20. replicat rep_1
  21. sourcedefs /u01/ogg/dirdef/defgen.prm
  22. userid ogg,password ogg
  23. reperror default,discard
  24. discardfile /u01/ogg/dirrpt/rep_1.dsc,append,megabytes 50
  25. dynamicresolution
  26. map test.test2, target test.test2;
  28. GGSCI (rh6.cuug.net) 1>  add replicat rep_1,exttrail /u01/ogg/dirdat/e2
  29. REPLICAT added.
  31. 启动mgr和replicat进程：
  32. GGSCI (rh6.cuug.net) 3> start mgr
  33. Manager started.
  34. GGSCI (rh6.cuug.net) 4> info all
  35. Program     Status      Group       Lag at Chkpt  Time Since Chkpt
  36. MANAGER     RUNNING
  37. REPLICAT    STOPPED     REP_1       00:00:00      00:00:34
  38. REPLICAT    ABENDED     RORA_1      00:00:00      383:08:47
  40. GGSCI (rh6.cuug.net) 5> start replicat rep_1
  41. Sending START request to MANAGER ...
  42. REPLICAT REP_1 starting
  43. GGSCI (rh6.cuug.net) 6> info all
  44. Program     Status      Group       Lag at Chkpt  Time Since Chkpt
  45. MANAGER     RUNNING
  46. 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文件：

[java] view plain copy print ?

  1. # binary logging is required for replication
  2. log-bin=/var/lib/mysql/mysql-bin
  3. log-bin-index=/var/lib/mysql/mysql-bin.index
  4. # binary logging format - mixed recommended
  5. #binlog_format=mixed
  6. 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 进程

[java] view plain copy print ?

  1. GGSCI (ogg) 2> start ext_1
  2. Sending START request to MANAGER …
  3. EXTRACT EXT_1 starting
  4. GGSCI (ogg) 3> info all
  5. Program Status Group Lag at Chkpt Time Since Chkpt
  6. MANAGER RUNNING
  7. EXTRACT ABENDED EXT_1 00:00:00 72:19:40
  8. EXTRACT RUNNING PUMP_1 00:00:00 00:00:01

仍然失败，以下为ogg日志：

[root@ogg ogg_ms]# tail ggserr.log

[java] view plain copy print ?

  1. 2014-09-29 16:29:09 INFO OGG-01026 Oracle GoldenGate Capture for MySQL, ext_1.prm: Rolling over remote file ./dirdat/e2000143.
  2. 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.
  3. 2014-09-29 16:29:09 INFO OGG-01057 Oracle GoldenGate Capture for MySQL, ext_1.prm: Recovery completed for all targets.
  4. 2014-09-29 16:29:09 INFO OGG-00182 Oracle GoldenGate Capture for MySQL, ext_1.prm: VAM API running in single-threaded mode.
  5. 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.
  6. 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
  7. WHEN FAILED : SetInitialPosition by time stamp
  8. WHERE FAILED : MySQLBinLog Reader Module
  9. CONTEXT OF FAILURE : No Information Available!>.
  10. 2014-09-29 16:29:09 ERROR OGG-01668 Oracle GoldenGate Capture for MySQL, ext_1.prm: PROCESS ABENDING.

重新删除extract进程组，重新添加

[java] view plain copy print ?

  1. bash-4.1$ ./ggsci
  2. Oracle GoldenGate Command Interpreter for MySQL
  3. Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
  4. Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30
  5. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
  6. GGSCI (ogg) 1> delete extract ext_1
  7. Deleted EXTRACT EXT_1.
  8. GGSCI (ogg) 2> add extract ext_1,tranlog,begin now
  9. EXTRACT added.
  10. GGSCI (ogg) 3> add exttrail ./dirdat/e2,extract ext_1
  11. EXTTRAIL added.
  12. GGSCI (ogg) 4> start ext_1
  13. Sending START request to MANAGER …
  14. EXTRACT EXT_1 starting
  15. GGSCI (ogg) 5> info all
  16. Program Status Group Lag at Chkpt Time Since Chkpt
  17. MANAGER RUNNING
  18. EXTRACT RUNNING EXT_1 00:00:23 00:00:03
  19. EXTRACT RUNNING PUMP_1 00:00:00 00:00:01

extract进程，启动成功！

查看日志：

-bash-4.1$ tail ggserr.log

[java] view plain copy print ?

  1. 2014-09-29 16:55:12 INFO OGG-00993 Oracle GoldenGate Capture for MySQL, ext_1.prm: EXTRACT EXT_1 started.
  2. 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.
  3. 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.
  4. 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.
  5. 2014-09-29 16:55:12 INFO OGG-01026 Oracle GoldenGate Capture for MySQL, ext_1.prm: Rolling over remote file ./dirdat/e2000155.
  6. 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.
  7. 2014-09-29 16:55:12 INFO OGG-01057 Oracle GoldenGate Capture for MySQL, ext_1.prm: Recovery completed for all targets.
  8. 2014-09-29 16:55:12 INFO OGG-00182 Oracle GoldenGate Capture for MySQL, ext_1.prm: VAM API running in single-threaded mode.
  9. 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.

发表评论

表情：
评论列表 （有 0 条评论，288人围观）

还没有评论，来说两句吧...

相关阅读