All statistics on the visits to the posts in the AMIS Technology Weblog are gathered in a MySQL database that sist underneath WordPress. We would like to do some analysis on these statistics, for several reasons: to try out the Oracle Warehouse Builder 10gR2 Beta Paris Release we have currently available, to develop a number of articles and demos around Oracle BI technology in general and of course to learn more about the visitors to our weblog: where do they come from, what days of the week do they visit, which times of the day (when is the best time for us to do maintenance, like an upgrade of WordPress to release 1.5), what topics are they primarily interested in, which browsers do they use etc. This analysis for obvious reasons is to take place from within an Oracle database. That means we have to migrate the data from MySQL to Oracle. In this post, I will roughly describe how such a migration can be done quite easily using the Oracle Migration Workbench (OMWB).
Step 1: Download all required software and documentation
I started out by downloading the Oracle Migration Workbench from OTN as well as the MySQL 4.0 plugin. The Reference Guide on Windows is here as well as the Release Notes. All Migration Workbench Documentation. I had to download the MySQL Connector/J 3.0.x driver as well (from MySQL’s Site).
Step 2: Install the software
- Install Oracle Migration Workbench by extracting the zip-file
- Install MySQL plugin – copy MySQL4.jar to plugins folder
- Create a zip-file for a specific part of the MySQL Connector/J driver and copy it to the MySQL drivers folder
Step 3: Prepare for Migration
- Create a OMWB Repository User – to store meta-data for the Captured Source and the to-be-created target. Following instructions in the doc and connected as SYSDBA in SQL*Plus, I created a database user. Note that when migration is complete this schema can be removed again.
- Ensure that the MySQL (source) database is available as well as the Oracle target database
Step 4 – Run the Oracle Migration Workbench
Run the Oracle Migration Workbench and connect as the Repository User. OMWB will proceed to create a number of tables to hold meta-data.
Run the Capture Source Wizard – this will read the meta-data for the source database: table definitions, constraints, views, triggers/stored procedures (n/a for MySQL 4.0).
Connect to the source (MySQL) database:
Review and edit the default mapping between MySQL constructs and their Oracle equivalents:
The Capture Wizard is populating the Oracle (target) Model from what is read from the Source Model:
OMWB will propose a default mapping between MySQL data-types and Oracle equivalents – it will create a design for the target Oracle database derived from these defaults. You can take matters into your own hands and override these defaults, for example to convert a specific VARCHAR column not to VARCHAR2 but to CHAR or CLOB instead. OMWB will propose to create triggers and/or sequences for the MySQL columns specified with AUTONUMBER .Next, Run the Migration Wizard.
This wizard will take the details of the Source Model as well as the Oracle (target) Model set up by now in the OMWB Repository and run the actual migration. The wizard presents a summary of what it will do:
It will then continue to create database objects and if so desired also migrate the actual data.
This screenshot shows the first stage in the migration: creation of tables, views, constraints etc. The second stage – the actual data migration – is depicted next:
Note that the migration of over 300k records took less than 5 minutes once the source model was loaded.
It is a bit late in the day, but at this point we can take a look at the meta-data held in the OMWB Repository – the Source Model and the Oracle (target) Model. We could have refined it before starting the migration. Of course, we can still remove the migration result (the target schema) and re-run the migration.
Conclusions
Using the Oracle Migration Workbench was an unexpected pleasure. It was easy to install, easy to use and it delivered the goods. In under two hours, I had installed all software, captured the source design, created the target database objects and migrated all data, some 50Mb worth of WebLog statistics. We recently used the OMWB also for an Access to Oracle migration (see post Oracle Warehouse Builder 10gR2 (beta), a first impression), and there too our findings were positive. It is clear that Oracle invests in tools that provide very easy migration from 3rd party databases to Oracle. Needless to say Oracle is not in such a hurry to provide tools for the reverse operation.
Alternatively: use MySQL ODBC driver and Oracle’s Generic Connectivity that can work with ODBC
Instead of performing this one-off migration from MySQL to Oracle, I can try to set up a live-link, that would allow me to live query MySQL from within my Oracle database. If I set up the ODBC based Generic Connectivity link from Oracle, I can create a Database Link in the Oracle database that allows we to directly access the tables in the MySQL Database. Oracle Warehouse Builder for example can use these tables as data sources for ETL operations that transfer data – insert and update – into the Oracle database (data warehouse).
See for example: How to Setup HSODBC to MySQL – (Solaris to Linux). For an example of the Generic Connectivity on Windows, see: December 3, 2004 Making a Connection from Oracle to SQL Server By James Koopmann. Note that in a similar way you can get access from your Oracle database to Excel sheets, Text and CSV files, Access Databases and Foxpro. A description intended for Oracle Discoverer but generally applicable to create a database link to an Excel Spreadsheet is found in Discoverer and Heterogenous Data Services
hi, sorry for a silly question, where can i get a MySQL4.jar plugin???
thanx
I ‘ve tried AndyBr’s Solution, it really works after trying it.I am not sure about the SID that i should enter on the Migration part Step 1of 4. Is this the SID of the new Oracle Database to be created? Any 1 with da answer?
Safi.
I was quite frustated using this OMWB because connection problem to MYSQL 5 db
After wasting hours of sleep time & bandwidth, I found another method using Connector/J
1. Download ‘mysql-connector-java-3.1.14.zip’
2. Extract it
3. Find ‘mysql-connector-java-3.1.14-bin.jar’ from the extracted dir
4. Rename the file mentioned above to mysql-connector-java.zip (only one file !)
5. Copy the file to ‘omwb/drivers’
6. Run omwb
7. Get some beer
hope this help,
AndyBr
I am also getting the “database not found” problem. If any one has found a solution, please email it to me at r e g i s t e r AT m a r k e b e r h a r t DOT c o m
Thanks,
Mark
I am also getting the same error “database not found”. Can someone send me the correct driver file which is working fine at jain_amitkumar@rediffmail.com
Thanks
Amit
hello there,
i am trying to reproduce a data server that a company uses to present information to its customers via the web. No problem. What they are using right now is Microsoft Access, connecting via ODBC to a server on line. The cool thing that they can do is have several tables linked together. Like if a certain field is updated in one table, it updates the same info automatically in another table. So, i want to reproduce this in MySQL, but i can’t seem to find out how. Is there a way to pull this off on the server side ? or does that have to be done on the client side ?
thanks for any tips.
I’ve the same message that John and Mei : “database not found, make sure hostname and port number …†.
John and Mei have you find solutions. I’ve ever tried to restart ombw many times… If you can help me
I have the same issue immediately following installation. After restarting the client, I am able to connect to MySQL from the OMBW Capture Wizard.
after following your instruction on zipping the myl-connector file, i am still getting the “database not found, make sure hostname ….”
like john, i can connect to mysql on my pc with hostname=localhost, so i don’t understand why the database cannot be found.
would appreciate it if you could help.
also, there is no”mysql-connector-java-3.x.xx-stable.zip” file, only a “mysql-connector-java-3.x.xx-ga.zip” file that can be downloaded.
thanks
mei
Extract the mysql-connector-java-3.x.xx-stable.zip file.
From the com subdirectory zip the extracted files.
NOTE :Ensure that the zip file contains the path information beginning with the com path name.
Rename the zip file to the following:
mysql-connector-java.zip
can anybody explain the working of oracle to sql connectivity
using odbc digramatically.
i know how to make this connectivity but not knowing that how does it work?
i want to know what is exact role of adding listener at listener.ora file
Would you please detail the step2
Step 2: Install the software
Install Oracle Migration Workbench by extracting the zip-file
Install MySQL plugin – copy MySQL4.jar to plugins folder
Create a zip-file for a specific part of the MySQL Connector/J driver and copy it to the MySQL drivers folder
I have difficult to install the Connector/J. the Installation Guide from Oracle is not in detail. And I try. It always said:
“database not found, make sure hostname and port nuber …” I believe the hostname and port number, usr name and password are correct, because I can connect to the database using the same information through SQL yog.
Thanks
John
Marc,
Thanks that is useful commentary. I had not actually tried it, but it did feel like somewhat farfetched. What would you think of an alternative along the following lines:
– write a Java Class that – through JDBC – can query MySQL data
– load that Class as Java Stored Procedure in Oracle
– write a PL/SQL Table Function that calls this Java Stored Procedure and returns the data it returns as Nested Table
– use this PL/SQL Table Function in Oracle based SQL Queries, for example: select * from table(my_sql_querying_function(query criteria))
Lucas
FWIW, for anyone reading this, I would not put too much faith in trying to use the hsodbc functionality to talk to mysql if you have any non-trivial amount of data. Due to the combination of how Oracle makes certain ODBC calls with the fact that the MySQL ODBC driver doesn’t actually support things like prepared statements, but just tries to fake them, you end up with nasty surprises. For example, when you execute any query against table “foo”, the result will be executing a “select * from foo” when Oracle tries to get column information, and having the mysql ODBC driver cache the entire results set (ie. entire table) in memory.