2
Migration Process

This chapter introduces the migration process by outlining the architecture of both MySQL and Oracle. It includes information on the following:

MySQL Architecture

MySQL is an open-source relational database management system (RDBMS). It uses a client/server architecture and is a multi-threaded, multi-user database server. It was designed specifically to be a fast server; therefore, it does not provide many of the features provided by other relational database systems, such as subselects, foreign keys, referential integrity, stored procedures, triggers, or views.

The access privilege system used by MySQL is designed to give comprehensive security to the data; however, it does require some configuration. The server provides concurrency control so different users cannot modify the same data at the same time. The locking mechanism is not adequate for tables with a lot of write actions from different users at the same time.

MySQL provides a number of client tools, the most commonly used are:

mysql - An interactive client that allows you to issue queries on databases and view the results
mysqldump - A tool that allows you to extract the schema and data within a MySQL database and place into a file
mysqlimport - A tool that allows you to read the schema and data from a file and place into a MySQL database
mysqladmin - A tool that allows you to perform administrative tasks, such as creating databases and dropping databases

MySQL is free to use for many different platforms, including Linux. It has ODBC support for Win32. It also has APIs for C, C++, Java, Perl, Python, and PHP.

Oracle Architecture

Oracle9i, Oracle8i, and Oracle8 databases are powerful, flexible, and scalable relational database management system (RDBMS) servers, that run on a range of computer systems, from personal computers to the largest mainframes. Oracle has been designed to run effectively in a client/server environment and supports hundreds to thousands of users.

The Oracle architecture supports advanced server features, such as record locking with versioning, advanced query optimization, the PL/SQL programming language, data replication, distributed database management, and other important features.

The architectural features described in this chapter are only a few of the features provided by Oracle. The features described are focused on the elements that pertain to working with MySQL. Refer to the following Oracle Server guides for a complete description of the Oracle architecture. These guides can also be found in online format on CD-ROM:

Oracle9i Database Concepts Release 1 (9.0.1)
Oracle9i Database Administrator's Guide Release 1 (9.0.1)
PL/SQL User's Guide and Reference Release 1 (9.0.1)
Oracle9i Database Error Messages Release 1 (9.0.1)

Triggers and Stored Procedures

Oracle allows you to write and store code in the DBMS along with data. You can associate trigger code with an UPDATE, INSERT, or DELETE event for each row or for a table as a whole. You can also set a trigger to run before or after the event. For example, you can set a trigger to run after any row is updated. This feature is not available in MySQL.

A stored procedure is a general routine, either function or subroutine, that is stored in pre-compiled form on the server. A trigger may call stored procedures, but triggers are only activated by specific database activity, such as the insertion of a row in a table.

PL/SQL Programming Language

The PL/SQL Programming Language is an ALGOL-based language like Pascal. PL/SQL is a modern, full-featured programming language with exception handling. You can use PL/SQL to write stored programs and triggers in Oracle. It is also the programming language used in many of the client-side tools available from Oracle, such as Forms from the Oracle Developer suite of products.

Sequences

