In-depth summary and analysis of ORACLE migration to MYSQL

During the past two weeks, I have been busy with the migration of databases. When I did not do it, I felt it was very easy, but I was dumpfounded when I did it. This tangle,

Here we will first talk about the problem:
1. Database Table Structure Problem: different data types need to solve varchar2 ------ varchar, number ----- int, date ---- datetime, how to solve the default value and comment of the SQL statement field for table creation.

2. There are no sensitive fields in oracle, but there are many sensitive fields in mysql table. It was strange that I did not know where the error was. The original describe field is a mysql sensitive field.

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

3. oracle backup SQL cannot run normally in mysql. Some to_date () functions, to_char (), make people very painful and do not know how to replace them, the reason is simple: there is a pile of explanatory text in the oracle backup file:

Copy codeThe Code is as follows: 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 can we remove these things? The idea may be that I directly run them in mysql after they are deleted, but if you think that your backup file is too large, you just cannot open it. I have encountered 1 GB of SQL backup, but I cannot open my computer. I have no choice but to think about other methods.

Here, I would like to express my gratitude to the friend of the ITPUB Forum, Philip Zhong. Here he provides a program to handle the migration of large data volumes. Here I will talk about how to use it. He provides a variety of methods, such as shell scripts, bat startup in windows, and source programs. I tried it. The first two were not called, so I had to execute his source program with my scalp. After debugging, I finally called it. I'm glad to know that ......
Note the following:
Copy codeThe Code is as follows: static datasync1_ceparameter extends ceparameters;
Static dataSyncSessionParameter sessionParameter;
// Static final String configFileName = "config. properties"; // the parameters in the source program are configured as needed.
Static final String configFileName = "config_oracle2mysql.properties"; // here is my configuration file
/**
* @ 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"; // You must note the location of your package.
String confFilePath = progPath + "// conf ";
If (setparameters (confFilePath )){
// Start to call thread to sync the data
SyncData ();
}
}

Config_oracle2mysql.properties configuration file: note that ora_hash is a 10 Gb function. Here we will change it: the hash function DBMS_UTILITY.GET_HASH_VALUE is a function similar to ora_hash that has been found online for a long time. My understanding here is to save time for multi-threaded large data migration, and the author uses the hash method to control it. Other notes I have written in the program to remind me of the second and remind everyone not to make my mistake and waste everyone's time.Copy codeThe Code is as follows: # for source database parameters
Source. dataSource. initialSize = 10
Source. dataSource. maxIdle = 20
Source. dataSource. minIdle = 5
Resource. 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, the auditorid must be the primary key and the ora_hash must be used in 10 Gb. Our 9i cannot be used.
Source. database. selectSql = select * from t_ B _role where DBMS_UTILITY.GET_HASH_VALUE (roleid, 1, # threadNum #) =?
# You can input commands and split ";"
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. Data Source. 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 a ready-made table in the target database.
Target. database. commitNum = 1000

If you have encountered any specific problems, you can discuss them together.