One thing that has been going on for two weeks is the migration of databases. Do not feel this is a very easy thing to do, but wait until the real to do to achieve the time, their dumbfounded. This tangle,
First of all, here's the problem:
1. Table structure Problem of database: Different data types need to be resolved varchar2------varchar, number-----int, date----datetime, the SQL statement field defaults for the table, and how the annotations are resolved.
2. There are no known sensitive fields in Oracle, but there are a lot of sensitive fields in the MySQL table. It was strange not knowing what was wrong when it went wrong. The original describe field is a sensitive field for MySQL.
Here I also found a ready-made tool on the Internet: Oracletomysql, it is only for us to migrate the table structure.
Specific address: http://www.5stardatabasesoftware.com/cn/
3. Oracle Backup SQL does not run normally in MySQL, some to_date () function, To_char () is very painful do not know how to replace, the reason is very simple in Oracle's backup file has a heap of interpreted text:
Copy Code code as follows:
Prompt pl/sql Developer import file
Prompt Created on the info 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, Auditorstatu S, RecordStatus, FIELD1, FIELD2)
These things how to remove, everyone's idea may be directly after I delete directly in MySQL run, but you think if if your backup file is very large, it is not open. I encountered a SQL backup has 1G, the computer is not really can not open no way to have to think of other ways.
Thanks here: Itpub Forum's philip_zhong friend, here he provides a program to handle the migration of large amounts of data. Here is a comment on my usage, he offers a variety of ways, shell script, Windows Bat boot, and the source program. I have tried, the first two did not adjust, had to bite the bullet to his source program to run, a variety of debug changes finally tune through. I'm so glad ...
Here's what you need to be reminded:
Copy Code code as follows:
Static Datasyncdatasourceparameter datasourceparameters;
Static Datasyncsessionparameter SessionParameter;
Static final String configfilename = "config.properties";//This is the parameter in the source program, configured according to your own needs
Static final String configfilename = "config_oracle2mysql.properties";//This 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"; this is where your package is located.
String Conffilepath = Progpath + "//conf";
if (Setparameters (Conffilepath)) {
Start to call thread to sync the data
Syncdata ();
}
}
Config_oracle2mysql.properties Profile: Here's what you need to be aware of: Ora_hash is a function of 10g. Here we change: dbms_utility. Get_hash_value This HASH function is a similar and Ora_hash function that has been on the web for a long time before someone mentions it. Anyway, my understanding here is that in order to save time for a large amount of data, the author is controlled by a hash method. Other attention to the place I have written in the program to remind me of the second and give you a wake-up call don't make my mistake and waste everyone's time.
Copy Code code as follows:
#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
#这里的auditorid必须是主键, Ora_hash is used in 10g, our 9i can't use AH.
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 a ready-made table in the target database.
target.database.commitnum=1000
If you encounter a specific problem, you can communicate with each other.
