Goldengate application Environment MySQL to Oracle

Objective

A requirement, some MySQL tables, in real-time synchronization to Oracle, then produced this article, the installation process, took a few detours, the reason is not too deep understanding, the official provision of installation steps represent the meaning.

Environment

Source: Mysql-server 5.5.39, Goldengate for MySQL 121210, mysql-client-5.5.28-1.linux2.6.x86_64.rpm, mysql-connector-odbc-5.2.7-1.el6.x86_64.rpm

Target side: Oracle10g,linux Goldengate 11_1_1

Note : Goldengate version of the inconsistency, there will be compatibility issues, such as the crawl process can not start, the delivery process is not started, etc., the environment is based on incompatibility, but the official provided a solution, the detailed process, I will indicate.

Let's get started, source-side operations

Note: MySQL default I use the root account to do synchronous operation

Basic Environment installation configuration, detailed installation not elaborated

Suppose you have installed the Mysql-server and installed the mysql-client-5.5.28-1.linux2.6.x86_64.rpm,mysql-connector-odbc-5.2.7-1.el6.x86_64. Rpm

Goldengate for MySQL 121210 has also been copied to the MySQL server.

1. Modify VI/ETC/MY.CNF, add the following parameters

[Mysqld]log-bin=mysql-bin      --open binary file, can be modified to another path example:/var/lib/mysql/hh-bin max_binlog_size= 4096 Binlog_format=row     -The log format is line, this important amount, otherwise goldengate, the crawl process can not start the socket=/tmp/mysql.sock  [ Client]socket
Note: this has a very large pit, after adding, need to grep "Binlog_format"/etc/my.cnf check if there are duplicate parameters, others do similar operations.

2. Create a /usr/local/etc/odbc.ini file

 [odbc Data sources]oggdb1  = MyODBC 3.51   Driver dsnoggdb1driver  =/usr/lib64/ View Description  = Connector/odbc 3.51   Driver dsnserver  = Localhostport  = 3306  user  = Rootpassword  = 111111  database  = toption  = 3  socket  =/tmp/mysql.sock 

3. Create a column definition ... Important-This should be done after the installation of Ggsci

vi dirprm/cat dirprm/dsalesab.prmdefsfile dirdef/111111  Table T. *; [Email protected] gg]#. /defgen  paramfile  dirprm/dsalesab.prm noextattr  --note goldengate versions are inconsistent and NOEXTATTR options are added

SCP dirdef/dsalesab.def [Email protected]:/opt/oracle/gg/dirdef   --Copy the generated DEF file to the target end, note that the new table is added later, and repeat this once.

4. Installing Ggsci

tar -xvf ggs_linux_x64_mysql_64bit. Tar [email protected] gg]#. /1

Ggsci so the installation is over.

5. Configuring the MGR Process

3>4>7809   --Add port

6. Configure the crawl process

Ggsci (RAMQ2)8>edit param Etggsci (RAMQ2)8>view param etextract et--Crawl process name setenv (Mysql_home="/usr/local/mysql")--mysql home directory exttrail./DIRDAT/TA, Format release11.1- Note :format release 11.1 defaults to my version to load this option, otherwise the target cannot write  to the datasourcedb [email protected]:3306, UserID Root, Password111111--connect MySQLtranlogoptions altlogdest/usr/local/mysql/data/mysql-bin.index--mysql index log File table T.*; --Table to crawl

Ggsci (host01) > add Extract et, tranlog, Begin now  --add the ET process Extract added. Ggsci (host01) > add Exttrail./dirdat/ta, Extract et, megabytes 5  --Add fetch data to store file Ta, crawl process et operation, size 5m ... Can increase the amount of exttrail added. 

7. Configure the transport process

9> edit param pt
9>192.168. 1.105 7809 , Compress   --Deliver the target machine ggrmttrail. 11.1  creating a target-side data file P5 Note: format release 11.1, explained  above passthrutable T. *;

Ggsci (host01) > Add Extract pt, Exttrailsource./dirdat/ta  -Where to fetch data Extract added. Ggsci (host01) > Add rmttrail/dirdat/p5, Extract pt, megabytes 5  --Add destination P5 file, to add success, Target side Mgr should also start, Data size 5mRMTTRAIL added. 

Target-side operation

Assuming that Oracle is installed

Check environment variables

oracle_base=/opt/Oracle             oracle_home= $ORACLE _base/102oracle_sid=orclld_ Library_path=/opt/oracle/102/lib: $LD _librarypath  --Note: No, Ggsci cannot start PATH= $PATH: $ Oracle_home/bin: $HOME/binexport oracle_base oracle_home oracle_sid ld_library_path  PATH

1. Install the GGSCI, the same way, not verbose.

Configuring the MGR Process

3>4>7809   --Add port 

To add a global configuration file
387> Edit param./387> View param./globalscheckpointtable System.checkpointtable
--Why do you want to add it? Temporarily can't remember, but must have, if you want to do two-way synchronization, the other side also need to add

2. Add the copy process to send data, take out, write to Oracle

Ggsci (GG)389>view param rmyreplicat rmysetenv (Nls_lang=American_america.al32utf8)--corresponds to MySQL code, otherwise Chinese garbled userid System,password111111Handlecollisions--The consistency of the data structure, or Delete, update, the process is hung --Assumetargetdefs--to operate the DDL, turn on the sourcedefs dirdef/Dsalesab.def--Remember the DEF file generated by the source side Discardfile./dirrpt/rmy.dsc,append,megabytes50--Data copy write failed record, very important, an error, you can see this. Map"t.t1", Target chis.my; --pit amount, MySQL table, need to add "" Number map"t.ch*", Target chis.*;--map t.*, Target chis.*;

Add Replicat P5, Exttrail./DIRDAT/P5   --Adding the replication process

Start

OK, finished, source and target side start the process!

Common commands

Start Extract *-Start the crawl process all

Start Replicat *-Start the replication process all

Info All--View all processes

Stats process name--target side operation, view copy process work No

View report process name--Viewing log information

Send [replicat|extract] Process name, report--sends the fetch process or the write process reports get after the Execute View report process name//output reports

Help start extract--Get helpful information

Start Replicat Process name Skiptransaction--the copy process encountered an error, skipped the error, started

Tip: Use Help

Summarize

This tutorial takes 1.5 hours to perfect, but it is not detailed enough to forget the reader's understanding.

The following articles are referenced during the installation process:

Http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/11g/mysql_orcl/index.html--Official tutorial, where map MySQL table did not hit the "number, pit ...

Http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/11g/GGS_Sect_Config_UX_MSQ_to_UX_ Ora.pdf--Official original document, no bug

Goldengate application Environment MySQL to Oracle