Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to migrate the data on oracle to mysql". In the daily operation, I believe that many people have doubts about how to migrate the data on oracle to mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "how to migrate the data on oracle to mysql". Next, please follow the editor to study!

First, install OGG on the oracle side and MySQL side, which is very simple, then configure mgr on the source side and the destination side (the port needs to be the same), then grab the process on the source side configuration, configure the replicate process on the destination side (run it only once), then use the DEFGEN command on the source side to do the mapping file, and transfer the file to the corresponding directory on the mysql side. Starting the crawling process on the source side can initialize the database and complete the data migration.

Notes and related errors:

1 the ogg from ogg to mysql is a heterogeneous ogg, so we need to generate a mapping file, that is, the mapping relationship between the two tables, with the help of the DEFGEN command, and put the file under the corresponding location on the target side. Otherwise, an error will be reported: WARNING OGG-01194 EXTRACT task RINIG1 abended: Could not find definition for INFOSERVICE.T_MEMBER_INFO_SUM

2. When configuring the crawling process on the source side, it should be noted that a crawling process is needed to initialize the database, and then a crawling process is needed to maintain data synchronization. These two crawling processes are different, and then the destination side also needs two replicate processes, one for initializing the database and the other for real-time data synchronization.

3. Note that the oracle field is case-sensitive by default, but mysql is not case-sensitive by default. As follows:

Mysql > select login_id from T_MEMBER_INFO where login_id = 'SHFRONT'

+-+

| | login_id |

+-+

| | shfront |

| | SHFRONT |

+-+

2 rows in set (0.81 sec)

This modification is needed to make mysql case-sensitive.

Mysql > alter table T_MEMBER_INFO modify login_id varchar (100) binary

Mysql > select login_id from T_MEMBER_INFO where login_id = 'SHFRONT'

+-+

| | login_id |

+-+

| | SHFRONT |

+-+

1 row in set (0.78 sec)

If the primary key of mysql is login_id, you may encounter the following error, primary key conflict, the real reason is that the mysql field value is case-insensitive by default

Error report: WARNING OGG-01004 Aborted grouped transaction on 'liuwenhe.T_MEMBER_INFO_SUM', Database error 1062 ([SQL error 1062] Duplicate entry' shandongchaoyue' for key 'PRIMARY'

When OGG is transferred from oracle to MySQL, it will encounter a lot of problems different from oracle in the process of using mysql. Conflicts of primary keys are always reported when initializing data, but these data are all derived from oracle databases with the same table structure, and of course the primary keys are the same.

After looking at the data file, I found that there are a lot of data with different case, such as' goolen,'GOOLEN','Goolen''.

In fact, in MySQL, field values are case-insensitive by default, which means that the inserted values' abc' and 'ABC' are equivalent.

The specific configuration is shown below:

Source oracle crawling process configuration:

[oracle@master2 ggs] $cd / u01/OGG_linux/ggs

[oracle@master2 ggs] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

1Magna GGSCI (master2) 1 > create subdirs

Creating subdirectories under current directory / u01/OGG_linux/ggs

Parameter files / u01/OGG_linux/ggs/dirprm: already exists

Report files / u01/OGG_linux/ggs/dirrpt: created

Checkpoint files / u01/OGG_linux/ggs/dirchk: created

Process status files / u01/OGG_linux/ggs/dirpcs: created

SQL script files / u01/OGG_linux/ggs/dirsql: created

Database definitions files / u01/OGG_linux/ggs/dirdef: created

Extract data files / u01/OGG_linux/ggs/dirdat: created

Temporary files / u01/OGG_linux/ggs/dirtmp: created

Stdout files / u01/OGG_linux/ggs/dirout: created

2. Log in to the database

GGSCI (master2) 3 > dblogin userid ogg,password ogg

Successfully logged into database.

3. Add users who need synchronization to ogg

Open additional logs for all tables under the scott user

GGSCI (WebServer) 3 > add trandata infoservice.*

2013-03-08 11:02:33 WARNING OGG-00869 No unique key is defined for table 'BONUS'.

All viable columns will be used to represent the key, but may not guarantee

Uniqueness. KEYCOLS may be used to

Define the key.

Logging of supplemental redo data enabled

For table SCOTT.BONUS.

Logging of supplemental redo data enabled

For table SCOTT.DEPT.

Logging of supplemental redo data enabled

For table SCOTT.EMP.

2013-03-08 11:02:34 WARNING OGG-00869 No unique key is defined for table

'SALGRADE'. All viable columns will be used to represent the key, but may not

Guarantee uniqueness. KEYCOLS may be

Used to define the key.

Logging of supplemental redo data enabled

For table SCOTT.SALGRADE.

4GSCI (WebServer) 4 > info trandata scott.*

Logging of supplemental redo log data is

Enabled for table SCOTT.BONUS.

Columns supplementally logged for table

SCOTT.BONUS: ENAME, JOB, SAL, COMM.

Logging of supplemental redo log data is

Enabled for table SCOTT.DEPT.

Columns supplementally logged for table

SCOTT.DEPT: DEPTNO.

Logging of supplemental redo log data is

Enabled for table SCOTT.EMP.

Columns supplementally logged for table

SCOTT.EMP: EMPNO.

Logging of supplemental redo log data is

Enabled for table SCOTT.SALGRADE.

Columns supplementally logged for table

SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

3GGSCI (master2) 3 > edit params mgr

PORT 7839

4GSCI (master2) 3 > start mgr

5GGSCI (master2) 3 > info mgr

Manager is running (IP port

WebServer.7839).

1. Crawling process

GGSCI (oracle3) 10 > add extract ext_1,sourceistable # sourceistable means to read data directly from the table

GGSCI (oracle3) 10 > view params ext_1

Extract ext_1

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Userid goldengate@cbl, password ogg123456

Rmthost 192.168.0.12,mgrport 7839

Rmttask replicat,group rinig1

Table INFOSERVICE.T_MEMBER_INFO

The mapping file generated by the 2.DEFGEN command.

1) create the parameter file of the DEFGEN tool

