osted on January 21, 2019 in MySQL, MySQL DBA, Oracle DB Admin
FacebookLinkedInWhatsApp
Post Views: 5,129
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
|
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
--------------------- ------------
ktexperts OPEN
|
|
|
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:
|
|
[root@SERVER1 ~]# mysqladmin version -h localhost –p
|
|
|
<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
|
|
|
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
|
|
[oracle@SERVER1 ~]$ file /u01/app/oracle/product/12.1.0/dbhome/bin/dg4odbc
|
|
|
<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
|
|
[oracle@SERVER1 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome/network/admin/
|
Configure Listener
|
|
[oracle@SERVER1 admin]$ ls –l listener.ora
|
Configure the New listener using below info:
|
|
[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
|
|
[oracle@SERVER1 ~]$ lsnrctl start ktuser
[oracle@SERVER1 ~]$ lsnrctl status ktuser
|
Configure the Tnsnames
Tnasnames.ora
|
|
[oracle@SERVER1 admin]$ ls –l tnsnames.ora
[oracle@SERVER1 admin]$ vi tnsnames.ora
|
Use the below configuration:
|
|
ktuser =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1522))
)
(CONNECT_DATA =
(SID = ktexperts)
)
(HS = OK)
)
|
Check the Connection
|
|
[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
|
|
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
|
|
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
|
|
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:
|
|
[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)
|
Create the Table with name ktmytab
|
|
mysql> create table ktmytab(sno int);
mysql> show tables;
|
Insert few values into the table.
|
|
mysql> insert into ktmytab values(9);
mysql> insert into ktmytab values(99);
mysql> insert into ktmytab values(999);
mysql> commit;
|
|
|
mysql> select * from ktmytab;
|
STEP-5
Configure the ODBC:
|
|
[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).
|
|
[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
|
|
|
[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.
|
|
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
|
|
SQL> select * from "ktmytab"@ ktdblink;
sno
----------
9
99
999
|
FacebookLinkedInWhatsApp
Note: Please test scripts in Non Prod before trying in Production.
