MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

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

Share via:

 

FacebookTwitterLinkedInWhatsApp

分享

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

1 2 3 4 5

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

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:

[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

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

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)

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

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.

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

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

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

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

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

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

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

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

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

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

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:

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

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)

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;

1 2 3 4

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       =

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

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

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

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

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

1 2 3 4 5 6 7

SQL> select * from "ktmytab"@ ktdblink;          sno     ----------          9         99        999

 

 

 

Share via:

 

FacebookTwitterLinkedInWhatsApp

分享

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/