1、主要访问路径
Oracle--》dg4odbc--》odbc--》mysql
2、配置流程从后往前
Oracle端端执行:
yum安装相关包：
yum install mysql-connectot-odbc
yum install unix-ODBC
配置odbc驱动
vi /etc/odbc.ini
[otomysql]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8a.so
Server = 192.168.2.2 --mysql IP
Port = 3306 --mysql port
User = root --mysql user
Password = 123456 --mysql password
Database = envprod --mysql database name
Charset=utf8 --注意字符集，否则可能会乱码
cat /etc/odbcinst.ini
[MySQL]
Description = MySQLodbc
cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1
[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
UsageCount=1
在Oracle用户使用
isql otomysql --连接mysql数据库
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
配置dg4odbc
配置监听程序
cat /u01/app/oracle/product/19.0.0/db_1/hs/admin/initotomysql.ora --默认存在名为"initdg4odbc.ora"的文件，复制"initdg4odbc.ora"文件，新文件名称改为"init***.ora"，***为监听的实例名称
HS_FDS_CONNECT_INFO=otomysql
HS_FDS_TRACE_LEVEL=debug --追踪级别参数，出于性能影响一般不配置或者配置为"OFF"，若遇到网关问题需要跟踪日志，则配置为"Debug"，跟踪日志文件存放在"ORACLE_HOME/hs/trace"目录下
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so
HS_FDS_TRACE_FILE_NAME = otomysql.trc
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
#HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
HS_NLS_NCHAR = UCS2 #必须配置否则可能报错
HS_LANGUAGE=AMERICAN_AMERICA.UTF8 #由于mysql采用的事utf-8，为了保证最终的结果在oracle段不出现乱码做次配置
set ODBCINI=/etc/odbc.ini
cat /u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora --用Oracle用户修改
conn_mysql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 198.210.*.*) (PORT = 1521))
)
(CONNECT_DATA =
(SID = otomysql)
)
(HS = OK)
)
(HS = OK) --这个必须添加，告诉Oracle连接其他数据库
cat /u01/app/grid_home/network/admin/listener.ora --注意使用grid用户修改此文件
LISTENER_SCAN1=
(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))
(ADDRESS=(PROTOCOL=TCP)(HOST=198.210.*.*)(PORT=1521))
)
) # line added by Agent
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
)
) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME= otomysql)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/lib:/usr/lib64:/u01/app/oracle/product/19.0.0/db_1/lib)
)
)
ADR_BASE_LISTENER=/u01/11.2.0/grid # GRID_HOME
使用grid用户重启监听：
#lsnrctl stop
#lsnrctl start
查看是否配置是否成功
#tnsping otomysql
MySQL端注意配置远程访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '****'; --此处可以指定外部IP访问权限，当然生产环境的权限不能像我这样设置
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
创建 dblink
create database link tomysql connect to "root" identified by "***" using 'otomysql'; --此用户与 /etc/odbc.ini文件中user项保持一直
select * from "test"@tomysql; --注意如若进行查询则相关的表名以及字段名务必用引号引起来。
