Oracle: Create Database Link to MySQL Database
April 3, 2012 Leave a comment
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.
- Setup the ODBC data source to your MySQL database
Here, 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.
- 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:
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.
- 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.
- 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:
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.
- Restart the Oracle Service and the TNS Listener Service
- 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';
- 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: