Migration from MYSQL to oracle and backup _ MySQL

The migration from MYSQL to oracle and the backup of bitsCN.com have been a program of the Yulin CPPCC in the past few days. due to the migration from MYSQL to Oracle, we have gained some experience.
Because the project was developed with MYSQL at the beginning, I did not expect any problems to occur.
1. MYSQL can import and export scripts. two initialization scripts for database backup initialization and data initialization should have been prepared, which are exported to full through MYSQL. the SQL initialization script is known to anyone who has used mysql. the exported database script contains statements for creating databases, creating tables, and inserting data. It must be divided into two types: database creation, table creation initialization script, and data insertion initialization script. So I wrote a class used to split the SQL script. in the class, I found the string followed by the beginning of create table, that is, the table name, and obtained the statement starting with insert, the change is to insert data. Therefore, each row of the file is read through the file stream. if the row is inserted, it is put into the convertInsertSQL method and then written into the insert_data. SQL script file, the rest are written to the script file create_table. SQL. the convertInsertSQL method processes the insert statement. because the insert statements in the script are batch inserted, the insert into table name (column name) values (corresponding value ), (corresponding value), (corresponding value), so you need to split it into statements such as insert into table name (column name) values (corresponding value, therefore, by intercepting the previous values (after the previous value is truncated, all the corresponding data is passed), (the regular expression is used to split it into a String array and loop the array, each append is used as an insert statement. All input statements are returned and written to the file stream.
Package com. test. file;

Import java. io. BufferedReader;
Import java. io. BufferedWriter;
Import java. io. File;
Import java. io. FileInputStream;
Import java. io. FileOutputStream;
Import java. io. IOException;
Import java. io. InputStreamReader;
Import java. io. OutputStreamWriter;
Import java. util. HashMap;
Import java. util. Map;

/**
* Scripts for exporting mysql databases
* Split the database into two scripts: database creation and table creation.
* @ Author gaofei
*
*/
Public class MyFileReader {

/**
* @ Param args
*/
Public static void main (String [] args ){
String full_ SQL _path = "C:/Documents and Settings/gaofei/desktop/final mysql script (12.15). SQL ";
String create_ SQL _path = "C:/Documents and Settings/gaofei/desktop/create database initialization. SQL ";
String insert_ SQL _path = "C:/Documents and Settings/gaofei/desktop/data initialization. SQL ";
Try {
ReadFull_to_insert (full_ SQL _path, create_ SQL _path, insert_ SQL _path );
} Catch (IOException e ){
System. out. println ("An error occurred while reading or writing the file ");
E. printStackTrace ();
}

// String aa = "insert into 'templatetype '('id', 'templatetypename', 'describe', 'defdate', 'defvar') values (0, 'General template type', 0, NULL, NULL), (1, 'homepage template type', 0, NULL, NULL), (2, 'topic template type', 0, NULL, NULL), (3, 'topic template type', 0, NULL, NULL), (4, 'content template type', 0, NULL, NULL), (5, 'message template type', 0, NULL, NULL), (6, 'voting template type', 0, NULL, NULL), (7, 'special template type', 0, NULL, NULL );";
// String bb = full_to_part (aa );
// System. out. println (bb );
}
/**
* Split the exported mysql script into a script for creating a database, creating tables, and inserting data.
* Convert the batch insert data statement into the script for each insert data statement.
* @ Param full_ SQL _path: original mysql script exported in full
* @ Param create_ SQL _path refers to the script used to create a database and a table.
* @ Param insert_ SQL _path refers to the inserted data script.
* @ Throws IOException
*/
Private static void readFull_to_insert (String full_ SQL _path, String create_ SQL _path, String insert_ SQL _path) throws IOException {
File fullFile = new File (full_ SQL _path );
File createFile = new File (create_ SQL _path );
If (! CreateFile. exists ())
CreateFile. createNewFile ();
File insertFile = new File (insert_ SQL _path );
If (! InsertFile. exists ())
InsertFile. createNewFile ();
InputStreamReader isr = new InputStreamReader (new FileInputStream (fullFile), "UTF-8 ");
BufferedReader br = new BufferedReader (isr );
OutputStreamWriter osw_create = new OutputStreamWriter (new FileOutputStream (createFile), "UTF-8 ");
OutputStreamWriter osw_insert = new OutputStreamWriter (new FileOutputStream (insertFile), "UTF-8 ");
BufferedWriter bw_create = new BufferedWriter (osw_create );
BufferedWriter bw_insert = new BufferedWriter (osw_insert );
Map AllData = new HashMap ();
String line = null;
Int num = 17;
While (line = br. readLine ())! = Null ){
String lowerLine = line. toLowerCase ();
If (lowerLine. startsWith ("insert") {// This statement is used to determine the sequence of data insertion.
If (lowerLine. indexOf ("'sequenceblock "')! =-1 ){
AllData. put (1, line );
} Else if (lowerLine. indexOf ("'operationlogtype "')! =-1 ){
AllData. put (2, line );
} Else if (lowerLine. indexOf ("'website "')! =-1 ){
AllData. put (3, line );
} Else if (lowerLine. indexOf ("'ucdefine "')! =-1 ){
AllData. put (4, line );
} Else if (lowerLine. indexOf ("'role "')! =-1 ){
AllData. put (5, line );
} Else if (lowerLine. indexOf ("'Department "')! =-1 ){
AllData. put (6, line );
} Else if (lowerLine. indexOf ("'cmsuser "')! =-1 ){
AllData. put (7, line );
} Else if (lowerLine. indexOf ("'account "')! =-1 ){
AllData. put (8, line );
} Else if (lowerLine. indexOf ("'accountrole "')! =-1 ){
AllData. put (9, line );
} Else if (lowerLine. indexOf ("'flowdefine "')! =-1 ){
AllData. put (10, line );
} Else if (lowerLine. indexOf ("'flowtask "')! =-1 ){
AllData. put (11, line );
} Else if (lowerLine. indexOf ("'rolefucperm "')! =-1 ){
AllData. put (12, line );
} Else if (lowerLine. indexOf ("'templategroup "')! =-1 ){
AllData. put (13, line );
} Else if (lowerLine. indexOf ("'templatetype "')! =-1 ){
AllData. put (14, line );
} Else if (lowerLine. indexOf ("'Template "')! =-1 ){
AllData. put (15, line );
} Else if (lowerLine. indexOf ("'contenttype "')! =-1 ){
AllData. put (16, line );
} Else {
AllData. put (num ++, line );
}
} Else {
Bw_create.append (line + "/r/n ");
}
}
For (int I = 1; I <num; I ++ ){
If (allData. containsKey (I )){
Bw_insert.append (full_to_part (allData. get (I )));
}
}

Bw_create.flush ();
Bw_insert.flush ();
Br. close ();
Bw_create.close ();
Bw_insert.close ();
}
// Private static void setSequence (){
//
//}

/**
* Convert data inserted in one row into multiple rows
* @ Param line
* @ Return
*/
Private static String full_to_part (String line ){
StringBuffer sb = new StringBuffer ();
String lowerLine = line. toLowerCase ();
Int firstDan = lowerLine. indexOf (""');
Int firstQuot = lowerLine. indexOf ("(");
String tableName = lowerLine. substring (firstDan, firstQuot );
System. out. println ("-------------------------- start to convert and insert ----" + tableName + "--- Data ----------");
Int values_position = lowerLine. indexOf ("values") + 7;
String forward_line = line. substring (0, values_position );

String datas_line = line. substring (values_position, line. length ()-1); // obtain the data inserted later.
String [] datas = datas_line.split ("//) //, // ("); // based on), (split into a String array

For (int I = 0; I <datas. length; I ++ ){
String data = null;
If (datas. length = 1) {// if there is only one data record that will not be split, the array will have only one data record.
Data = datas [I];
} Else {
If (I = 0) // if it is the first one, append a bracket.
Data = datas [I] + ")";
Else if (I = datas. length-1) // if it is the last one, add a bracket in front
Data = "(" + datas [I];
Else // for intermediate data, brackets are required before and after
Data = "(" + datas [I] + ")";
}
Sb. append (forward_line); // add the insert field name and values first
Sb. append (data + ";");
Sb. append ("/r/n ");
}
Sb. append ("/r/n ");
Return sb. toString ();
}

}
This is a common method. you can use this method to convert the two initialization scripts of MYSQL.
2. Oracle table creation fails to be solved. it can be reverse generated through Hibernate. The relationship between table creation, table structure, and table has been established.
The following figure shows how to insert data:
The insert script for MYSQL is basically the same as that for Oracle, but there are also differences. for date, there are issues with some symbols. Converts an insert statement from MYSQL to an insert statement from ORACLE.
1. remove '. This symbol is included in the table name when data is inserted. Therefore, replace' with the table name.
2. you need to convert the date by using the to_date (date, "yyyy-MM-dd" function. Therefore, I use a regular expression to find all the dates, there is also a date that carries the hour, minute, and second, and requires another regular expression. it can be replaced with the year, month, and day.
Package com. SQL. convert;

Import java. io. BufferedReader;
Import java. io. BufferedWriter;
Import java. io. File;
Import java. io. FileInputStream;
Import java. io. FileOutputStream;
Import java. io. IOException;
Import java. io. InputStreamReader;
Import java. io. OutputStreamWriter;
Import java. util. regex. Matcher;
Import java. util. regex. Pattern;

Public class FromMYSQLInsertToOracleInsert {
/**
* The following must be converted:
* Remove 'from all table names and column names
* @ Param args
* @ Throws IOException
*/

Public static void main (String [] args) throws IOException {
String mysql_file = "C:/Documents and Settings/gaofei/desktop/insertData. SQL ";
String oracle_file = "C:/Documents and Settings/gaofei/desktop/oracle_insertData. SQL ";

TurnSQL (mysql_file, oracle_file );

}
Private static void turnSQL (String mysql_file, String oracle_file) throws IOException {
File mysqlFile = new File (mysql_file );
File oracleFile = new File (oracle_file );
If (! OracleFile. exists ())
OracleFile. createNewFile ();
InputStreamReader isr = new InputStreamReader (new FileInputStream (mysqlFile), "UTF-8 ");
OutputStreamWriter osw = new OutputStreamWriter (new FileOutputStream (oracleFile), "UTF-8 ");

BufferedReader br = new BufferedReader (isr );
BufferedWriter bw = new BufferedWriter (osw );

String line = null;
While (line = br. readLine ())! = Null ){
Bw. append (convertString (line ));
Bw. append ("/r/n ");
}
Bw. flush ();
Br. close ();
Bw. close ();
Isr. close ();
Osw. close ();
}
Private static String convertString (String line ){
Line = line. replace (""',"");
Pattern p = Pattern. compile ("'// d {4} //-/d + '");
Matcher m = p. matcher (line );
String date = null;
While (m. find ()){
Date = m. group (0 );
Line = line. replace (date, "to_date (" + date + ", 'yyyy-MM-DD ')");
}
P = Pattern. compile ("'// d {4} //-// d + // s // d + //: // d + //: // d + '");
M = p. matcher (line );
Date = null;
While (m. find ()){
Date = m. group (0 );
String newDate = date. substring (0, date. indexOf (""));
Line = line. replace (date, "to_date (" + newDate + "', 'yyyy-MM-DD ')");
}
Return line;
}
}

3. it seems that there is no problem. it is okay to execute the script directly, but the problem comes again:
I directly use the sqlplus control to access Oracle. there is no tool. every time a script is executed, the @ script name is used. but note that if the path is too long, such as @ "C:/Documents and Settings/gaofei/desktop/oracle_insertData. SQL", a space must be added in the middle of the path,
Execution error. Many errors:
1. oracle considers this type of special data as a custom variable in plsql programming. of course, a problem may occur. therefore, set define off and disable the custom variable.
2. a problem is rarely used. it is about Oracle data insertion. a field can be inserted with a maximum of 2999 characters. Too many will be inserted. My data is templates or news, and many of them are tens of thousands, so the problem arises. No way. Finally, I deleted a lot of data very short. after successful insertion, I inserted it through a program.

After the migration is completed, the following is about Oracle backup.

The SQL script can be prepared, but the script will still be an INSERT statement and cannot be inserted at that time.
Therefore, the DMP file is backed up through the ORACLE backup command.
At the beginning, the SYSTEM user was used to directly back up the data, but there will be a lot of SYSTEM tables, which does not meet the requirements. As a result, a user's future is established, and the future user is used to reverse generate the table structure and table relationship. Then, you can use insert into sequenceblock select * from system. sequenceblock to grant the futureDBA permission to import all tables with data from the system to the future user.
In this way, the table is created in the specified tablespace of the future user.
Use future to back up data directly. There are two backup methods.
$ Exp future/ylzxdb1219 @ ylzxdb file = D:/future_final.dmp full = y;
Exported information of all databases as a future identity
$ Exp future/ylzxdb1219 @ ylzxdb file = D:/future_user.dmp owner = future;
Export all data of the future user as a future user.
However, future has the DBA permission, so the system table will be backed up when it is backed up again. Therefore, the second method is recommended. Only backups of all tables of the current user's future are exported.
Import data:
$ Imp future/ylzxdb1219 @ ylzxdb fromuser = future touser = future ignore = y file = D:/future_user.dmp;
(Note: for example, the database backup file is placed in the future. user. dmp file on the D: Disk)
This article is from the "Alternative Freedom" blog bitsCN.com