MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

osted on January 21, 2019 in MySQL, MySQL DBA, Oracle DB Admin

Share via:

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 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

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

1	[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

<strong>YOU CAN SEE BELOW INFORMATION:</strong>

Enter password: *******

mysqladmin Ver 8.42 Distrib 5.7.24, for Linux on x86_64

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	mysql> show VARIABLES LIKE "%version%";

+--------------------------------------+-----------------------------------------------+
| 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)

+--------------------------------------+-----------------------------------------------+

| 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

1	[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.

<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/

1	[oracle@SERVER1 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome/network/admin/

Configure Listener

[oracle@SERVER1 admin]$ ls –l  listener.ora

1	[oracle@SERVER1 admin]$ ls –l listener.ora

Configure the New listener using below info:

[oracle@SERVER1 admin]$ vi listener.ora

1	[oracle@SERVER1 admin]$ vi listener.ora

Use the below configuration:

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/")
    )
  )

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

1 2	[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

1 2	[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)
  )

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

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

mysql> CREATE USER 'ktuser'@'SERVER1' IDENTIFIED BY 'Welcome@12';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ktuser'@'SERVER1' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

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;

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;

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: *******

1 2	[root@SERVER1 ~]# mysql -uktuser -p -hSERVER1 Password: *******

CREATE THE DATABASE AND TABLE :

mysql> create database ktmydb;
mysql> show databases;

+---------------------+
| Database            |
+---------------------+
| information_schema  |
| ktmydb              |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
5 rows in set (0.00 sec)

mysql> create database ktmydb;

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| information_schema |

| ktmydb |

| mysql |

| performance_schema |

| sys |

+---------------------+

5 rows in set (0.00 sec)

mysql> use ktmydb;

1	mysql> use ktmydb;

Create the Table with name ktmytab

mysql> create table ktmytab(sno int);
mysql> show tables;

1 2	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> insert into ktmytab values(9);

mysql> insert into ktmytab values(99);

mysql> insert into ktmytab values(999);

mysql> commit;

mysql> select * from ktmytab;

1	mysql> select * from ktmytab;

STEP-5

Configure the ODBC:

[root@SERVER1 ~]# vi /etc/odbc.ini

1	[root@SERVER1 ~]# vi /etc/odbc.ini

Add the below content:

[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       =

[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 ~]$ 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

1	[oracle@SERVER1 admin]$ vi init$ORACLE_SID.ora

Modify the content as like below:

# 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>

# 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';

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

SQL> select * from "ktmytab"@ ktdblink;

       sno
    ----------
         9
        99
       999

SQL> select * from "ktmytab"@ ktdblink;

sno

----------

999

Share via:

Facebook Twitter LinkedIn WhatsApp

Note: Please test scripts in Non Prod before trying in Production.

(6 votes, average: 5.00 out of 5)

https://www.ktexperts.com/mysql-to-oracle-database-link-creation-using-heterogeneous-services/

相关阅读:
Object C学习笔记25-文件管理(一)
实施项目--为什么开发人员一直在抱怨需求变动
 Git.Framework 框架随手记--准备工作
 一网打尽！2018网络安全事件最全的盘点
 林纳斯·托瓦兹和Linux行为准则：揭穿7个谬论
 LinkedList源码解析
 四种List实现类的对比总结
 HashMap源码解析
 volatile
Java内存模型与共享变量可见性
原文地址：https://www.cnblogs.com/seasonzone/p/12171668.html