环境说明: Oracle: 12.1.0.2.0 OS: CentOS Linux release 7.9.2009 MySQL: 5.7.38 Oracle 字符集:AMERICAN_AMERICA.AL32UTF8 MySQL 字符集:utf8
以下操作均在oracle服务器执行:
1 查看DG4ODBC版本
[oracle@test ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/product/12.1.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=a103b0b14d55c007d0049142ef0f5cf18c5b2d19, not stripped
2 安装unixODBC
[root@test ~]# yum install unixODBC*
安装完测试:
[root@test ~]# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
下载驱动包:
https://dev.mysql.com/downloads/connector/odbc/
安装:
[root@test ~]# rpm -ivh mysql-connector-odbc-8.0.11-1.el7.x86_64.rpm warning: mysql-connector-odbc-8.0.11-1.el7.x86_64.rpm: Header V3 DSA/SHA256 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%] 1:mysql-connector-odbc ########################################### [100%]
Success: Usage count is 1
Success: Usage count is 1
4 配置/etc/odbc.ini(此文件默认没有直接添加,我的环境是配置两个mysql数据库,需要几个配几个)
[root@test ~]# vi /etc/odbc.ini
[mysql数据库名]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8w.so (根据自己安装的版本修改)
Server = mysql服务器IP
Database = mysql数据库名
Port = 端口
USER = mysql账号
Password = mysql账号密码
Socket =
Option = 3
Stmt =
CHARSET = UTF8 (mysql字符集)
[mysql数据库名]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8w.so (根据自己安装的版本修改)
Server = mysql服务器IP
Database = mysql数据库名
Port = 端口
USER = mysql账号
Password = mysql账号密码
Socket =
Option = 3
Stmt =
CHARSET = UTF8 (mysql字符集)
5 连接测试:
[root@test ~]# isql mysql数据库名 -v
6 oracle 监听文件添加:(注意语法)
Oracle > cd $ORACLE_HOME/network/admin
Vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC =
(SID_NAME = test1) ----修改mysql数据库名
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(PROGRAM = dg4odbc)
)
(SID_DESC =
(SID_NAME = test2) ----修改mysql数据库名
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(PROGRAM = dg4odbc)
)
)
7 修改tnsnames.ora
Vi tnsnames.ora
test1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle主机名 ) --修改
(PORT = 1521))
(CONNECT_DATA =
(SID=test1) --mysql数据库名
)
(HS=OK)
)
test2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle服务器主机名 )--修改
(PORT = 1521))
(CONNECT_DATA =
(SID=test2) --mysql数据库名
)
(HS=OK)
8 配置odbc init文件(默认没有,直接添加)
Root> su - oracle
Oracle> cd $ORACLE_HOME/hs/admin
Vi inittest1.ora --init后面需要与tnsnames.ora的名字一致
HS_FDS_CONNECT_INFO=test1 --mysql数据库名
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 --oracle数据库字符集
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini --之前vi添加的位置
Vi inittest2.ora --init后面需要与tnsnames.ora的名字一致
HS_FDS_CONNECT_INFO=test2 --mysql数据库名
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 --oracle数据库字符集
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini --之前vi添加的位置
9 重启监听
[oracle@oracle01 db_1]$ lsnrctl stop [oracle@oracle01 db_1]$ lsnrctl start
SQL> alter system register;
重启前;
重启后:
Oracle>Tnsping test1
10 创建dblink测试:(使用之前odbc.ini里面的账号密码创建dblink,此处使用test)
使用需要访问mysql的oracle账号登陆
Sql>conn username/password
create database link TEST1_MYSQL connect to "test" identified by "test" using 'test1';
create database link TEST2_MYSQL connect to "test" identified by "test" using 'test2';
测试dblink 查询mysql数据
Sql>conn username/password
Select count(*) from "mysql表名"@TEST1_MYSQL
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。