Introduction

MySQL-to-Oracle is a program to move MySQL databases to Oracle server.

Features

  • All MySQL data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Merges MySQL data into an existing Oracle database
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Converts individual tables (see Select Tables article for related information)
  • Filter data to convert via SELECT-queries (see Using Queries article for related information)
  • Converts indexes with all necessary attributes
  • Synchronize Oracle database with MS Access data
  • Converts foreign keys
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Windows 2000/XP or Windows NT 4 with Internet Explorer 5 or later Windows 98/ME or Windows 95 with Internet Explorer 5 or later
  • At least 32 MB of available memory
  • Oracle client and networking components should be installed on the same machine where you run MySQL-to-Oracle from

Limitations

  • Does not convert stored procedures


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to Oracle server as if it would be a regular table. Select name of table where you wish to store query results in the desintation Oracle database. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can always check query by running it from MySQL client.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call the conversion procedure from an automation script you can use console version of MySQL-to-Oracle S2OAGENT.EXE. Find this file in MySQL-to-Oracle installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--blob2text | convert BLOB to text
--dest=... | Oracle service name
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process tables that already exist in Oracle database (0 - overwrite, 1 - merge, 2 - skip)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--oraclu=... | Oracle user name
--oraclp=... | Oracle user password
--port=... | MySQL port
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MySQL database name
--tab_def | convert table definitions only
--tab_file=... | name of the file containing table names to convert (one table name per line)
--uppercase | make names uppercase

In the following example the program converts MySQL database "db1" on the remote MySQL server "mysqlhost" into Oracle database "db1_from_mysql":

S2OAGENT.EXE --src=db1 --dest=db1_from_mysql --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword --oraclu=scott --oraclp=tiger

Table names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Notes:

  1. You can omit 'mysqlh' to connect local database server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. The default mode is 'overwrite'. You can change it using '--mode' parameter
  4. If you omit 'tab_file' parameter, all database tables will be converted
  5. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MySQL-to-Oracle is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL-to-Oracle from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-Oracle. See Troubleshooting article to learn how to resolve this problem.


Connect to Oracle Server

On this wizard screen you should enter the necessary settings to establish connection with Oracle server.

  • Server - IP address or network name of Oracle server machine. If the converter has been installed on Oracle server machine, you can specify "127.0.0.1" or "localhost" in this box.
  • Database name - name of Oracle database. Enter the same title that you use with SQLPlus or any other standard Oracle applications.
  • Port - port for connecting to Oracle. It is necessary to specify it only if it differs from default value 1521.
  • Username - your Oracle username for the database to which you are connecting
  • Password - your Oracle password for the database to which you are connecting

MySQL-to-Oracle provides two methods of connecting to Oracle server: directly and via TNSNAME.ORA entry. To use the first method you have to enter information in all fields except the port if it is configured by default (1521). To use the second method you have to leave both "Server" and "Port" fields empty and enter Oracle service name as it appears in TNSNAME.ORA file in "Database" field.

Sometime user enters all Oracle connection settings properly but still may get the following error: "Oracle data source doesn not exist. Check to verify correct name was given.". It means that the program failed to conect to the destination server using the selected method. To bypass this error you can try the following trick. Leave both "Server" and "Port" fields empty and enter in "Database" field the entire connection string as follows:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=IP address of Oracle server)(PORT=1521)))

Of course, you need to replace "IP address of Oracle server" in the sample above with the actual value of IP address.


Conversion Settings

On this wizard screen you should select source MySQL database. Also, you can customize the conversion process using the following settings:

  • Convert table definitions only - select this checkbox to convert only table definitions of the original MySQL database
  • Skip converting indexes - select this checkbox if you don't want to convert indexes
  • Make names uppercase - Oracle accepts lowercase names but you have to enclose them in quotes in queries. To avoid this problem you should select this checkbox.
  • Convert BLOB to text - Sometimes MySQL database authors define text columns as BLOB (for example, in order to perform case-sensitive comparison). In this case you should select the checkbox to obtain better conversion results.

MySQL-to-Oracle allows you to choose how to process MySQL tables that already exist in the destination Oracle database: overwrite, merge or skip converting existing tables.

  • Select 'Overwrite' option to replace the contents of existing Oracle tables with MySQL data.
  • Select 'Merge' option to merge MySQL data into the destination Oracle database. MySQL-to-Oracle implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into Oracle database only those records whose key/index value does not exist in the destination Oracle table. Otherwise, MySQL-to-Oracle appends the whole table contents to the corresponding Oracle table. Note: the structures of source and destination tables must be identical.
  • Select 'Skip' option to not convert existing tables.
  • Select 'Synchronize' option to update Oracle table using MySQL data. See Synchronization article for more information.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

MySQL-to-Oracle maps MySQL types into the most appropriate Oracle types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original MySQL database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.


Profile

Profile is a set of parameters that entirely defines the program session. It includes Oracle and MySQL connection information (host, user name, etc) and all necessary settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.s2o) file with left mouse button. This will cause MySQL-to-Oracle wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Select Tables

On this wizard screen you can manage the list of MySQL tables to convert. In the left part of the screen you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MySQL table for converting into Oracle format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MySQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be migrated to Oracle server. See Using Queries article for the further information.


Synchronization

MySQL-to-Oracle implements synchronization as combination of insert MySQL records that are missing in Oracle table and update existing Oracle records with MySQL data. For synchronization purpose source and destination tables must have identical structures and have primary key or unique index defined.

Assume both MySQL and Oracle databases have table "People" defined as below:

    Table1(
    ID Integer NOT NULL,
    FName VARCHAR(50),
    LName VARCHAR(50),
    EMail VARCHAR(50),
        Primary Key (ID)
    );

