|Oracle Migration Workbench Reference Guide for MySQL 3.22, 3.23 Migrations
Release 9.2.0 for Microsoft Windows 98/2000, Microsoft Windows NT and Red Hat Linux 6.2
Part Number A97249-01
This chapter introduces the migration process by outlining the architecture of both MySQL and Oracle. It includes information on the following:
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.
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:
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.
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.
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.
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.
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.
You must back up the MySQL database files before using the Migration Workbench Capture wizard to migrate to Oracle.
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.
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:
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.
To create the mysqldump data extraction script and the SQL*Loader control files for all tables:
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:
Host name of the system connecting to the MySQL server
MySQL user name
Password of the MySQL user
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.
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:
<DESTINATION_PATH>section. For instance, the destination path would be indicated as
<DESTINATION_PATH>section of the dump_extract.bat file, such as
%ORACLE_HOME%\Omwb\sqlloader_scripts\MySQL\<timestamp>\Oracledirectory to insert data files into the equivalent Oracle tables.