Oracle to MySQL Migration
Stored Procedures, Packages, Triggers, Scripts and Applications
White Paper
March 2009, Ispirer Systems Ltd.
Introduction
The purpose of this white paper is to describe the factors that impact migrating databases and applications from Oracle to MySQL.  Cost and risk factors will be detailed, as well as tools and methodologies to help achieve a higher quality conversion.
It is very true that the Sun MySQL database can dramatically reduce the database Total Cost of Ownership (TCO) for a company by lowering license, hardware and administration costs.  The largest risk in moving to the MySQL platform is the risk and complexity of migrating business logic from Oracle, particularly when existing applications make significant use of PL/SQL procedures, triggers, packages and Oracle specific SQL statements.
The migration from Oracle to MySQL can be troublesome, time consuming, and expensive. However, proven methodologies and tools can reduce the cost and time required and can significantly mitigate risk.  With the help of the migration product SQLWays, a migration can be assessed, planned, and properly automated. With the proper use of automated tools and a strong project management process in place, companies can incur savings of over 70% compared to traditional manual migration techniques.  Coupled with the savings from implementing MySQL, automated migration becomes a very attractive alternative.
Challenges
The Oracle database provides very advanced capabilities to develop application logic that resides entirely inside the database using PL/SQL stored procedures, functions, packages and triggers.
Oracle PL/SQL is an easy-to-use and powerful procedural extension to SQL that is strongly recommended by Oracle for performance reasons.  In most applications, use of PL/SQL naturally leads to a significantly large number of procedures, packages, and triggers.  MySQL, although having some similar functionality, does not make use of PL/SQL.mysql帮助文档
Besides Oracle specific syntax, PL/SQL offers many non-ANSI compliant features, including features which are only found in Oracle. These Oracle specific features include:
•Packages - shared package variables, built-in packages
•%TYPE, %ROWTYPE, exceptions
•Object-oriented features: object types, functions, and collections
•Business intelligence and XML features etc
An Oracle to MySQL migration can be a very challenging process, particularly if Oracle specific features are in use, such as the ones described above.
However, such a migration could be relatively easy and risk-free. Such would be the case if the target database contained a relatively small amount of tables and simple business logic. Since cost and risk may vary from project to project, it is important to fulfill a preliminary assessment.
Assessment
The purpose of the assessment is to define the scope, feasibility, cost and risk associated in migratin
g from an Oracle database to a MySQL based database implementation.
Database Assessment
Firstly, you need to define the types of database objects and how many of them you will need to migrate.  Objects are items such as the following:
•Tables
•Views
•Procedures
•Functions
•Packages
•Triggers
•Sequences, synonyms etc.
If you need to convert PL/SQL code (procedures, packages, functions and triggers) or
view/queries containing Oracle specific SQL syntax, you have to investigate what features are used and define the number of their occurrences.  Examples of items that need to be accounted for are:
•Non-ANSI compatible SQL functions, operators and statements
•Results sets
•Cursor loops
•Exceptions
•Temp tables
•Object types and functions
•Collections
•Dynamic SQL
•Built-in packages
•OLAP functions
•XML functions etc.
After you have finished the examination, it is best to select MySQL equivalents or solutions to replace Oracle specific functionality. You can find typical solutions in subsequent chapters.
Application Assessment
Besides schema and server-side business logic conversion, you may also need to modify SQL statements within the application. It is vital to assess how much of this work will need to be done to complete the migration.
To start, you have to check what database API is used in your applications to access the Oracle database. It is important to note how many application source files contain Oracle specific code and therefore need to be modified to work with MySQL.
Most applications use a standard API like ODBC, JDBC, or ADO.NET to access Oracle, but some ap
plications may use a native API like Oracle OCI or Pro*C/C++. Collecting all this information is imperative.
Even if you use a standard API, such as using ODBC/JDBC drivers, significant changes may need to be made to existing SQL statements. For example DECODE functions or legacy left outer join syntax (*) will need to be modified. It is recommended to estimate the number of native SQL statements.
If the application happens to use a native API like Oracle OCI, you will need to completely redesign the database access code to use the MySQL API or ODBC.
Assessment Tools
It is important to understand how much use is made of specific database features. How best is a ‘feature use’ assessment performed?
Start first by calculating the number of tables, procedures, views etc. like in the table below. For a more detailed analysis, you can use Ispirer’s SQLWays product to collect comprehensive statistics.
The following is a sample assessment:
Database Number
Tables 350
Views 280
Procedures 420
Functions 135
Triggers 50
Packages 10
Database Details
BLOBs 37
Outer joins 155
Ref cursors 89
Exceptions 450
Temp tables 34
Application
files
Java/JDBC 590
Outer joins 190
SQL functions  356
Result sets 47
Migration Approach  Automated conversion
Based on the assessment results, you can then develop the migration plan. If you have dozens of procedures, you might consider a manual conversion, but if hundreds or thousands of procedures ne
ed to be migrated, it is best to examine automated migration tools in the market. SQLWays provides such functionality.
Cost and Risk
The cost and risk associated with the conversion project depends on the scope of migration. It is important to note that the cost and risk are also impacted by the diversity and frequency of Oracle features in use in the database and application.  The more Oracle features in use, the more complex and costly the conversion.  Also, the more Oracle features in use, the more automated tools could help achieve success.
Data and DDL Migration Cost
Migration of data and DDL (Schema) objects are typically done very easily, as there are many tools on the market that can assist you with this kind of migration.
Typical Data and DDL migration involves conversion of
•Data types
•Constraints (primary and foreign keys, unique constraints, NULL, defaults etc)
•Data transfer
•Indexes
Although there are syntax differences in Oracle and MySQL DDL statements, both have similar data types (character, number, date, time, LOBs) and allow you specifying similar integrity constraints.
Sample DDL/Data migration estimation:
Database
Tables  <100 tables
LOBs  10 columns
Max table rows <10M
Max table size <300 Mb
Migration Process
hrs
Assessment 2-8
MySQL configuration 4-16 hrs
Automated transfer 2-4 hrs
Testing, configuration
4-12 hrs
change, next iteration
Total time 12-40 hrs
Tools Free, or less than $500
Due to automation, the cost of migrating DDL and data is not directly proportional to the number of tables and data size. For example, the cost of migration for 100 and 300 tables can be similar in the cost, if the tables have similar structure and data size.
When the number of tables and their size increase, you may need to spend more time to properly configure the MySQL database, tune data transfer, and focus on things like index creation performance.
Risk Mitigation for Typical DDL and Data Migration
Typical DDL/Data migration imposes relatively low risk. Using SQLWays, it is possible to run the full database transfer in evaluation mode, review data, and run applications connected to the new MySQL database:
This is general workflow:
1.Run full database transfer in evaluation mode
2.Check transfer errors, compare tables structures, the number of rows in Oracle and
MySQL
3.Review and test data in all or representative tables using SQL tools such as Oracle
SQL*Plus, MySQL Query Browser, or the mysql command line utility.
4.Run and test the target application connected to MySQL
Challenging Data Migrations
Although, in general, data/DDL migration is relatively easy compared with business logic conversion, there are some conditions that commonly increase the complexity of a
data/DDL migration:
•Large volumes of data
If you need to migrate large volumes of data, you may need more effort to configure MySQL servers.
A large amount of data may impact the migration process, particularly in terms of the time it takes to complete the migration. In order to mitigate the time required to complete the migration, you might execute the migration in a concurrent fashion. This increases the complexity of the migration.
Also, the transfer of large volume of data can complicate error handling, as you now cannot afford to re-run a full migration if just a few tables fail.
The project might benefit from tools that allow for bulk insert options, as tools that issue a commit after each row may be no longer be a viable option.
•Minimal downtime
In some mission-critical environments, you have to ensure downtime is kept to a minimum. To meet these requirements, you must properly design the migration process to do things such as run data transfer concurrently, or transfer static tables out of the downtime window.  Sometimes replication tools will need to be used to reduce downtime.
•Strict performance requirements

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。