Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ravi.

Asked: December 25, 2000 - 4:30 am UTC

Last updated: March 17, 2023 - 4:53 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Is there a utility that helps us migrate the data from mySQL database to Oracle 8.1.6 database?

Also, is there a strategy one has to follow for migrating from one database to another?

and Tom said...

Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

link needs update

A reader, April 17, 2004 - 2:43 am UTC

Tom Kyte
April 17, 2004 - 9:50 am UTC

otn-> software/downloads -> migration stuff.

Your opinion

Jairo Ojeda, May 12, 2005 - 4:57 pm UTC

Tom, someone tells to my developer team that he won't use a Oracle database to share our data at the web because it is not secure to his web site and there are lot vulnerabilities around Oracle database. He said that we have to develop the web application using mySQL to sharing our data from a Oracle9iR2 database. This sound to me like an authority question, this man do the same in her company, they use Oracle database with their client application an user mySQL to show information at the web site.

We need about three tables on that database, I proposed to use Oracle Streams to propagate the tables from production to the database to share the data on the web but the web application was developed using mySQL.

I want to change his mind, and know the mind of my developer team (ease) but this man appears to had a bad experience with Oracle some time ago.

What do I have to do? Maybe, donÂ’t care about, learn something of mySQL. One of the developers tell me why we must change mySQL to Oracle, if it is secure, it is replicated by a java application from Oracle database, and web sites like Google.com, Nasa.gov, Yahoo.com and Amazon.com use mySQL?


Tom Kyte
May 13, 2005 - 8:35 am UTC

oh ho ho.

thats a good one indeed. sorry, but it did make me sort of smile this morning.

How many actual certifications has mySQL been through?
</code> http://www.oracle.com/solutions/security/eal4certs_0205.html

How many security related features does mySQL have?
http://www.oracle.com/solutions/security/index.html <code>

I would simply ask him for the report by a credible, public, known entity that shows mySQL to be secure and Oracle not?

Those same websites all use Oracle too. And probably lots of other databases.

Thanks

Jairo Ojeda, May 13, 2005 - 11:36 am UTC

Your questions and links will help me, thanks

MySQL vs Oracle

Frank, August 17, 2006 - 7:06 am UTC

I have a coworker telling everybody in the office that we should do away with Oracle because of the licensing cost and that MySQL can do anything that Oracle can do and better. Do you know anything about MySQL so you can make an informed comparison?

Tom Kyte
August 17, 2006 - 9:17 am UTC

otn.oracle.com/xe

Oracle can be just as "inexpensive" as MySQL. Hows that.

lol

Mark, August 17, 2006 - 10:48 am UTC

"MySQL can do anything that Oracle can do and better."

Man oh man, I wouldn't know where to begin... Speaking very roughly, last year's version of MySQL caught up with Oracle version 7 (1992). That is, they finally implemented stored procedures and triggers. I think they got around to foreign keys not much earlier (which I believe Oracle has had since version 7, also).

Take it from a guy who doesn't work for Oracle and has worked with MySQL, PostgreSQL and Oracle. The company I work for is using PostgreSQL exclusively these days. MySQL didn't come close to meeting our requirements. However I'm far from happy with PostgreSQL - see my complaints here - </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1886476148373#67219028766976
and here - 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4685088047969#68344902112056 <code>

In other words, IMO, Oracle > PostgreSQL > MySQL, and I find PostgreSQL to be too wimpy. But it all depends on your requirements obviously.

Not to mention that Oracle owns Berkeley and InnoDB

A reader, August 17, 2006 - 11:13 am UTC

Berkeley DB and InnoDB underly many of the capabilities of mySQL.

xe

A reader, January 09, 2008 - 12:54 pm UTC

TOm:

you refer to xe above as an alternative to mySQL. Does this provide all features and better than mySQL and there is no database size limitations.
Tom Kyte
January 10, 2008 - 2:10 pm UTC

xe has well documented limits

1 gb memory
1 cpu
1 instance per host
4 gb user data

