MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server.
The remote database can be same oracle or it can be non-oracle database.
To access non-Oracle systems you must use Oracle Heterogeneous Services.
Software’s required:
- Oracle RDBMS software
- MySQL software
- ODBC Drivers
- MySQL Connector
Prerequisites:
- Oracle database should be up and running.
- MySQL database should be up and running.
- Oracle net services should be up and running.
- ODBC and ODBC agent should be configure, up and running.
Please check ORACLE database link configuration for basic database link information.
Process for heterogeneous database link configuration:
STEP-1
Oracle Database Configuration:
Check the database version
|
1 2 3 4 5 |
SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS --------------------- ------------ ktexperts OPEN |
|
1 2 3 4 5 6 7 8 |
SQL> select * from v$version; BANNER CON_ID ------------------------------------------------------------------------------------ ---------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 |
Note: ORACLE RDBMS running with 64bit
MySQL Database Configuration:
|
1 |
[root@SERVER1 ~]# mysqladmin version -h localhost –p |
|
1 2 3 4 5 6 7 8 9 10 11 |
<strong>YOU CAN SEE BELOW INFORMATION:</strong> Enter password: ******* mysqladmin Ver 8.42 Distrib 5.7.24, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.24 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 2 days 21 hours 42 min 44 sec Threads: 1 Questions: 60 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.000 |
|
1 |
mysql> show VARIABLES LIKE "%version%"; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+--------------------------------------+-----------------------------------------------+ | Variable_name | Value | +--------------------------------------+-----------------------------------------------+ | innodb_version | 5.7.24 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.24 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +--------------------------------------+-----------------------------------------------+ 8 rows in set (0.02 sec) |
Note: MySQL running with 64bit
Download 64 bit ODBC and MySQL database connector and install.
STEP-2
Configure the ODBC:
Note: From Oracle 11g, the Oracle Heterogeneous Service (HS) executable name is now called DG4ODBC. If you’re using a 64-bit version of Oracle you must use a 64-bit ODBC driver. If you’re using a 32-bit version of Oracle, you must use a 32-bit ODBC driver.
Check the Oracle Heterogeneous Service (HS) executable
|
1 |
[oracle@SERVER1 ~]$ file /u01/app/oracle/product/12.1.0/dbhome/bin/dg4odbc |
|
1 2 3 4 5 |
<strong>Result:</strong> /u01/app/oracle/product/12.1.0/dbhome/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped However ODBC is a one of the prerequisite for installing the oracle RDBMS i.e: UnixODBC is required to install oracle RDBMS) And MySQL ODBC Connector is required. |
ODBC Drivers you can download (RPM) from the below link and install.
https://dev.mysql.com/downloads/connector/odbc/
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-unix-rpm.html
STEP-3
ORACLE NET SERVICE CONFIGURATION:
Configure the oracle Net services using listener.ora and tnsnames.ora
|
1 |
[oracle@SERVER1 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome/network/admin/ |
Configure Listener
|
1 |
[oracle@SERVER1 admin]$ ls –l listener.ora |
Configure the New listener using below info:
|
1 |
[oracle@SERVER1 admin]$ vi listener.ora |
Use the below configuration:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
ktuser = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1522)) ) ) ) SID_LIST_ktuser= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ktexperts) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome) (SID_NAME = ktexperts) (PROGRAM=/u01/app/oracle/product/12.1.0/dbhome/bin/dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/12.1.0/dbhome/lib/") ) ) |
Note:
LISTENER NAME = ktuser
SID_NAME = ktexperts
HOST = SERVER1
PORT = 1522
Start and Check the status of listener ktuser
|
1 2 |
[oracle@SERVER1 ~]$ lsnrctl start ktuser [oracle@SERVER1 ~]$ lsnrctl status ktuser |
Configure the Tnsnames
Tnasnames.ora
|
1 2 |
[oracle@SERVER1 admin]$ ls –l tnsnames.ora [oracle@SERVER1 admin]$ vi tnsnames.ora |
Use the below configuration:
|
1 2 3 4 5 6 7 8 9 10 |
ktuser = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1522)) ) (CONNECT_DATA = (SID = ktexperts) ) (HS = OK) ) |
Check the Connection
|
1 |
[oracle@SERVER1 ~]$ tnsping ktuser |
STEP-4
Create the user:
Create the MySQL database and MySQL user and grant the necessary privileges to user.
——————————-KTUSER—————————————–
FOR THE SERVER: SERVER1
|
1 2 3 |
mysql> CREATE USER 'ktuser'@'SERVER1' IDENTIFIED BY 'Welcome@12'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'ktuser'@'SERVER1' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; |
FOR THE SERVER: ANY SERVER
|
1 2 3 |
mysql> CREATE USER 'ktuser'@'%' IDENTIFIED BY 'Welcome@12'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'ktuser'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; |
FOR THE SERVER: LOCAL HOST
|
1 2 3 |
mysql> CREATE USER 'ktuser'@'localhost' IDENTIFIED BY 'Welcome@12'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'ktuser'@'localhost' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; |
CHECK THE CONNECTION:
|
1 2 |
[root@SERVER1 ~]# mysql -uktuser -p -hSERVER1 Password: ******* |
CREATE THE DATABASE AND TABLE :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> create database ktmydb; mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | ktmydb | | mysql | | performance_schema | | sys | +---------------------+ 5 rows in set (0.00 sec) |
|
1 |
mysql> use ktmydb; |
Create the Table with name ktmytab
|
1 2 |
mysql> create table ktmytab(sno int); mysql> show tables; |
Insert few values into the table.
|
1 2 3 4 |
mysql> insert into ktmytab values(9); mysql> insert into ktmytab values(99); mysql> insert into ktmytab values(999); mysql> commit; |
|
1 |
mysql> select * from ktmytab; |
STEP-5
Configure the ODBC:
|
1 |
[root@SERVER1 ~]# vi /etc/odbc.ini |
Add the below content:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[ODBC Data Sources] ktuser = MySQL ODBC 3.51 Driver DSN [ktuser] Driver = /usr/lib64/libmyodbc8a.so Description = MySQL ODBC 3.51 Driver DSN SERVER = 192.168.0.1 PORT = 3306 USER = ktuser Password = Welcome@12 Database = ktmydb OPTION = 3 SOCKET = |
Note:
User: MySQL user
Password: MySQL user
Database: MySQL local Database
STEP-6
Configure the initialization file for the Oracle Heterogeneous Service (HS).
|
1 2 3 4 |
[oracle@SERVER1 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome/hs/admin/ [oracle@SERVER1 admin]$ cp initdg4odbc.ora init$ORACLE_SID.ora [oracle@SERVER1 admin]$ ls –l init$ORACLE_SID.ora -rw-r--r--. 1 oracle oinstall 668 Nov 2 15:53 initktexperts.ora |
|
1 |
[oracle@SERVER1 admin]$ vi init$ORACLE_SID.ora |
Modify the content as like below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters # HS_FDS_CONNECT_INFO = ktuser HS_FDS_TRACE_LEVEL = off #S_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so HS_FDS_FETCH_ROWS = 1 HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=american_america.we8iso8859P1 HS_NLS_NCHAR=UCS2 # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system # #set <envvar>=<value> |
STEP-7
Connect the Oracle database and create public database link to access MySQL database data.
|
1 |
SQL> create public database link "ktdblink" connect to "ktuser" identified by "Welcome@12" using 'ktuser'; |
Note:
user : MySQL user
Password: MySQL user
Tns Entry : Oracle Net Tnsnames alias
|
1 2 3 4 5 6 7 |
SQL> select * from "ktmytab"@ ktdblink; sno ---------- 9 99 999 |
Note: Please test scripts in Non Prod before trying in Production.
Loading...
Omar
Hello, thank you for the article, but I need connect MySql to Oracle, how I do can to make the connection?