oracle gateway for mysql_Oracle 11.2 Gateway to MySQL install configuration

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

可以参考 http://oracledbahub.com/2013/08/oracle-gateway-oracle-mysql-odbc-connection/

注意要安装

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

和其他必要的rpm 包

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

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 =

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