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

Let's talk about migrating Oracle to MySQL using the ogg (goldengate) solution. The secret of the text is that it is relevant to the topic. So, forget the gossip, let's go straight to the following, and I'm sure you'll benefit from reading this article on migrating Oracle to MySQL using the ogg (goldengate) solution.

Source source oracle 11.2.0.4 ogg12c

Ip:10.200.3.193 system centos 7.1

1. Create a source table and some initialization data

Su-oracle

Sqlplus / as sysdba

SQL > alter database add supplemental log data

SQL > select supplemental_log_data_min from v$database

SQL > alter database forcelogging

SQL > select supplemental_log_data_min from v$database

NO

SQL > alter database add supplemental log data

Database altered.

SQL > alter system switch logfile

System altered.

SQL > select supplemental_log_data_min from v$database

YES

SQL > create tablespace ggs

Tablespace created.

SQL > create user ggs identified by ggs default tablespace ggs

User created.

SQL > grant connect,resource,dba to ggs

SQL > alter system set undo_retention=86400

System altered.

SQL > grant flashback any table to ggs

First configure parameters for silent installation-refer to the installation of goldengate12c of centos7.1

two。 Create a Manager parameter file on the source source side and specify the port and some required parameter options:

Cd / u01/app/ggs

. / ggsci

Create related directories: GGSCI (testtech4-193) 1 > create subdirs

Configure mgr

View params mgr

Port 7809

Dynamicportlist 7810-7820

Start start mgr

GGSCI (testtech4-193) 1 > dblogin userid ggs password ggs

Successfully logged into database.

GGSCI (testtech4-193) 1 > REGISTER EXTRACT ext1 DATABASE

GGSCI (testtech4-193) 2 > add trandata workflow.act_ru_execution

3. Edit the defgen file

Vi / u01/app/ggs/dirprm/defgen.prm

Defsfile / u01/app/ggs/dirdef/table_del,purge

Userid ggs, password ggs

Table WORKFLOW.act_ru_execution

Cd / u01/app/ggs

Defgen paramfile / u01/app/ggs/dirprm/defgen.prm

Cd / app/ggs/dirdef/

Copy the file to the / app/ggs/dirdef/ on the destination side

4. Execute the following command on the source source side to add an Extract process called init for data initialization:

Cd / u01/app/ggs

GGSCI (testtech4-193) >. / ggsci

GGSCI (testtech4-193) > add extract init,sourceistable

GGSCI (testtech4-193) > info extract *, task

GGSCI (testtech4-193) > edit params init

Extract init

Userid ggs password ggs

Rmthost 10.200.3.178,mgrport 8809

Rmttask replicat,group repinit

Table WORKFLOW.act_ru_execution

Destination target mysql5.6 ogg 12c

Ip:10.200.3.178 system centos 7.1

1. Create the target target database and the corresponding tables

Mysql-uroot-p

Create a database for login using OGG, for example, you need to store checkpoint table

Mysql > create database ogg

Query OK, 1 row affected (0.00 sec)

Mysql > grant all on. To ogg@10.200.3.178 identified by 'ggs'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.02 sec)

two。 Create a target-side target application process

GGSCI (techtest3-178) > dblogin sourcedb ogg@10.200.3.178:3306 userid ogg password ggs

GGSCI (techtest3-178) > add checkpointtable ogg.ck_table

GGSCI (techtest3-178c) > add replicat repdel,exttrail / u01qqqqqxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

GGSCI (techtest3-178) > edit params repdel

Replicat repdel

Targetdb ogg@10.200.3.178:3306 userid ogg password ggs

Handlecollisions

SOURCECHARSET zhs16gbk-the source character set must be set, especially if the source character set is inconsistent with the destination character set. This parameter must be set.

Sourcedefs / u01/app/ggs/dirdef/table_def

Discardfile / u01/app/ggs/dirrpt/repdel.dsc,purge

Map WORKFLOW.act_ru_execution, target activiti.act_ru_execution

Because it is a heterogeneous environment, specify the sourcedefs parameter and use the data definition file of the source database.

The handlecollisions parameter is used to handle the conflict between the result of initialization loading and real-time change of synchronous capture data.

Destination side: view params mgr

Port 8809

DYNAMICPORTLIST 8840-8850

ACCE × × ULE, PROG REPLICAT, IPADDR 10.200.3.139 ALLOW

3. Target side target create initialization load delivery parameter file

GGSCI (techtest3-178) > add replicat repinit,specialrun

GGSCI (techtest3-178) > edit params repinit

Replicat repinit

Targetdb ogg@10.200.3.178:3306 userid ogg password ggs

Sourcedefs / u01/app/ggs/dirdef/table_def

Discardfile / u01/app/ggs/dirrpt/repinit.dsc,purge

Sqlexec "SET FOREIGN_KEY_CHECKS=0"

Map WORKFLOW.act_ru_execution, target activiti.act_ru_execution

4. Add an Extract group on the source side of source

GGSCI (testtech4-193) > ADD EXTRACT extdel,TRANLOG, BEGIN NOW

GGSCI (testtech4-193) > edit params extdel

EXTRACT extdel

Userid ggs, password ggs

GETTRUNCATES

REPORTCOUNT EVERY 1 MINUTES, RATE

Numfiles 5000

DISCARDFILE. / dirrpt/extdel.dsc, APPEND, MEGABYTES 1000

WARNLONGTRANS 2h, CHECKINTERVAL 3m

EXTTRAIL. / dirdat/dl

TRANLOGOPTIONS CONVERTUCS2CLOBS

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000

DBOPTIONS ALLOWUNUSEDCOLUMN

Table WORKFLOW.act_ru_execution

GGSCI (testtech4-193) > add exttrail. / dirdat/dl, extract extdel, megabytes 500

GGSCI (testtech4-193) > ADD EXTRACT dpedel, EXTTRAILSOURCE. / dirdat/dl

GGSCI (testtech4-193) > edit params dpedel

EXTRACT dpedel

RMTHOST 10.200.3.178, MGRPORT 8809, compress

PASSTHRU

Numfiles 50000

RMTTRAIL. / dirdat/dl

DYNAMICRESOLUTION

Table WORKFLOW.act_ru_execution

GGSCI (testtech4-193) > ADD RMTTRAIL. / dirdat/dl, EXTRACT dpedel, megabytes 500

The following is to start the extraction and delivery process, because the initialization of oracle to mysql cannot make use of scn like oracle.

The general idea is that the process extracts the data first, and then initializes it. When initialization is finished, there will be conflicts in starting the application process, dealing with conflicts and solving problems. Finally, make a data comparison.

GGSCI (testtech4-193) > start extdel

GGSCI (testtech4-193) > start dpedel

5. Source-side source initialization load data

The initialization load extraction task init that is configured before the source source starts:

GGSCI (testtech4-193) > start init

Check the report of the initialization data loading replication process on the destination side target to confirm that the load is successful:

GGSCI (techtest3-178) > view report repinit

After the initialization of init, the process will restart automatically

6. Finally, the destination target starts repdel.

Confirm that the handlecollisions that changes the synchronous Replicat process repdel is closed after the initialization data is loaded.

GGSCI (techtest3-178) 1 > send repdel,nohandlecollisions

The handlecollisions parameter in the repdel parameter file is removed to prevent handlecollisions from being enabled again when repdel is started next time.

Finally, verify the data: initialize data, change data, etc.

Is there anything you don't understand about migrating Oracle to MySQL using the ogg (goldengate) solution above? Or if you want to know more about it, you can continue to follow our industry information section.

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.