Shulou(Shulou.com)06/01 Report--

Oracle GoldenGate Learning-heterogeneous platform synchronization (Mysql to Oracle)

As shown in the figure: the source side uses the Mysql library, and the target side uses the Oracle library

1. OGG installation configuration (source side)

1. Download 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

[oracle@ogg ~] $cd / u01/ogg_ms/ [oracle @ ogg ogg_ms] $lsggs_Linux_x86_MySQL_ 32bit.tar [oracle @ ogg ogg_ms] $tar xvf ggs_Linux_x86_MySQL_32bit.tar

[oracle@ogg ogg_ms] $. / ggsciOracle GoldenGate Command Interpreter for MySQLVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (ogg) 1 > create subdirsCreating subdirectories under current directory / u01/ogg_msParameter files / u01/ogg_ms/dirprm: already existsReport files / u01/ogg_ms/dirrpt: createdCheckpoint files / u01/ogg_ms/dirchk: createdProcess status files / u01/ogg_ms/dirpcs: createdSQL script files / u01/ogg_ms/dirsql: createdDatabase definitions files / U01/ogg_ms/dirdef: createdExtract data files / u01/ogg_ms/dirdat: createdTemporary files / u01/ogg_ms/dirtmp: createdStdout files / u01/ogg_ms/dirout: created II. Database configuration

Source side: mysql library configuration

Database configuration file: [root@ogg ~] # cat / etc/my.cnf# Replication Master Server (default) # binary logging is required for replicationlog-bin=mysql-bin# binary logging format-mixed recommended#binlog_format=mixed binlog_format=row start the database service [root@ogg ~] # service mysql startStarting MySQL connection database [OK] [root@ogg ~] # mysql- h localhost-u mysql- pEnter password:Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType '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 changedmysql > show tables Empty set (0.00 sec) create test table (storage engine is 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) destination end: Oracle library configuration:

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/testConnected.16:08:18 TEST@ prod > create table test2 (id int,name varchar2 (10)); Table created. III. OGG synchronization configuration

Source side: Mysql library configuration

[oracle@ogg ogg_ms] $. / ggsci

Oracle GoldenGate Command Interpreter for MySQLVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. User login (root): GGSCI (ogg) 2 > dblogin sourcedb test@localhost:3306,userid root,password oracleSuccessfully logged into database. Configure mgr:GGSCI (ogg) 3 > edit param mgrport 7809dynamicportlist 7800-8000autorestart extract *, waitminutes 2 published 5GGSCI (ogg) 4 > start mgrManager started.GGSCI (ogg) 5 > info mgrManager is running (IP port ogg.7809) .GGSCI (ogg) 6 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING configure extract process group: GGSCI (ogg) 7 > edit param ext_1extract ext_1setenv (MYSQL_HOME= "/ var/lib/mysql") tranlogoptions altlogdest / var/lib/mysql/mysql-bin.indexsourcedb test@localhost:3306,userid root Password oracleexttrail. / dirdat/e2dynamicresolutiongettruncatestable test.test2 GGSCI (ogg) 11 > add extract ext_1,tranlog,begin nowEXTRACT added.GGSCI (ogg) 12 > add exttrail. / dirdat/e2,extract ext_1EXTTRAIL added. Configure the pump process group as follows: GGSCI (ogg) 13 > edit params pump_1extract pump_1rmthost 192.168.8.249 Mgrport 7809rmttrail / u01/ogg/dirdat/e2passthrugettruncatestable test.test2;GGSCI (ogg) 14 > add extract pump_1,exttrailsource. / dirdat/e2EXTRACT added.GGSCI (ogg) 15 > add rmttrail / u01 pump_1RMTTRAIL added pump_1RMTTRAIL added. Heterogeneous platform configuration defgen:GGSCI (ogg) 16 > edit params defgendefsfile / u01/ogg_ms/dirdef/defgen.prmsourcedb test@localhost:3306, userid root,password oracletable 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:19Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2014-09-26 16:01:05****Operating System Version:LinuxVersion # 1 SMP Wed Sep 1 01:26:34 EDT 2010 Release 2.6.32-71.el6.i686Node: oggMachine: i686 soft limit hard limitAddress Space Size: unlimited unlimitedHeap Size: unlimited unlimitedFile Size: unlimited unlimitedCPU Time: unlimited unlimitedProcess id: 2606 * * * Running with the following parameters * * * defsfile / u01/ogg_ms/dirdef/defgen.prmsourcedb test@localhost:3306 Userid root,password * table test.test2 Retrieving definition for test.test2Definitions generated for 1 table in / u01/ogg_ms/dirdef/defgen.prm transfers the defgen file to the destination: [oracle@ogg ogg_ms] $scp dirdef/defgen.prm rh7:/u01/ogg/dirdefGGSCI (ogg) 5 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT_1 00:00:00 00:21:03EXTRACT RUNNING PUMP_1 00:00:00 Start the extract and pump processes at 00:00:06: GGSCI (ogg) 6 > start extract ext_1Sending START request to MANAGER... EXTRACT EXT_1 startingGGSCI (ogg) 7 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT_1 00:00:00 00:21:17EXTRACT RUNNING PUMP_1 00:00:00 00:00:09extract process startup exception (abended)!

