Migrate Oracle to MySQL

Oracle DBMS is a well-known player on the market of high load corporate warehouses. This is not surprising that it comes with quite restricted licenses and high-priced total cost of ownership (TCO). Long term trend of reducing corporate expenses for IT infrastructure forces many companies to migrate their databases to more affordable platforms.

MySQL is another advanced database management system that often considered as alternative for Oracle due to similar capabilities of both systems (also, the same seller and support provider). Oracle provides such valuable features as multi-level compression, wide range of backup options and storage customization. Having all these features, MySQL extends capabilities by tight web integration, easy start for development and administration, huge community of developers and administrator working at more affordable rates than Oracle specialists. This allows to consider MySQL as ideal choice for projects demanding high data integrity, reliability and efficiency.

Despite of the approach being used to migrate from Oracle to MySQL, there are following stages that must be implemented:

• Oracle schemas and table definitions are exported into ‘CREATE SCHEMA’ and ‘CREATE TABLE’ statements, providing safe type mapping and proper conversion of the related attributes
• Those SQL-statements are converted according to the target syntax and imported into MySQL database
• Oracle data is exported into CSV files (one file per table), binary and non-standard data must be dumped into hexadecimal representation
• The output CSV files are post-processed to comply with MySQL format (when it is necessary) and imported into the target database
• Oracle views, stored procedures, functions and triggers are extracted in forma of SQL statements and source code.
• Those SQL statements and source code are transformed to comply with MySQL syntax paying attention to system built-in functions and then load to target server. Since MySQL does not provide capabilities similar to Oracle packages, global variables inside packages must be migrated into data of the special service table.

Table Definitions

Below we explore all steps required to migrate Oracle to MySQL in details. Oracle lists all tables available for the current user through the following SELECT-query to system table ‘user_tables’:

SQL> select table_name from user_tables;

Definition of the specified table is extracted as follows below. First two statements are used to set screen buffer large enough to fit the entire table definition. If it is not enough for particular table, increase th volume:

SQL> set long 2000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE',

[,’SCHEMA’]) from DUAL

Placeholder

has to be replaced by actual value. Also, you can specify schema after table name to prevent duplicated tables from different schemas.

The output SQL-statement must be transformed before importing into MySQL database as follows:

• all statements specific for Oracle must be wiped off from table’s DDL (for instance, the pattern “USING INDEX PCTFREE…”)
• Oracle system functions or expressions used as default values for some columns are converted into MySQL equivalents

Data

The statements below are used to export data from Oracle table ‘mytable’ into comma separated values (CSV) file ‘output.csv’. Of course, you have to replace those names with actual values:

SQL> set heading off

SQL> spool output.csv

SQL> select column1 || ',' || column2 || ... from mytable;

SQL> set colsep ','

SQL> select * from mytable;

SQL> spool off;

MySQL provides these statements to import CSV file into a table with specifying the delimiter symbol:

LOAD DATA LOCAL INFILE 'output.csv'

INTO TABLE mytable

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY 'rn';

Once the data is migrated from Oracle to MySQL, it is time to convert indexes and constraints. It is reasonable to be done after the data to catch all possible conflicts with indexes and constraints setting more priority to data integrity.

Indexes

Oracle can extract all indexes for table ‘mytable’ from the dedicated system table ‘all_indexes’ through this statement:

SQL> select * from all_indexes where table_name = ‘mytable’;

Remember that all database objects names are uppercased in Oracle by default. Definition of the specific index is extracted through the following statement:

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('INDEX','') from DUAL;

The output CREATE-statements are transformed to comply with MySQL syntax and then used to create all required indexes in the target database.

Oracle to MySQL Converter

The steps of database migration listed above may seem sophisticated and time-consuming. This is true, especially for large data warehouses incapsulating complicated business logic in views, stored procedures, functions and triggers. Manual migration is bound to human factor that is error prone and may cause data loss or damage. Running migration based on the dedicated commercial tools makes the overall process smooth and safe with just a few clicks of mouse button.

One of such tools is called Oracle-to-MySQL and it has been developed by Intelligent Converters, a software vendor focused in database migration and synchronization for more than 20 years. The tool supports most of database entries such as schemas, table definitions, data, constraints, indexes, foreign keys and views. Low-level algorithms of reading and writing data allow to reach high efficiency benchmarked as 20,000 rows per second on an average modern hardware platform.

Visit the official site of Intelligent Converters to learn more about their Oracle to MySQL database migration tool.