This table has the following contents in MySQL database:

2 | Andrew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com

In Oracle database:

1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@yahoo.com
After migrating MySQL data to Oracle in "Synchronize" mode MySQL table is kept untouched and Oracle table is modified as (pay attention to the cell marked red):
1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com


Troubleshooting

While working with MySQL-to-Oracle wizard you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-Oracle. You can use OLD_PASSWORD function to make the certain account compatible with MySQL-to-Oracle. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with MySQL-to-MSSQL.

P: Error "There are orphaned records that prevent from creating foreign key..."
R: Foreign key cannot be created because there are one or more child records that do not have a matching record in the parent table. You have to remove these records in order to create the foreign key. The following query will determine if the entries in the child table have invalid foreign key references:

     select <column(s)> from <child_table>
     minus
     select <column(s)> from <parent_table>

where <child_table> - table with the foreign key, <parent_table> - table with primary key referenced by the foreign key, <child_column(s)> - one ore more columns included in foreign key.

P: Error "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating MySQL data into a new Oracle table, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending MySQL data to an existing Oracle table, you should remove the conflicting unique index in the destination Oracle table.

P: Error "ORA-12705: Cannot access NLS data..."
R: The program failed to find specification of database language. You have to specify database language using "NLS_LANG" environmental variable. For example:

    set NLS_LANG=AMERICAN_AMERICA.UTF8

It the error persists, please read the following two articles giving detailed instructions how to diagnose and fix it:

http://www.dba-oracle.com/t_ora_12705_resolution.htm
http://www.dba-oracle.com/t_ora_12705_error.htm

P: Error "ORA-06413: Connection not open"
R: If you run MySQL-to-Oracle on 64-bit Windows, the most probable reason of the error is that executable module sql2ora.exe has been placed into folder "...\Program Files (x86)\..." while Oracle does not allow bracket symbols in client application. Please copy all contents of product's installation folder into another location that does not contain brackets in the path and run MySQL-to-Oracle from there. If the error persists, please contact our support department at service@convert-in.com

P: The application failed to start because OCI.dll was not found.
R: OCI.DLL is supplied by the Oracle Corporation and is a part of the Oracle Version 7.3.4 (or later) client software installation. Check that you have Oracle client and networking components installed on the machine where you run our software from. Also please make sure that the path to Oracle components folder is in $PATH system variable.

P: I cannot uninstall MySQL-to-Oracle.
R: If you need to remove MySQL-to-Oracle product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE sql2ora.log

P: All table names are enclosed in quotes in the destination Oracle database.
R: By default MySQL-to-Oracle converts table and column names in case sensitive fashion. Oracle requires you to enclose those names in quotes in SQL-queries. You can avoid it by selecting 'Make names uppercase' checkbox on 'Step 3 of 5' wizard page.

P: Error "ORA-12500: the listener could not start a server process..."
R: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly. Verify that the ORACLE Server executable is present and has execute permissions enabled. Ensure that the ORACLE environment is specified correctly in LISTENER.ORA. If Oracle server is running under MS Windows NT/2000/XP, check that service 'OracleService<data source name>' (where <data source name> is an actual name of destination Oracle data source) is running on the database server machine.

P: Error "ORA-12505: the program could not resolve service name..."
R: The service name specified in the connection was not found in the listener's tables. Check to make sure that the service name is correct. The services that are currently registered with the listener can be obtained by typing: LSNRCTL SERVICES <listener-name>

P: Error "ORA-12541: no listener..."
R: The connection request could not be completed because the listener is not running on the database server. Start the listener on the destination machine using 'lsnrctl start' command.

P: Error "ORA-12545: connect failed because target host or object does not exist..."
R: Oracle suggest ensuring that the ADDRESS parameters (in the TNSNAMES.ORA file) have been entered correctly; the most likely incorrect parameter is the node name. Ensure that the executable for the server exists (perhaps "oracle" is missing.) If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again. It could be a network connectivity problem. Try pinging the host. This error has been observed on a laptop when the database was local, TCP/IP protocol used but the network card had been removed.

P: Error "Access denied for user: <user-name>"
R: You will not be able to connect to MySQL server until you have the appropriate permissions. Ask your MySQL server administrator give you sufficient permissions to read the source database on MySQL server.

P: Some table names and/or field names in the resulting Oracle database are different from the original.
R: Oracle and MySQL accept different sets of characters for table names and field names. When MySQL-to-Oracle converts a name containing prohibited characters it replaces them with '_' in the destination database.

P: Program freezes on the second dialog while connecting to remote MySQL server.
R: MySQL server cannot resolve network names. Close the program, restart MySQL daemon with "--skip-name-resolve" option and try again.

P: After entering all Oracle connection settings properly I've got the following error: "Oracle data source doesn not exist. Check to verify correct name was given.".
R: It means that the program failed to conect to the destination server using the selected method. To bypass this error you can try the following trick. Leave both "Server" and "Port" fields empty and enter in "Database" field the entire connection string as follows:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=IP address of Oracle server)(PORT=1521)))

Of course, you need to replace "IP address of Oracle server" in the sample above with the actual value of IP address.

If you came across some other kind of problem while using MySQL-to-Oracle, please contact our support department at service@convert-in.com


Using Queries

Queries give you the ability to extract partial data for migrating to Oracle server. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
    ID INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(50),
    LName VARCHAR(50),
    Birthday DATE,
    Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000
Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE Notes IS NOT NULL

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


Contents

Add Query

Command Line

Connect to MySQL Server

Connect to Oracle Server

Conversion Settings

Edit Table

Introduction

Profile

Quick Launch

Select Tables

Synchronization

Troubleshooting

Using Queries