PHP migrates data from Oracle to Mysql instances

Why migration?

First of all, considering the operation cost, using Mysql can save a lot of money. On the other hand, the stability and functions of Mysql are constantly improved and enhanced to meet customers' needs, such as supporting multi-node deployment and data partitioning. In addition, Mysql is easier to use than Oracle. Therefore, the customer needs to migrate the existing Oracle data table and content to Mysql.

Why do I need to write the script by myself?
The migrated tables and data are quite large, with hundreds of tables. Therefore, manual completion is not convenient. I also tried some free migration tools, such as MySQLMigrationTool, and found that the transferred field type does not meet the requirements (maybe the original Oracle table is not well designed ), this may result in incomplete data and feel that the data is not too reliable. Therefore, we decided to write the migration script by ourselves. If any migration script does not meet the requirements, we can adjust it immediately. So let's get started.

Technical Support

1. php5

2. php oci8

3. mysql 5.1

Migration table structure

Retrieve all schema tables

Use the following statement to obtain all the table names in the schema from Oracle:

SELECT table_name FROM user_tables

Then, you can traverse all tables and Create Table structures and migrate data to mysql.

Obtain all fields and types of a single table

Use the following statement to obtain all fields and types of a single table from oracle:

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT   FROM USER_TAB_COLUMNS   WHERE TABLE_NAME = UPPER('{$table_name}') ORDER BY column_id ASC

In this way, you can know whether the table field name, type, length, can be blank, default value. Because the Field Types of oracle and mysql are not fully compatible, you need to create a ing table of field types.

Oracle	Mysql
Number (<11)>	Int
Number (> 11)	Bigint
Varchar	Varchar
Varchar2 (& lt; 255) & gt;	Varchar
Varchar2 (& gt; 255)	Text
Clob	Text
Date	Date

Obtain the Primary Foreign Unique Key of a single table. Use the following statement to obtain the Primary Foreign Unique Key of a single table from oracle.

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.R_CONSTRAINT_NAME, C.DELETE_RULE,  CC.COLUMN_NAME      FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CC  WHERE C.TABLE_NAME=upper('{$table_name}') AND C.CONSTRAINT_TYPE!='C'  AND C.CONSTRAINT_NAME=CC.CONSTRAINT_NAME AND C.OWNER=CC.OWNER AND C.TABLE_NAME=CC.TABLE_NAME Order by c. CONSTRAINT_TYPE, C. CONSTRAINT_NAME, CC. POSITION

Obtain the index of a single table

Use the following statement to obtain the index of a single table from oracle:

SELECT T.INDEX_NAME,T.COLUMN_NAME,I.INDEX_TYPE FROM USER_IND_COLUMNS T,USER_INDEXES I WHERE T.INDEX_NAME = I.INDEX_NAME AND T.TABLE_NAME = I.TABLE_NAME AND T.TABLE_NAME = UPPER('{$table_name}')

In this way, you can obtain the index name and the indexed field of the table.

You can create a table structure by assembling all the table information into the mysql table structure with the table name, field information, primary key, Foreign, Unique. Index and other information. Since oracle does not have an auto-increment field, a work und is to use primary key as the auto-increment field in mysql, because oracle cannot accurately know which field uses seq for auto-increment, this method is not very accurate.

Migration View

Obtain all views in the schema. Use the following statement to obtain all views in schema from oracle:

SELECT VIEW_NAME,TEXT      FROM USER_VIEWS

In this way, you can obtain the view name and build the view statement.

View processing incompatible with Oracle and Mysql

In Oracle, The view Construction statements are looser, while in mysql, The view Construction statements must be stricter. from statements cannot be constructed from subqueries, that is, statements are not allowed by humans.

CREATE VIEW `view_name` AS SELECT * FROM (SELECT * FROM table_name) ...

To deal with such a view, you can create a view for the subquery and replace the subquery with the newly created view name.

Migrate data

Data Migration: select data from the Oracle database and assemble it into an insert Statement of Mysql. Note two points: first, oracle date type data format and mysql date type format is not consistent, so use TO_CHAR (field_name, 'yyyy-MM-DD ') convert it to the corresponding data format of mysql. Second, the content of the clob field in oracle should be retrieved using oci_fetch_array ($ stid, OCI_ASSOC | OCI_RETURN_NULLS | OCI_RETURN_LOBS, insert the text field to mysql. When inserting a large amount of data in mysql, note that the default value of max_allowed_packet in my. ini or my. cnf in window is 1 MB.

[mysqld]...max_allowed_packet = 16M...

The mysql goes away error does not occur in linux, because the default value is 16 Mb.

Content that cannot be migrated

The triggers and stored procedures in Oracle are different from those in Mysql, so they cannot be automatically migrated through the script program. You need to manually rewrite it.

Summary

For data migration from Oracle to Mysql, the table structure and data can basically be translated. Some views are not fully compatible with oracle statements, so we need to create some intermediate tables for them. The trigger and stored procedure cannot be translated. The two parts must be overwritten. After the table structure is translated, check whether the table structure is suitable. If you think it is not good, you can use alter or other statements to adjust the table structure. When importing a large amount of data, pay attention to the mysql configuration file (my. ini or my. the max_allowed_packet value in cnf) should be set too small to avoid data import errors, import failures, garbled characters, and other unstable factors.