Oracle Gateway for Oracle & Mysql ODBC Connection

–Demo for Oracle Gateway ODBC connection between Oracle & Mysql

Prerequisites:-

Machine: X86_64 bit

OS: RHEL 6.4

Database: Oracle 11.2.0.3.0 , & MySql 5.5.27 RPM

Gateway:  linux.x64_11gR2_gateways.zip

Mysql Connector/ UnixODBC driver: mysql-connector-odbc-5.2.5-1.ansi.el6.x86_64.rpm

ODBC driver manager: unixODBC-2.2.14-12.el6_3.x86_64 RPM

–Follow below steps for Oracle Gateway ODBC connection between Oracle & Mysql:

 

STEP1:

Su – root

–Install mysql rpm.

NOTE: In my case Mysql is installed on same machine, you can install it on different machine.

–Now create db and user and provide required privileges:

Mysql>use test;

Mysql>create user msql;

Mysql> GRANT ALL PRIVILEGES ON test TO 'msql'@'IP_OF_GATEWAY_SERVER' IDENTIFIED BY 'msql';

Mysql>grant file on *.* to ' msql'@'%';

Mysql>FLUSH PRIVILEGES;

Mysql>create table testtab (id int primary key, name char(100));

Mysql>insert into testab values(1, 'rishi');

 

STEP2:

Su – oracle

Install 11gR2 Oracle Database: 

HOME directory: /home/oracle/product/11.2.0/db_1

Db_Name: orcl

–now create user in orale db:

Export ORACLE_SID=orcl

Sqlplus “/as sysdba”

Sql>create user test identified by test;

Sql>grant resource to test;

Sql>grant connect to test;

Sql>create directory my_home_dir as '/home/oracle';

Sql>grant all on directory my_home_dir to test;

 

STEP3:

Su – oracle 

–Install 11gR2 gateway

HOME directory: /home/oracle/product/11.2.0/gateway

STEP4:

Su – root

–Install Mysql Connector/ UnixODBC driver:

Yum install mysql-connector-odbc-5.2.5-1.ansi.el6.x86_64.rpm

STEP5:

Su – root

–Install  ODBC driver manager:

Yum install unixODBC-2.2.14-12.el6_3.x86_64

Yum install unixODBC-devel-2.2.14-12.el6_3.x86_64

Yum install qt3-ODBC-3.3.8b-30.el6.x86_64

 

STEP6:

–Create odbc.ini file for ODBC Driver

Su –

[root ]# vi /etc/odbc.ini

[myodbc5]

Driver = /usr/lib64/libmyodbc5a.so    ———–Mysql Driver Lib

Description = Connector/ODBC 5.1 Driver DSN

SERVER = 192.168.10.160   ————-Mysql Database server ip

PORT = 3306                         ————-Mysql connection Port

USER = test                             ————– Mysql username          

PASSWORD = test                 ————– Mysql user Password

DATABASE = test                 —————  Mysql Database name

OPTION = 0

TRACE = OFF

 

STEP7:

n  Verify ODBC connection:

Su – root

#isql myodbc5 -v3

+—————————————+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

 

SQL> select version()

+———-+

| version()|

+———-+

| 5.5.27   |

+———-+

SQLRowCount returns 1

1 rows fetched

 

STEP8:

–Configure Listener.ora Tnsnames.ora file for Gateway:

Su – oracle

Cd /home/oracle/product/11.2.0/gateway/network/admin

Vi LISTENER.ORA

Add following entry:

 

SID_LIST_LISTENERG=

(SID_LIST=

 (SID_DESC=

  (SID_NAME=mydb)

  (ORACLE_HOME=/home/oracle/product/11.2.0/gateway)

  (PROGRAM=dg4odbc)

  (ENVS=LD_LIBRARY_PATH=/home/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/lib64)

 )

 (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /home/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

)

 

Vi Tnsnames.ora

Add following entry:

mydb =

(DESCRIPTION=

 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.160)(PORT=1522))

 (CONNECT_DATA=(SID=mydb))

 (HS=OK)

)

 

—make above entry in DB tnsname.ora file:

Cd /home/oracle/product/11.2.0/db_1/network/admin

Vi Tnsnames.ora

mydb =

(DESCRIPTION=

 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.160)(PORT=1522))

 (CONNECT_DATA=(SID=mydb))

 (HS=OK)

)

Now check listener status:

Oracle>lsnrctl status LISTENERG

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 05-AUG-2013 23:32:57

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.160)(PORT=1522)))

STATUS of the LISTENER

————————

Alias                     LISTENERG

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 – Production

Start Date                02-AUG-2013 00:30:15

Uptime                    3 days 23 hr. 2 min. 42 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/11.2.0/gateway/network/admin/listener.ora

Listener Log File         /home/oracle/diag/tnslsnr/localhost/listenerg/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.160)(PORT=1522)))

Services Summary…

Service "mydb" has 1 instance(s).

  Instance "mydb", status UNKNOWN, has 1 handler(s) for this service…

Service "orcl" has 1 instance(s).

  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

 

Oracle> tnsping mydb

 

TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 05-AUG-2013 23:34:05

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/home/oracle/product/11.2.0/gateway/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.160)(PORT=1522)) (CONNECT_DATA=(SID=mydb)) (HS=OK))

OK (20 msec)

 

STEP 9:

Now Configure  gateway init parameter file:

Cd /home/oracle/product/11.2.0/gateway/hs/admin

cp initdg4odbc.ora initmydb.ora

Vi initmydb.ora

–Add following entry:

 

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

HS_FDS_TRACE_LEVEL = <trace_level>

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI= /etc/odbc.ini

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

set LD_LIBRARY_PATH=/home/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/lib64

 

STEP 10:

Restart listener:

lsnrctl stop LISTENERG

lsnrctl start LISTENERG

 

STEP 11:

Oracle# sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 2 01:07:11 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name:  test/test@orcl

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 SQL> Create public database link mysqldb connect to "msql" identified by  "msql" using 'mydb';

Database link created.

—now access mysql tables as below:

SQL>select * from "msql"."testab"@mysqldb;

       id name

———- —————————————-

         1 rishi

SQL>insert into "msql"."testtab"@mysqldb values(2, 'rishi2');

1 row created.

SQL>select * from "msql"."testtab"@mysqldb;

        id name

———- —————————————-

         1 rishi

          2 rishi2

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply