MySQL to Oracle Migration

MySQL database can be migrated to oracle using Oracle SQL Developer software. Oracle SQL Developer has built in migration support to migrate database objects and data from databases like MySQL, Microsoft SQL Server, Microsoft Access and Sybase.

Oracle SQL Developer can be downloaded from http://www.oracle.com/technology/software/products/sql/index.html

 
Before actual migration process starts we have to prepare the environment for migration. This involves two steps



Setting up migration repository : 

The migration process requires a repository to store the meta-data it collects and transforms for the migration process; the repository is a database schema where we are going to store these meta-data. The migration repository consists of 37 tables, 8 views and the primary keys and indexes required, along with triggers and pl/sql code.
Oracle recommends configuring a dedicated database schema for migration repository (this is not compulsory). The migration repository schema should have the following role and system privileges:
i) RESOURCE
ii) CREATE SESSION
iii) CREATE VIEW
We can use any tool to create the schema. In this example I am using SQLPLUS to create the schema and to assign the roles and privileges to the schema.

 I have created the user MIGRATION. I am going to install the migration repository on this user. To do this first create a connection in Oracle SQL Developer using this user.

Right Click on Connections and click on New Connection

Enter the connection settings. The name of the new connection is MigrationRepo

 

After connection is established, we can see the connection name in the Connections tree.

 Now I am going to create the migration repository. Go to Migration menu, click on Repository Management and click Create Repository

 It will ask to select Create Repository.

Create Repository is the name of the database connection to use to create a migration repository. The objects associated with the migration repository are created in the schema of the user associated with the selected connection.
In this example it is MigrationRepo. Click Create button to start repository creation process.

Now we have completed the first step of preparing the environment for migration. The second step is to configure the third party database connection.




Configuring third party (MySQL) connection:
Now we have to configure a database connection for the database which we will migrate. There are two steps to this process

a) Configuring JDBC drivers
Download the JDBC driver for the third party database. In our example we will download JDBC driver for MySQL database. It can be downloaded from the link:
http://dev.mysql.com/downloads/connector/j/5.0.html
The filename is mysql-connector-java-5.0.4.tar.gz
Once downloaded extract the binary jar file to a folder. The driver binary jar file is in the extracted folder and for MySQL the name is mysql-connector-java-5.0.8-bin.jar

Now we have to give Oracle SQL Developer the location of this file

Click on Add Entry

Locate and select the JDBC driver

The driver is added; click OK to finish this step.

 

b) Create a Third Party (MySQL) Connection:

Go to the MySQL tab and enter the connection parameters and connect

This will finish our task of setting up the environment for migration.
 
 
 
Now we are going to start the actual migration process. This process should be repeated every time we want to migrate.

Following are the steps of migration process:



Capture the third party database:

This step is used to capture a snapshot of the current state of the third party database (MySQL). For migration process the Migration Workbench needs a "point in time" view of the third party database and this step provides that. After the snapshot is captured, the Workbench works on the meta-data stored in the migration repository, it doesn’t issue queries against the MySQL database.
i) Right click on the MySQL connection and if it is not connected connect it.
ii) Right click and select Capture MySQL to start capturing.

This will capture all the databases in the MySQL server.

If we need to migrate a specific database from the MySQL server that can also be done. In our example my MySQL server has many databases but I have to migrate the guwahati database. Select guwahati database and right click on it and click on Capture Schema

guwahati database has been captured; I can see it in the Captured Models window.

Convert Captured Model

Captured model is in third party database format. We have to convert the captured model to an Oracle-specific model. Right click on the captured model and click on Convert to Oracle Model

Now the data type conversion window appears. Here we may specify the data type transformations that may be required to convert MySQL database specific data types to Oracle supported data types. It is safe to accept the defaults in most cases. Click Apply to start the conversion process.

Conversion Process:  

After conversion process is completed, the database will appear in the Converted Models window. It is now in Oracle model.

Generating SQL Scripts for the Converted Model

After the Converted Model is generated; the Migration Workbench has a structure of the converted database. We will use this to generate SQL scripts to create the schema(s) and objects in Oracle database.

 SQL Script generation process:

Once SQL generation is completed, Close Generating Oracle SQL window.



The generated SQL will be opened in a new SQL worksheet window.

Create a connection to run the generated script
The generated SQL script needs to be run with a user which has CREATE USER right.


I am going to add another connection in SQL Developer to Oracle database with an user having CREATE USER right

I have created a new connection SystemUser with the Oracle user system.
This step is can be avoided in two ways:
a) We can remove the create user statements from the script and manually add the users and then run the script.
b) We can remove the statements for creating and connecting as new user if we wish to create the database within an existing user's database schema.


Run the generated script

Press F5 to run the script to run, or choose the "Run script" icon from the SQL Worksheet toolbar

Select the connection to use for running the script. In my example I am using SystemUser connection

Script running process

Check the script output to find if the script has ran correctly. Sometime we may have to do some manual adjustments to make it run correctly.

Migrating Data to Oracle

This is the last step in the migration process and in this step the data is copied from third party database to Oracle.
It is done as follows:


Create a Connection to the new Database Schema (created by the previous step by the script)

In the previous step, the SQL script created a schema guwahati , I am going to use that user in this connection

Migrate the data

Go to Migration menu and select Migrate Data

Now we have to provide information for data migration

  
Source Connection: Database connection from which data is to be migrated (In this example the MySQL connection MySQL5).
Target Connection: Database connection to which data is to be migrated (In this example the connection NewConn we just created in the previous step).
Converted Model: Converted model containing tables whose data is to be moved to the corresponding Oracle database tables (Select the model we have created in step 2).
Use qualified names from converted model for insert: If this option is checked, object names are qualified by the schema name.






Click OK to start the data moving process. This process will use a number of parallel connections to transfer the data.

Check the Migration log to find if any error has occurred in the migration process.

After this process completed successfully, data should be in the newly created schema of Oracle database.

জয় আই অসম,

প্রণব শর্মা