ORACLE to MySQL 透明网关实施

2024年 5月 9日 74.8k 0

OracleForMysql透明网关实施

目录

一、ORACLE透明网关安装/检查... 2

二、mysql-connector安装... 6

三、odbc配置... 6

四、透明网关(dg4odbc)配置... 8

五、透明网关listener监听配置... 8

六、ORACLE生产端配置... 10

 

 

环境:

Oracle:11.2.0.4

Mysql:5.6.36

软件包:

p13390677_112040_Linux-x86-64_5of7.zip                   #Oracle_gateway安装包

         mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm           #mysql odbc插件安装包

 

一、ORACLE透明网关安装/检查

1、如果透明网关装在Oracle服务器上,则直接使用dg4odbc命令即可得到以下返回:

[oracle@test ~]$ dg4odbc

 

 

Oracle Corporation --- THURSDAY  APR 25 2024 09:51:31.820

 

Heterogeneous Agent Release 11.2.0.4.0 - 64bit
Production  Built with

   Oracle
Database Gateway for ODBC

 

2、如果透明网关装在mysql端或第三台无Oracle软件的机器时,则需要单独安装透明网关:

涉及安装包:

p13390677_112040_Linux-x86-64_5of7.zip

         安装步骤:

A、 依赖安装:

yum -y install binutils-*

yum -y install compat-libstdc++-*

yum -y install elfutils-libelf-*

yum -y install elfutils-libelf-devel-*

yum -y install elfutils-libelf-devel-static-*

yum -y install gcc-*

yum -y install gcc-c++-*

yum -y install glibc-*

yum -y install glibc-common-*

yum -y install glibc-devel-*

yum -y install glibc-headers-*

yum -y install kernel-headers-*

yum -y install ksh-*

yum -y install libaio-*

yum -y install libaio-devel-*

yum -y install libgcc-*

yum -y install libgomp-*

yum -y install libstdc++-*

yum -y install libstdc++-devel-*

yum -y install make-*

yum -y install sysstat-*

yum -y install unixODBC-*

yum -y install unixODBC-devel-*

B、 用户创建及环境变量设置

groupadd -g 1002 oinstall

groupadd -g 1003 dba

useradd -u 1004 -g oinstall -G dba oracle

echo oracle | passwd --stdin oracle

======================================================

echo "

export ORACLE_BASE=/u01/app/oracle

export
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH >>
/home/oracle/.bash_profile

======================================================

chown -R oracle.oinstall /home/oracle/.bash_profile

C、 Oracle用户解压并运行runInstall

ORACLE to MySQL 透明网关实施-1

D、 图形化界面安装

ORACLE to MySQL 透明网关实施-1

ORACLE to MySQL 透明网关实施-1

ORACLE to MySQL 透明网关实施-1

ORACLE to MySQL 透明网关实施-1

ORACLE to MySQL 透明网关实施-1

二、mysql-connector安装

下载地址:

https://dev.mysql.com/downloads/connector/odbc/

 

1、  依赖包安装:

yum install unixODBC*

 

[root@test ~]# rpm -qa|grep unixODBC*

unixODBC-devel-2.2.14-14.el6.x86_64

unixODBC-2.2.14-14.el6.x86_64

2、  mysql-connector安装

ORACLE to MySQL 透明网关实施-1

 

三、odbc配置

1、记录driver文件路径

ORACLE to MySQL 透明网关实施-1

2、查看odbc.ini路径

[root@test ~]# odbcinst -j

ORACLE to MySQL 透明网关实施-1

3、编辑odbc.ini文件

[root@test ~]# cat /etc/odbc.ini

 

[mysql_test]                                                                  #连接名(透明网关配置文件、监听文件等需要用到)

Description    
= ODBC for MySQL                    #描述信息

Driver         
= /usr/lib64/libmyodbc8w.so     #odbcinst文件中记录的driver文件路径

Server         
= 10.10.20.129                              #mysql服务器ip

Port           
= 3306                                            #mysql服务端口

User           
= dbtest                                        #mysql远程连接用户名

Password       
= abcd1234                                 #mysql远程连接用户密码

Database       
= test                                            #远程连接的库名

4、odbc连接测试

[root@test ~]# isql mysql_test

ORACLE to MySQL 透明网关实施-1

 

 

四、透明网关(dg4odbc)配置

1、创建配置文件并修改具体内容

[root@test ~]# su - oracle

[oracle@test ~]$ cd $ORACLE_HOME/hs/admin

[oracle@test admin]$ cp initdg4odbc.ora
initmysql_test.ora

[oracle@test admin]$ vim initmysql_test.ora

# 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 = mysql_test                                           #odbc.ini中的连接名

HS_FDS_TRACE_LEVEL = off                                                               #日志选项(on、off、debug)

HS_FDS_SHAREABLE_NAME =
/usr/lib64/libodbc.so                   #odbc的deriver文件

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252            #oracle数据库的字符集

HS_NLS_NCHAR = UCS2                                                                    #dblink处理访问时乱码问题

set ODBCINI=/etc/odbc.ini                                                                 #odbc.ini文件的路径

#

# ODBC specific environment variables

#

 

五、透明网关listener监听配置

1、创建新的静态监听

[oracle@test admin]$ cat
$ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

  (SID_DESC =

   
(GLOBAL_DBNAME = orcl)

    (SID_NAME
= orcl)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

  )

 
(SID_DESC=

    (SID_NAME = mysql_test)

    (ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1)

    (PROGRAM = dg4odbc)

 
)

)

 

LISTENER =

 
(DESCRIPTION_LIST =

   
(DESCRIPTION =

     
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))

     
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

2、重新加载监听文件

[oracle@test admin]$ lsnrctl reload

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production
on 25-APR-2024 11:38:18

 

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

 

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))

The command completed successfully

3、检查监听

[oracle@test admin]$ lsnrctl status

ORACLE to MySQL 透明网关实施-1

 

 

六、ORACLE生产端配置

1、tnsnames文件配置

orcl_mysql =

 
(DESCRIPTION =

   
(ADDRESS_LIST =

     
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.20.128)(PORT = 1521))

    )

   
(CONNECT_DATA =

      (SID =
mysql_test)

    )

   (HS = OK)

  )

2、测试tns连接

ORACLE to MySQL 透明网关实施-1

3、使用tns创建dblink

create 
PUBLIC DATABASE LINK dlk connect to “dbtest” identified by “abcd1234”
using 'orcl_mysql';

4、使用连接串创建dblink

create public database link mysql_emr connect to "read" identified by "Read@2020" using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.68.10.16 )(PORT = 1521)))(CONNECT_DATA =(SID = mysql_emr))(HS = OK))’;

5、测试dblink

ORACLE to MySQL 透明网关实施-1

 

 

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论