SE1 (standard edition 1) is very close to free and doesn't have the data/memory limits (but does have cpu caps) as well

MySQL

Parthiban Nagarajan, April 26, 2009 - 11:37 pm UTC

Hi Tom

Oracle has acquired Sun and hence the "mySQL" database. ( For 3-Tier architecture, I heard that mySQL is better than Oracle. Is it the reason that Oracle has acquired Sun? :) What would be the future of the free, open-sourced database ( or the Oracle Database itself :) ?
Tom Kyte
April 27, 2009 - 2:06 pm UTC

... For 3-Tier
architecture, I heard that mySQL is better than Oracle. ...

oh come on. give me a break.




And do you really think that mysql (the supported enterprisey one) is free?

Oracle XE is entirely free
it is closed source.


Mysql is not entirely free
mysql is open source



Oracle has many open source offerings - from Enterprise Linux, to Virtualization software, to transactional engines for mysql. You can get enterprise linux right now (and for a long time) and start using it. You want support and maintenance and all - you pay.


We are hardly "newbies" on the open source front. mysql would not be the first open source database we have either - in addition to innodb (the transactional front end to mysql) we have berkley db as well.

mfz, December 03, 2009 - 8:07 am UTC

I would like to access Mysql from Oracle .
As I understand , there is no transparent gateway for mysql .

How can I access mysql from Oracle .

Thanks
Tom Kyte
December 04, 2009 - 3:20 pm UTC

there is an odbc gateway, you can use that.

odbc 64 bit

A reader, December 07, 2009 - 8:07 am UTC

Sorry . I did not spell out my environment correctly .

I am on 11g R1 on windows 64 bit. Unfortunately , there is no 64 bit odbc for this ( as far as I know ) . What is the workaround ?


Thank you.
Tom Kyte
December 07, 2009 - 3:00 pm UTC

are you sure about that

http://dev.mysql.com/downloads/connector/odbc/5.1.html

i just searched for

64bit odbc driver mysql

and bam - it was right there.

mysql

A reader, December 07, 2009 - 10:20 pm UTC


mfz, December 08, 2009 - 9:32 am UTC

I was looking at Note.466225.1.

May be I misunderstood .

In any case , I went in , tried the ODBC setup . I could not get connected to the mysql db from oracle .

listener.ora
==============

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sqltest)
      (PROGRAM = dg4odbc)
      (SID_NAME = sqltest)
      (ORACLE_HOME = D:\app\Root\product\11.1.0\db_1)
    )
  )


tnsnames.ora
============

sqltest  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=mfz-db01)(PORT=1521))
    (CONNECT_DATA=(SID=sqltest))
    (HS=OK)
  )


Pinging the database
-----------------------


C:\>tnsping sqltest

TNS Ping Utility for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-DEC-2009 09:32:03

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
D:\app\Root\product\11.1.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=mfz-db01)(PORT=1521)) (CONNECT_DATA=
(SID=sqltest)) (HS=OK))
OK (70 msec)
  
 

Created the db link
---------------------

CREATE PUBLIC DATABASE LINK MYSQLDB CONNECT TO SQLTEST IDENTIFIED BY userid USING 'pwd'; 




SQL> select * from dual@mysqldb;
select * from dual@mysqldb
                   *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


What am I doing wrong ? Please advise.
Tom Kyte
December 10, 2009 - 12:52 pm UTC

where you say "pinging the database", you are not pinging a database at all - in fact, no databases need be present for that to work. You are simply connecting to the listener and sending it a 'ping'

On the SERVER, where the database you are connected to exists - what is the tnsnames.ora file there. the database link uses the server configuration.

A reader, December 10, 2009 - 1:45 pm UTC

I am checking the connectivity only from the server machine .the tnsnames.ora posted above is from the server

tnsnames.ora
============


sqltest =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=mfz-db01)(PORT=1521))
(CONNECT_DATA=(SID=sqltest))
(HS=OK)
)

Tom Kyte
December 10, 2009 - 3:31 pm UTC