A sequence is a unique number generator that is implemented in shared memory on a server. It is designed to provide a set of unique values for PL/SQL programs for use as primary keys. Sequences are designed for high performance applications that may otherwise `single-thread' on table-based unique number generators.

Transactions

Oracle supports an implicit transaction model. Each SQL statement is part of a logical transaction. A logical transaction begins with the first SQL statement and ends with a COMMIT or ROLLBACK statement. Immediately after either of these statements, a new transaction takes effect with the next SQL statement.

Other Oracle Features

A database administrator has great flexibility when configuring Oracle. The administrator can write data on multiple disks for increased performance, tune rollback and recovery options, and allocate computer resources to optimize the configuration for each server. Oracle also supports distributed processing, so data can be distributed across multiple systems. Oracle offers a version of the server called Trusted Oracle Server for applications that require a higher level of user and use authentication.

Preparing for Migration

You must back up the MySQL database files before using the Migration Workbench Capture wizard to migrate to Oracle.

Extending the Application

After you move the data management portion of the MySQL application to Oracle, you can rely on Oracle to protect the data and maintain all referential integrity and business rules that you have encoded in PL/SQL.

With this foundation, you can extend the application with a wide range of tools. Oracle offers several high-productivity tools in the Oracle 9i Application Server, such as Oracle Portal or Oracle JDeveloper, and Oracle Objects for OLE.

In addition, if the application grows, you can move the Oracle server to larger computers without changing the application.

Using Offline Data Loading

You can use mysqldump, a client program shipped with the MySQL server, to output the schema and data of a MySQL database into .sql/.txt files in various formats. The Migration Workbench uses mysqldump in conjunction with SQL*Loader to provide an offline data loading capability for large tables. The following topics explain the process of offline data loading:

Script Directory Structure

The %ORACLE_HOME%\Omwb\sqlloader_scripts directory stores all data extraction scripts. Within this directory there is a sub-directory called MySQL that stores the SQL*Loader script output for MySQL. Within the MySQL directory, the Migration Workbench creates a directory using <timestamp> that represents the date and time you generated the SQL*Loader scripts. For example, a sub-directory called 1-10-99_17-56-16 indicates that you generated the scripts at 17:56 P.M. on 1st of October 1999.

As part of the Generate SQL*Loader Script command, a subfolder called Oracle is created in <timestamp> directory. The Oracle directory contains SQL*Loader control files and a SQL*Loader script called sql_load_script.bat. The SQL*Loader control files and the data files that you create should be located in this directory. Therefore, you should copy the dump_extract.bat file into the Oracle directory before executing the sql_load_script.bat file.

Generating mysqldump Data Extract Scripts

To create the mysqldump data extraction script and the SQL*Loader control files for all tables:

From the Oracle Model, click the Tables folder in the Oracle Model.
Choose Object -> Generate SQL*Loader Scripts.

Note:
You can also generate the scripts for a specific table by highlighting that table from the Oracle Model, then choosing Object -> Generate SQL*Loader Scripts.
If you are sure you want to generate the SQL*Loader scripts for the tables specified, click Yes.
After noting the location of the SQL*Loader scripts, click OK.

Using the Extracted Scripts

After generating the SQL*Loader scripts, you can use them to load the data into the Oracle database.

A description of the command line for each table in the dump_extract.bat file is as follows:

Code	Description
HOST	Host name of the system connecting to the MySQL server
USERNAME	MySQL user name
PASSWORD	Password of the MySQL user
-T	option for the mysql dump creates a .sql file that contains the SQL CREATE commands for the given table as well as a .txt file that contains the data for the given table.
-fields-terminated -by=<ec>	Used with the -T option to give a value to use as the closing value for each column - ec ends each column data for each row.

To use the scripts within the data extraction directories to execute a manual data extraction:

Add the host name, user name, and password to the dump_extract.bat file for connection to the MySQL server. This is indicated by the following tags: HOST, USERNAME, PASSWORD.

Note:
You must use a root user name and password or another user with DBA privileges.
Specify the output for the dump_extract.bat files by adding the destination path in the <DESTINATION_PATH> section. For instance, the destination path would be indicated as %ORACLE_HOME%\Omwb\sqlloader_scripts\MySQL\<timestamp>\Oracle.
Run the dump_extract.bat file to generate the data files. This extracts the data from the tables into data files called <table_name>.txt

Note:
Running the dump_extract.bat file also creates the <table_name>.sql files. However, they are not needed because the .CTL files also contain the table schema.
Move the data files into the destination path that you specified in the <DESTINATION_PATH> section of the dump_extract.bat file, such as %ORACLE_HOME%\Omwb\sqlloader_scripts\MySQL\<timestamp>\Oracle directory.
If the destination Oracle database does not reside on the same system as the Migration Workbench, you should FTP the entire destination directory to the same system as the destination Oracle database.
Execute the sql_load_script.bat file from the %ORACLE_HOME%\Omwb\sqlloader_scripts\MySQL\<timestamp>\Oracle directory to insert data files into the equivalent Oracle tables.

Note:
The migration of BLOBS is not supported by the scripted data move. You can remove the \n character is it exists in CLOBS.

2 Migration Process