1. Introduction
As you
might imagine, migrating a database and its attendant applications is no small
affair. Throw into the mix a switch of database platform, and you
certainly have your hands full. In this two part article, we’ll be
discussing all of the steps involved in this process, questions to ask
yourself, what to watch out for, and how to perform the whole thing within a
limited window of downtime. At the end, we’ll go over the process of
"upgrading" your skills, be it through books and publications,
forums, training, or certification.
2. Assessment of Migration Plan
Before
you do anything, you will need to put together a plan or outline of what’s to
be done. I know, I know, no one likes to write documentation. But
it’s an important step because on the tech side, it will force you to determine
what needs to be done, and on the management side, it will illustrate
step-by-step the process, so they can see the complexity, consider business
impact, cost of requisite systems, resources required to execute the migration,
and overall timeframe.
What
should this assessment include specifically? Well first, you should
investigate what version of MySQL you’re using currently. Do a review of
what features you’re using, such as datatypes (numeric, character, enumerated,
etc), what type of storage engines you’re using such as MyISAM, Innodb, and so
on. Consider the index types you’re using, and additional features such
as Views and Triggers that may be in use inside your database. Also,
audit the stored procedures you have in your database, as these will need to be
rewritten. Consider MySQL replication carefully as well. If it is
setup for the whole db, it will match more closely to Oracle’s standby database
or DataGuard feature, whereas if you’re using table by table replication with
the wildcard syntax in your my.cnf file, this will more likely be done using
materialized views (formerly called snapshots) in Oracle.
On the
Oracle side, you’ll need to look of course at the version you’ll be running,
how the various MySQL datatypes, and objects map into Oracle. Consider
the autoincrement attribute on primary keys. Although Oracle does have
sequences, sequences differ slightly from how autoincrement works in
MySQL. There is no guarantee of consecutive numbers, for instance. Therefore,
you may experience gaps in your key values based on how Oracle caches
sequences. Consider how this might affect your application’s
behavior. Although not directly part of the migration process, you’ll
need to consider how you want to backup your Oracle systems as well. The
export facility fairly closely matches mysqldump on the MySQL side, and RMAN of
course provides all the hotbackup functionality you might find in utilities
like innobackup or the underlying ibbackup available with MySQL.
In your
assessment document, make a note of all of these findings, and be as thorough
as possible. The more you do the due diligence now, the fewer gotchas you’ll
have later on in the process.
3. Development Migration and Setup
3a.
Preparation
Preparation
will involve setting up your development environment. That includes
requisitioning the right hardware, setup at the data center, installation of
Oracle software, and building your starter database. Consider Oracle’s
best practices when doing this setup, such as using RAID 10 for your underlying
disk storage, layout your redologs, allocate sufficient system and sysaux tablespace
and so on. You’ll also want to consider the character set of your new
database. Beware of US7ASCII as it can limit you from European and
accented characters. Better to choose WE8ISO8859P1. If you may be
using Asian characters in your database, you’ll have to look into the National
Language Services (NLS) features of Oracle, and consider multi-byte character
sets. You’ll also create tablespaces for data and index. Consider
the size of your source objects. At the filesystem you can use "du -sm
dir_name" of your data directory. Give yourself another 25-50% space
to make room for growth.
3b.
Database Structure Migration
On the
MySQL side, dumping the structure of a database (which becomes a schema in
Oracle) you can do the following:
$ mysqldump --no-data my_db_name > my_db_name.mysql
Because
of slight differences in syntax, you cannot run this script as-is in
Oracle. What you can do is rewrite these CREATE TABLE and CREATE INDEX
statements yourself. Obviously if there are huge numbers of objects in
your database, you’ll want to consider an automated way to do this. We’ll
discuss Oracle’s Migration Workbench in part II of this article, but for now we
recommend running through the objects one by one. This will also provide
a sort of inventory of objects in your database, and force you to be thorough
and pour through them all in turn. It will also be prudent to get
developers involved in this process. By running through the tables and
objects to change their syntax, they will on the one hand brush up, or get up
to speed on Oracle syntax, and on the other, really comb through the objects to
consider what changes might be required in their application code.
Ultimately,
application changes will be a large part of porting to a new database engine,
no matter how diligent your developers may have been in writing database
independent code. So again, this manual process will force a fine tooth
comb upon the intricacies of your database.
4. Conclusion
Yes,
migration of a database application to a new platform like Oracle is no simple
task, but it can be done, and with proper preparation, it can be managed.
Do a proper assessment of your current systems before any systems setup, and
review the time, hardware, software, and people requirements with management
before moving forward.
In part
II of this article, we’ll discuss moving the actual data from production,
review Oracle’s Migration Workbench which may save you some time, and discuss
potential application issues, and challenges. We’ll then review the whole
process, and carefully plan the final migration of your production database and
applications.
