In depth summary and analysis of ORACLE migration to MYSQL

In the past two weeks, I have been busy with one thing, that is, the migration of databases. When I didn't do it, I felt that it was a 10-point easy thing, but when I actually did it, I was dumbfounded. This kind of entanglement,

Let's talk about the problems encountered here:
1. Database table structure problem: Different data types need to solve varchar2-----varchar, number------int, date----datetime, sql statement field default value, comments how to solve.

2. There are no so-called sensitive fields in oracle, but there are many sensitive fields in mysql table. It was strange when something went wrong. I don't know what went wrong. Originally, a field of describe was a sensitive field of mysql.

Here, I also found a ready-made tool on the Internet: oracletomysql, which can only migrate the table structure for us.
Specific address: http://www. 5stardatabasesoftware. com/cn/

3. The backup sql of oracle can't run in the normal mysql. Some to_date () functions, to_char () make people very painful and don't know how to replace them. The reason is very simple. There is a pile of explanatory text in the backup file of oracle:

prompt PL/SQL Developer import file
prompt Created on 2012-05-30 by chenbh
set feedback off
set define off
prompt Disabling triggers for T_B_AUDITOR...
alter table T_B_AUDITOR disable all triggers;
prompt Loading T_B_AUDITOR...
insert into T_B_AUDITOR (AUDITORID, NAME, ORGID, SEX, IDCARDNO, TITLE, PHONE, MOBILE, DESCRIBE, AUDITORRIGHT, AUDITORSTATUS, RECORDSTATUS, FIELD1, FIELD2)

How to remove these things, everyone's idea may be that I directly delete it and run directly in mysql, but you want to 1. If your backup file is very big, it can't be opened at all. I met sql backup has 1G, the computer is not really open, there is no way to have to think of other ways.

Here thanks: ITPUB forum philip_zhong friends, here he provided a program, to deal with large data migration work. Here I say under the use of testimony ah, he provides a variety of ways, shell script, windows under the bat start, and the source program. I have tried, the first two did not tune, so I had to crustily skin of head to run his source program 1, and all kinds of debug were finally tuned after modification. I'm glad …
What needs to be reminded here is:

static dataSyncDataSourceParameter dataSourceParameters;
 static dataSyncSessionParameter sessionParameter;
 //static final String configFileName = "config.properties";// Here are the parameters in the source program, which are configured according to their own needs 
 static final String configFileName = "config_oracle2mysql.properties";// Here is my profile 
 /**
  * @param args
  */
 public static void main(String[] args) {
  // initialize the parameters
  //String progPath = args[0];
  //String progPath = "D://work//MyEclipse 8.5//Workspaces//dataSync";E://workspace//oracletomysql//package
  String progPath = "E://workspace//oracletomysql//package";// Attention here, everyone. It's yours package The location of. 
  String confFilePath = progPath + "//conf";
  if (setparameters(confFilePath)) {
   // start to call thread to sync the data
   syncData();
  }
 }

config_oracle2mysql. properties Configuration File: It should be noted here that ora_hash is a function only found in 10g. Here we change it again: DBMS_UTILITY. GET_HASH_VALUE This hash function is a function similar to ora_hash that has been searched on the Internet for a long time before someone mentioned it. Anyway, my understanding here is to save time for multi-threaded handling of large amounts of data, so the author controls it by hash. Other attention I have written out in the program 1 to remind me to pay attention to 2 to remind everyone not to make my mistakes and waste everyone's time.

#for source database parameters
source.dataSource.initialSize=10
source.dataSource.maxIdle=20
source.dataSource.minIdle=5
source.dataSource.maxActive=100
source.dataSource.maxWait=120000
source.jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
source.jdbc.url=jdbc:oracle:thin:@10.17.199.8:1521:lab1107
source.jdbc.username=lab1107
source.jdbc.password=lab1107
#Target sync data threadNum=source.database.threadNum
source.database.threadNum=10
# Here's auditorid Must be a primary key, ora_hash Is in 10g Used in, ours 9i It can't be used. 
source.database.selectSql=select * from t_b_role where DBMS_UTILITY.GET_HASH_VALUE(roleid,1,#threadNum#)=?
#you can input many commands and split by ";" 
source.database.sessionCommand=ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
#for target jdbc parameters
target.dataSource.initialSize=10
target.dataSource.maxIdle=20
target.dataSource.minIdle=5
target.dataSource.maxActive=100
target.dataSource.maxWait=120000
target.jdbc.driverClassName=com.mysql.jdbc.Driver
target.jdbc.url=jdbc:mysql://10.5.110.239:3306/test?autoReconnect=true&characterEncoding=UTF-8
target.jdbc.username=root
target.jdbc.password=chen
#target.database.insertSql=insert into test2(PATHALIASID,PATH,CREATETIME,LASTMODIFIEDTIME,OBJECTPREFIX,PATHMD5ID,COLLIDESWITH) values(?,?,?,?,?,?,?)
target.database.insertSql=insert into T_B_ROLE(ROLEID,ROLENAME,ROLEDESC,ROLESTATUS,RECORDSTATUS,FIELD1,FIELD2,SORTNUM) values(?,?,?,?,?,?,?,?) This must be off-the-shelf in the target database 1 A table. 
target.database.commitNum=1000

If you encounter specific problems, you can communicate with each other.