Oracle: Create Database Link to MySQL Database

For data migration or integration projects, it is extremely useful to connect different databases on SQL level. Oracle offers a very nice feature for that called “database” links where you can query different Oracle databases.

However, it’s not only possible to query Oracle databases, but you can also connect to for example a MySQL database (or any other ODBC data source) with some configuration effort.

Here is how to do it in Windows – namely Windows Server 2008 64 Bit with Oracle 11.2.0, but should be similar in any other constellation. Apart from the different ODBC setup, it is very similar on any other OS like Linux or Unix an Oracle server is running on.

  1. Setup the ODBC data source to your MySQL database

    Setup an ODBC MySQL DatasourceHere, we connect to the database “hours” on the server “atviecrm1″, using the previously created MySQL user “oracle” (the password is also “oracle”).The ODBC data source name is “test”. If you click the button “Test” you can verify that the connection is working.

  2. Configure listener.ora

    Now we have to create a listener for this data source – so go into your $ORACLE_HOME/NETWORK/ADMIN directory and add the following to listener.ora:

    SID_LIST_LISTENER =
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=tst)
    (ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1)
    (PROGRAM=dg4odbc)
    )
    )

    It tells the listener to use dg4odbc, and there the SID “tst” – see next step how to configure this SID. ORACLE_HOME of course has to be changed to your ORACLE_HOME directory.

    Hint: If you encounter problems with dg4odbc try typing in “dg4odbc” at the command line. If the command is not recognized, add the directory $ORACLE_HOME/BIN (this is where dg4odbc should be located) to your path variable.

  3. Configure data source / SID for dg4odbc

    Go into the directory $ORACLE_HOME/HS/ADMIN and create a file called “inittst.ora”  (The name convention is init + SID.ora – you can create many SIDs to different ODBC data sources). Write just the following line in the file:


    HS_FDS_CONNECT_INFO = test

    This specifies which ODBC data source to use, in our case “test” which we configured in the first step – this should be enough to establish a connection, of course you can configure a lot of other ODBC parameters in this file but I will not go into details here.

  4. Configure tnsnames.ora

    Now, in order to be able to create a database link, we also need to create an entry in $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ora:

    TSTLINK =
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=TCP)
    (HOST=localhost)
    (PORT=1521)
    )
    (CONNECT_DATA=
    (SID=tst)
    )
    (HS=OK)
    )

    Please note, the HS=OK entry is very important, as well as using the same SID we configured in the two steps before, of course. TSTLINK is the name we are going to use for creating the database link in step 6.

  5. Restart the Oracle Service and the TNS Listener Service
  6. Create database Link

    Logon to your Oracle database with DBA rights (e.g. as SYS) and create the database link:


    create public database link link2mysqltest connect to "oracle" identified by "oracle" using 'TSTLINK';

  7. Test database Link and finished


    Hint
    : Note that table names in MySQL are case sensitive so user the double quotes to force the correct case of the table names.


    select * from "test_table"@link2mysqltest

There are some articles dealing with this issue, the three below are very useful in particular:

https://forums.oracle.com/forums/thread.jspa?threadID=631591

http://www.pythian.com/news/1554/how-to-access-mysql-from-oracle-with-odbc-and-sql/

http://www.tutorials.de/relationale-datenbanksysteme/218777-oracle-demo-datenbanklinks.html (German)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: