Web systems migrating from Oracle to MySQL

Http://www.linuxidc.com/Linux/2014-02/97335.htm

The first two days of leadership gave a live, database migration, the original system is used by the Oracle database, now to change to MySQL, then did not think about the next, the original system with the framework of SSI, so probably wanted to make adjustments to the following points

First Database migration

Modification of the second application

The following is a detailed procedure for

A Database migration

The simple thing is to migrate all the data in the Oracle database to MySQL, which can be used with the Mysqlmigration Toolkit tool

MySQL released a toolkit mysqlmigration Toolkit that migrated data from SQL Server or Oracle to MySQL. With a wizard interface, this toolkit easily imports existing data into a MySQL database that can be quickly ported, including table structures and views in a variety of databases to mitigate risk, use proven porting strategies, cost savings, and an integrated toolkit that can significantly increase efficiency Reduce routine maintenance work oracle-to-mysql.

After porting with Mysqlmigration Toolkit, the field type of the table varies (from Oracle to MySQL): Varchar2→varchar,date→datetime,timestamp→datetime, Number→decimal,integer→decimal (22,0).

The row size too large error is reported in the migration Operation Migratedobjects Step, select the problematic table, point to the lower left of advanced, and change the column type of varchar (4000) to text.

The table's primary key, foreign key does not have the problem, checks the next can. The index of the table (the bitmap index of the Oraclede) is converted to the Btree index. The comment information for both the table and the field is lost.

Note: When I migrated, I just migrated the tables and data, and some views, triggers, functions, stored procedures, and so on, were all done manually.

Here are the specific steps to follow:

1 Installation mysqlmigration Toolkit This does not do a specific introduction, the online search is a bunch, after the installation is complete, open mysqlmigration Toolkit encountered an error as follows

Migration Toolkit An error occured during the initialzation of the runtimes system.please make sure ..... ；

A lot of solutions on the internet but I tried not to, looked for a long time finally found a method, the specific operation is as follows

Create a new mysqlmigrationtool.cmd in the MySQLMigrationTool.exe directory

Edit content as

MYSQLMIGRATIONTOOL.EXE-JVM "D:\Program Files (x86) \java\jdk1.7.0_01\jre\bin\client\jvm.dll"

Jvm.dll use the real path on your machine, then start directly with mysqlmigrationtool.cmd, problem solving

2 Migrating databases

1. Run MySQL migration Toolkit, "Next" to "Source database", select Oracle database Server in database system, If the first use will tell you to load the driver Ojdbc14.jar.

After the driver is loaded, the interface becomes the following form, in which you fill in the connection information for the Oracle database and press "Next" to continue.

2. In target database, the default database system is MySQL Server, in connection parameters, fill in the corresponding MySQL database connection information, press "Next" to continue.

3, the database connection test, the test passed after the "next" to the source schemata Selection, click to prepare for the data migration schemata after Press "next" to continue.

4. In Object Type Selection, click Detailed Selection button, select the table that is not migrated in the list on the left, and put it in the list on the right, that is, the remaining tables on the left list will be migrated. Select OK and press "Next" to continue.

5. In object mapping of the migration of type Oracle Schema, if you want to set parameters, click the Set Parameter button. If the default database table is UTF8, select MultiLanguage, and if the default database table is GBK, you will need to select User defined and fill in CHARSET=GBK, collation=gbk_general_ci below. Migration of type Oracle table to set the parameter set parameter button. If the default database table is UTF8, select data Consistency/multilanguage, and if the default database table is GBK, you need to select User defined and fill in addautoincrement=yes below. CHARSET=GBK, Collation=gbk_general_ci, Engine=innodb. Select OK and press "Next" to continue.

6, and then to Manual Editing, here can repair alterations table script. Due to the differences in syntax rules between Oracle and MySQL, it is often necessary to adjust the data type of the script and the default values, such as the default value sysdate is usually set for timestamp type data in Oracle, but it is not recognized in MySQL. Select Show All Objects in the filter and select the table in the migrated Objects to modify the script, then click Advanced in the lower left to edit the script. Click on the right side of the Apply Changes button to save and press "Next" to continue.

7, then all the way "Next", if there is no prompt error, you can import data from the specified table into MySQL. If you are prompted with an error message, you need to return to manual editing to re-modify the script.

It should be added that in the case of large capacity data, especially CLOB data, an exception may occur: "Packets larger than Max_allowed_packet is not allowed". This is because the MySQL database has a system parameter Max_allowed_packet, the default value is 1048576 (1M), you can query its value in the database by the following statement:

Show VARIABLES like '%max_allowed_packet% ';

To modify this parameter, locate the My.ini file in the MySQL folder and add a line in the My.ini file [mysqld]:

max_allowed_packet=16777216

Restart MySQL, this will be able to import less than 16M of data, of course, this value can be adjusted as needed

Note here: In the fifth step operation, the parameters must be set, just start not set, data structure can be imported successfully, but many of the table data are not in or a lot less data, I put igration

of type Oracle Schema, migration of type Oracle table parameter is set to UTF-8, there is no problem, specific reason is unclear

3

Manual migration functions, views, stored procedures, and so on, if there are many users are also to migrate, here is not good to do detailed instructions, specific operations to the Internet to find

Second

Adjustment of the program

Platform migration is a bit of a hassle, because our framework is SSI, inside with a lot of sql.xml, this also is not good to elaborate,

Only a few points of attention can be listed:

1

Paging changes 2 functions in SQL, such as To_char and so on, this piece is not difficult to change is more troublesome, requires a lot of testing

Summary: The platform of the migration, not difficult, more trouble, to be careful, the most important task is to test.

See Oracle Feature page for more information on Oracle HTTP://WWW.LINUXIDC.COM/TOPICNEWS.ASPX?TID=12

Web systems migrating from Oracle to MySQL