CREATE PUBLIC DATABASE LINK MYSQLDB CONNECT TO SQLTEST IDENTIFIED BY userid USING 'pwd';

umm I see it, you have things mixed around there


connect to username
identified by password
using 'tns connect string'

ops$tkyte%ORA11GR2> create database link ora11gr2@loopback
  2  connect to ops$tkyte
  3  identified by foobar
  4  using 'ora11gr2'
  5  /

Database link created.

ops$tkyte%ORA11GR2> select * from dual@ora11gr2;

D
-
X

DG4ODBC with MSSQL

Shiva, December 14, 2009 - 5:18 am UTC

Tom,

We have a set of tables in SQLServer along with the data. We have also created a same set of tables in oracle server keeping the same table structure as in SQLServer. Here we need to migrate the data from SQLServer to Oracle and this would be a only one time activity. Hence we decided to use the DG4ODBC gateway to achieve the same. We have configured the settings as indicated in the DG4ODBC setup guide.

However we are facing following issues with the DG4ODBC heterogeneous services:
1) For most of the tables it extracts the data from SQLServer and however for some tables it gives an error message called:
[Micrsoft][SQL native client]Connection is busy with results for another command

2) If we use a cursor on SQLServer table we will get the following error:
ORA-01002: fetch out of sequence

Kindly suggest me whether am I missing any configuration settings or is it a bug?

Also let me know are there any other ways can I import the data into Oracle from SQLServer?


Note: 1) Few tables has image (BLOB) and text (CLOB) columns
2) I have also tried and failed to import the data through MS Access due to LOB columns
3) Also tried and failed to import the csv files using SQLLDR due to LOB columns

Thanks in Advance


Warm Regards
Shiva
Tom Kyte
December 14, 2009 - 9:50 am UTC

... Also let me know are there any other ways can I import the data into Oracle
from SQLServer?
...

you probably have DTS installed, that is reputed to work well.



Please contact and utilize support for #1 and #2.

Mariadb vs MySQL,

A reader, September 07, 2011 - 10:31 am UTC

Hello,

Now MySQL is supported by Oracle. I have read about MariaDB that works with the same set of binaries that MySQL use. From technical point of view, are there any difference between MariaDB and MySQL?

Thanks,

Tom Kyte
September 08, 2011 - 4:50 pm UTC

You would have to ask someone that uses them I guess?

Migrating views, procs etc

Amit, February 22, 2014 - 2:45 pm UTC

Oracle migration Workbench claims to migrate MySQL to Oracle.

However, a DATED version of documentation also states that it does not migrate the views, procs etc into Oracle.

http://www.oracle.com/technetwork/developer-tools/sql-developer/supportedmigplatforms-086703.html



Is there a way to automate this migration too?

Copy tables with data from Mysql to Oracle

A reader, March 17, 2023 - 3:21 am UTC

Greetings!

We need to copy 20 tables from MySQL database to oracle 11g with data for migration purposes.

Could you please suggest the migration methods available to perform this data migration ?


Thank you
Chris Saxon
March 17, 2023 - 4:53 pm UTC

This will depend on the size of the tables, how much (if any) downtime you can tolerate, and your budget. Here's a couple of options:

Oracle SQL Developer has a migration tool built-in to help you create scripts to do the migration. This may suit if the tables are small and/or you can have a large downtime window to do the migration:

https://docs.oracle.com/en/database/oracle/sql-developer/19.2/rptug/migrating-third-party-databases.html#GUID-299A057B-2B51-4646-9285-043A848D2A0B

If you want to do this with as little downtime as possible, you could look into Oracle GoldenGate. This enables you to transfer changes happening on the MySQL database to the Oracle Database. So you can switch over with little or no downtime

https://docs.oracle.com/en/middleware/goldengate/core/19.1/gghdb/using-oracle-goldengate-mysql.html

Copy tables with data from Mysql to Oracle

A reader, March 17, 2023 - 3:23 am UTC

There was a typo, target oracle database is on 19c (19.11.0.0).