View the log:

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.

2014-09-26 17:24:56 ERROR OGG-01668 Oracle GoldenGate Capture for MySQL, ext_1.prm: PROCESS ABENDING.

Appendix: solutions (refer to network documentation)

GoldenGate's official documentation makes it clear that GoldenGate needs to set MySQL's log format (binlog_format) to ROW, while the other two formats (MIXED or STATEMENT) are not supported.

# official description is as follows:

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.

#

However, MySQL does not support ROW and MIXED formats before version 5.1.5 (MySQL 5.1.5 introduced ROW,5.1.8 into MIXED)

# official description is as follows: 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.

#

Currently, the customer's MySQL version is 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 is not supported in this version, so after GolenGate is installed, when the extraction process is started, the process abend

# the following is the error message:

2014-09-16 16:55:16 ERROR OGG-00146 VAM function VAMRead returned unexpected result: error 600-VAM Client Report

Therefore, if you want to use GoldenGate to achieve real-time data synchronization of MySQL, you need to upgrade the MySQL version to more than 5.1.5.

4. Ogg configuration on the target side

Configure MGRGGSCI (rh7.cuug.net) 4 > edit params mgrport 7809dynamicportlist 7800-8000autorestart extract *, waitminutes 2 5~GGSCI (rh7.cuug.net) 5 > start mgrManager started.GGSCI (rh7.cuug.net) 6 > info mgrManager is running (IP port rh7.cuug.net.7809). Configure replicat process group: [oracle@rh7 ogg] $. / ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (rh7.cuug.net) 1 > GGSCI (rh7.cuug.net) 1 > edit param rep_1replicat rep_1sourcedefs / u01/ogg/dirdef/defgen.prmuserid ogg,password oggreperror default,discarddiscardfile / u01 Greg target test.test2;GGSCI dirrptplash repair1.dscl appendary megabytes 50dynamicresolutionmap test.test2, target test.test2;GGSCI (rh7.cuug.net) 1 > add replicat rep_1,exttrail / u01/ogg/dirdat/e2REPLICAT added. Start the mgr and replicat processes: GGSCI (rh7.cuug.net) 3 > start mgrManager started.GGSCI (rh7.cuug.net) 4 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGREPLICAT STOPPED REP_1 00:00:00 00:00:34REPLICAT ABENDED RORA_1 00:00:00 383:08:47GGSCI (rh7.cuug.net) 5 > start replicat rep_1Sending START request to MANAGER... REPLICAT REP_1 startingGGSCI (rh7) .cuug.net) 6 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGREPLICAT RUNNINGREP _ 1 00:00:00 00:00:00

Due to the source side extract process exception, data can not be synchronized, the problem has not been solved, welcome guidance, to be continued.

Problem solving:

Change the version of mysql-5.5.12. Currently, Golden Gate version does not support mysql-5.6.

GGSCI (ogg) 1 > edit param ext_1

Configuration / etc/my.cnf file:

# binary logging is required for replicationlog-bin=/var/lib/mysql/mysql-binlog-bin-index=/var/lib/mysql/mysql-bin.index# binary logging format-mixed recommended#binlog_format=mixedbinlog_format=row and edit the extract process group configuration file

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

Start the extract process

GGSCI (ogg) 2 > start ext_1Sending START request to MANAGER... EXTRACT EXT_1 startingGGSCI (ogg) 3 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT_1 00:00:00 72:19:40EXTRACT RUNNING PUMP_1 00:00:00 00:00:01

Still failed, the following is the ogg log:

[root@ogg ogg_ms] # tail ggserr.log

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. 2014-09-29 16:29:09 ERROR OGG-01668 Oracle GoldenGate Capture for MySQL, ext_1.prm: PROCESS ABENDING.

Delete the extract process group again and add it again

Bash-4.1 $. / ggsciOracle GoldenGate Command Interpreter for MySQLVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (ogg) 1 > delete extract ext_1Deleted EXTRACT EXT_1.GGSCI (ogg) 2 > add extract ext_1,tranlog,begin nowEXTRACT added.GGSCI (ogg) 3 > add exttrail. / dirdat/e2 Extract ext_1EXTTRAIL added.GGSCI (ogg) 4 > start ext_1Sending START request to MANAGER... EXTRACT EXT_1 startingGGSCI (ogg) 5 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNINGEXT _ 1 00:00:23 00:00:03EXTRACT RUNNING PUMP_1 00:00:00 00:00:01extract process Start successfully!

View the log:

-bash-4.1$ tail ggserr.log

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. Data synchronization, task continues.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.