GGSCI (oracle3) 3 > view params defgen

Defsfile. / dirdef/source.def, purge

Userid ogg@rman_cbl1, password ogg

TABLE INFOSERVICE.T_PUBLISH_INFO

TABLE INFOSERVICE.T_PUBLISH_ZBXX

TABLE INFOSERVICE.T_MEMBER_INFO

TABLE INFOSERVICE.T_MEMBER_INFO_FUBIAO1

TABLE INFOSERVICE.T_MEMBER_INFO_SUM

TABLE INFOSERVICE.T_MEMBER_MY

TABLE INFOSERVICE.T_MEMBER_MY_INFO

TABLE INFOSERVICE.T_PUBLISH_INFO_SUM_TONGJI

TABLE INFOSERVICE.USER_REGIST_LS_FAIL

TABLE INFOSERVICE.USER_REGIST_LS

2) run the DEFGEN tool to generate the data definition file

Go to the GGSCI installation directory and execute on the command line:

[oracle@oracle3 ogg] $. / defgen paramfile dirprm/defgen.prm

3) scp the generated file to the corresponding file on the target side:

[oracle@oracle3 ogg] scp / home/oracle/ogg/dirdef/source.def root@192.168.0.12:/files/ogg/dirdef/source.def

Destination: replicate configuration:

GGSCI (db2) 1 > add replicat rinig1,specialrun / / specialrun means run only once

Notice the following target liuwenhe.T_MEMBER_INFO

GGSCI (server02) 12 > view params rinig1

Replicat rinig1

Sourcecharset ZHS16GBK

Setenv (NLS_LANG=AMERICAN_AMERICA.zhs16gbk)

Sourcedefs. / dirdef/source.def

Sourcedb infoservice,userid ogg,password ogg

Discardfile. / dirrpt/rinig1.dsc,purge

Map infoservice.T_MEMBER_INFO, target liuwenhe.T_MEMBER_INFO REPERROR (1403, discard)

Finally, open the crawling process on the source side.

GGSCI (oracle3) 3 > start ext_1

GGSCI (oracle3) 3 > view report ext_1 # View the output result

Finally, you can look at the log:

Ogg error log

[root@server02 dirdef] # find /-name ggserr.log

[root@server02 dirdef] # tail-f / files/ogg/ggserr.log

Summary:

The ogg from oracle to mysql needs to use the mapping file generated by the defgen command and transfer the file to the corresponding location on the target side to complete database initialization. The replicate process used for initialization will only run once. Pay attention to the character set problem, that is, the character set configured by the crawling process at the source side must be the same as that configured by the target side replicate, otherwise only part of the data may be imported.

At this point, the study on "how to migrate the data on oracle to mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.