Moving from MySQL to an Oracle Database Server

I was asked recently in one of my blogs what are some of the pure technical advantages of moving from MySQL to Oracle. I kind of laughed when I read the question because nothing is likely to stir up more perspectives and emotions than asking about moving from one database server vendor to another. So here goes ten technical considerations of moving to an Oracle database server from a MySQL server. I'm keeping the list to pure database server technology. There are also a lot of reasons to move to MySQL as well from other database server vendors (my whole blog is on this).

Technical Advantages moving from a MySQL Server to an Oracle database server

1. Scalability: Much higher vertical scalability and higher OLTP transaction capability. Ability to set up shared server architecture for more user connection scalability.
2. Metadata: Lots of very detailed metadata information as well as wait events for tuning.
   
3. Stored Routines: Compiled stored procedures with much higher scalability. Java and .NT stored procedure capability.
4. SQL functionality: Lots of SQL functions and analytical SQL functions. Lots of XML functionality built into database server.
5. Backups: Hot online backups with Oracle's Recovery Manager
6. Automatic Management: Lots of automatic management with Automatic Database Diagnostic Management (ADDM), Advisors, Automatic Workload Repository (AWR) , Automatic Segment Management, Shared Memory Management, etc.
7. Data Warehousing: Bitmap indexes, materialized views, analytical functions, star joins, sort-merge joins, hash joins, etc.
   
8. Grid Control: Oracle Enterprise Manager is a very robust enterprise tool for managing and monitoring multiple tiers of an Oracle infrastructure.
   
9. Inexpensive disk strategy: Striping and mirroring capability with Oracle's Automatic Storage Management (ASM).
10. Flashback technology: Lots of recovery capability with Flashback technology such as Flashback Database, Flashback Table, Flashback Versions Query, etc.

Anyway, a few things to talk about over coffee and cookies. :)

Differences between Oracle and MySQL

Some key differences for DBAs between Oracle and MySQL database servers include:

• Different tools used to manage and monitor database servers.
• Oracle architecture is process based, MySQL architecture is thread based.
• Different tools used for backup and recovery.
  
• Database specific SQL syntax.
• Database specific SQL functions.
• Different syntax for stored routines. MySQL has no packages.
• MySQL routines are not compiled and run in each session thread and not in global memory.
  
• MySQL only supports row-level triggers.
  
• Different startup and shutdown processes.
• Oracle RAC is a shared disk solution while MySQL Cluster is a shared nothing solution.
• The default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases.
• With Oracle, the CREATE DATABASE command is used to create the physical storage for the database server.
  
• With MySQL, the mysql_install_db script is used to create the physical storage for the database server.
  
• The term "database" in Oracle means all the physical files associated with an instance.
• The term "database" in MySQL means a schema. The term database and schema can be used interchangeably in MySQL.
• In Oracle a database user owns all the schema objects.
• In MySQL the database schema owns all the schema objects.
• Oracle supports role based security. With MySQL, scripts and stored routines are used to programatically organize security permissions.
  
• Oracle has a lot more feature functionality that makes it very popular.
• MySQL has a lot less functionality than Oracle that makes it very popular.
• Oracle has tons of options for creating a table. Different characteristics and behavior are defined with these options.
• The key with MySQL tables are defining a storage engine with a table. The storage engine defines characteristics such as row level locking versus table level locking, referential integrity, support for different types of indexes and features. Different optimizations, storage, tuning and backup and recovery are required for each storage engine. Storage engines are a KEY feature in MySQL.
  
• MySQL supports different ISOLATION levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ AND SERIALIZABLE.
  

Oracle DBAs are not just managing Oracle databases any longer. They are supporting, SQL Server, MySQL, Business applications, application servers in a lot of Oracle environments. The more software you can support in an organization the more marketable you are. Its important that Oracle DBAs be able to support the corporate environments that are running database software from multiple vendors and the software surrounding databases.

There are reasons that MySQL customers like the light weight, easy to use MySQL database server. If an Oracle DBA compares MySQL to Oracle feature by feature they are going to be very disappointed. An Oracle DBA needs to look at the feature/functionality that makes MySQL so popular. The feature/functionality is not tons of features, its that it is light weight, fast and easy to manage.

The closest analogy I can make to an experienced Oracle DBAs, is that a MySQL database server is similar in management to what an Oracle version 7 database was like. MySQL uses cache hit ratios, has minimal wait events and DBAs still need to write scripts to surround the MySQL database.

Starting up and Shutting Down a MySQL Server

There are different ways for starting up and shutting down a MySQL server. A MySQL server when it starts up it starts up all the way. When a MySQL server shuts down it shuts down all the way. MySQL cannot startup and and shutdown at different levels the way an Oracle database server can.

MySQL Optimal Configuraton Architecture

Oracle DBAs understand the importance of defining guidelines and standards and using them in the configuration and management of database servers. Years ago the Optimal Configuration Architecture (OFA) developed a base set of guidelines and naming conventions for Oracle DBAs.

I developed the MySQL Optimal Configuration Architecture (MOCA) modeled after OFA to give new MySQL DBAs a set of guidelines and standards to consider when configurating MySQL database servers.

Managing MySQL Storage

Storage needs to be managed for any MySQL instance. A MySQL database server does not have the same flexibility as an Oracle database server in terms of how to lay out the data storage. It is important to learn the data storage defaults and options.