Everybody who needs to migrate database from Oracle to MySQL or backward should explore benefits and disadvantages of each database migration system before they begin. This whitepaper describes main cons and pros of Oracle and MySQL.
Oracle pros:
- Complex security system. Oracle works with groups or roles while MySQL does not. Oracle offers option to deny a privilege, while MySQL only permits to grant.
- External authentication options like LDAP, Active Directory. MySQL supports only built-in authentication.
- True full-text search. MySQL implements this feature in limited scope and yet it is available for non-transactional storages only.
- Complex administration tools and IDEs for debugging.
- Integration with business intelligence applications.
Oracle cons:
- High cost. The Oracle database server usually costs much more than comparable MySQL solution. The same related to required hardware platform, database administrators and developers.
- Complexity. MySQL is quite easy to learn and use, but Oracle requires deep administration skills to set up and maintain the database.
MySQL pros:
- It’s open source.
- Low Total Price of Ownership. Price of commercial licenses of MySQL is more affordable than same for Oracle. Also, MySQL has less requirements to the hardware compared to Oracle.
- Each table can have a different storage engine that makes overall database configuration more flexible.
- Each storage engine is supplied with its own features, and properties.
MySQL cons:
- Does not support user-defined types.
- You won’t notice any recursive queries.
- Does not support functional columns which are calculated as expression.
- Data definition language statements are non-transactional.
- Does not support materialized views.
- Does not support sequences.
- Some storage engines do not support foreign keys.
If database migration is verified after all conditions, it’s important to have in mind primary differences between these DBMS.
Types
Oracle and MySQL support similar data types and there is safe mapping between them. However, important thing to remember is MySQL does not support user defined types unlike Oracle. Those types must be decomposed into set of trivial built-in types and migrated in that way.
Oracle database objects missing in MySQL
This section lists Oracle database entries that have no direct equivalent in MySQL. Database specialists must emulate any of these missing entries based on the required semantics during database migration from Oracle to MySQL:
- BEFORE trigger
- Package
- Role
- Snapshot
- Synonym
- Tablespace
As you may see, there are lot of syntax and semantic differences between Oracle and MySQL. Due to this fact manual migration may take much time and efforts or even causes data loss due to human errors. It is reasonable to use special software for automating the migration process when migrating large and complex databases. Intelligent Converters company offer products to migrate Oracle to MySQL and backward. Both products migrate all database objects except stored procedures and triggers. Also, it and support command line arguments to automate, script and schedule the database migration procedure.
