Oracle DBLink专题

需提前安装透明网关，在此不赘述。

到MSSQLServer

创建到SQLServer的DBLINK的方式：
供应商给出的是直接将tns内容写在dblink里面：

create database link DBLINKSBO_AE connect to sa identified by "111111" using '(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)';


这个没有尝试，我的方式是：
在SQLServer创建账号srm，测试库为20210706_AE，通过配置tnsnames.ora、listener.ora、initdg4msql.ora，前两个位于ORACLE_HOME(直接cd $ORACLE_HOME)目录的network/admin/下，第三个文件位于dg4msql/admin/下；

1.首先先配置initdg4msql.ora

HS_FDS_CONNECT_INFO=[10.10.0.215]:1433//20210601_AE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT_RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


2.然后配置tns
增加内容

dg4msql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp) (HOST = localhost) (PORT = 1521))
(CONNECT_DATA = (SID = dg4msql))
(HS=OK)
)


3.然后配置监听
在SID_LIST中增加内容

(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/data/app/oracle/product/12.2.0/dbhome_1)
(PROGRAM=dg4msql)
)


监听配完记得重启

4.创建DBLINK及测试

create database link DBLINKSBO_AE
connect to srm identified by "Hxxxxx123"
using 'dg4msql';


测试：
使用sqldeveloper连接后

select 1 from dual@DBLINKSBO_AE;


5.连接同一个SQLServer的其他库
这时候就要重新配置一组：
配置initdg4msqlce.ora，直接创建文件就可以

HS_FDS_CONNECT_INFO=[10.10.0.215]:1433//20210601_CE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT_RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8


然后配置tns
增加内容

dg4msqlce =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp) (HOST = localhost) (PORT = 1521))
(CONNECT_DATA = (SID = dg4msqlce))
(HS=OK)
)


然后配置监听，注意PROGRAM不用改：

(SID_DESC=
(SID_NAME=dg4msqlce)
(ORACLE_HOME=/data/app/oracle/product/12.2.0/dbhome_1)
(PROGRAM=dg4msql)
)


6.相关错误代码
ORA-28545
ORA-02063
ORA-01017
ORA-02063

7.查看DBLINK信息

SELECT * FROM dba_db_links;


8.删除dblink

DROP DATABASE LINK [name];
或
DROP PUBLIC DATABASE LINK [name];


到MySQL

Oracle-MySQL透明网关的工作方式可简述为：
Oracle数据库 --> DBLINK --> TNS别名 --> Listener监听 --> ODBC --> MySQL connector --> MySQL监听 --> MySQL数据库。

1.准备工作
检查是否安装了透明网关

cd $ORACLE_HOME
cd hs
dg4odbc


如果显示类似如下内容，则说明已安装（19c之后需要单独安装）

Oracle Corporation --- FRIDAY MAR 29 2024 17:27:42.898

Heterogeneous Agent Release 12.2.0.1.0 - 64bit Production Built with
Oracle Database Gateway for ODBC


ODBC-mysql安装
下载地址：

https://downloads.mysql.com/archives/c-odbc/


选择对应的包，比如我这里是mysql-connector-odbc-8.0.30-1.el7.x86_64.rpm
上传至服务器，
然后安装：

rpm -ivh mysql-connector-odbc-8.0.30-1.el7.x86_64.rpm


安装依赖：

yum install unixODBC*


2.创建测试库
MySQL: 创建测试库test1，表A_DBLINK，用户就使用新建的测试用户srm即可

3.配置文件
在oracle数据库上配置odbc连接mysql的环境
vim /etc/odbcinst.ini

vim /etc/odbc.ini

[mysql_test]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8w.so ---与上面现实的unicode driver一致
Server = 10.10.4.154 ---mysql远程地址
Port = 3306 ----mysql 端口
User = srm ----mysql连接用户
Password = srm ---mysql 连接密码
Database = test1 ---mysql连接数据库


然后root下使用isql测试

isql -v mysql_test


oracle下使用isql测试出现错误segmetation fault，原因是oracle的.bash_profile用的是LANG = en_US.GB2312
换成LANG = en_US.UTF-8即可，但对oracle中文字符的影响尚不明确

4.配置连接监听地址
cd $ORACLE_HOME/network/admin
vim listener.ora
在SID_LIST_LISTENER下添加一部分

(SID_DESC=
(SID_NAME=mysql_test)
(ORACLE_HOME=/data/app/oracle/product/12.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)


vim tnsnames.ora添加

mysql_test=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME= mysql_test)
)
(HS = OK)
)


5.重启监听后测试连接

lsnrctl stop
lsnrctl start


测试链接

tnsping mysql_test


显示结果

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 29-MAR-2024 17:59:12

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

Used parameter files:
/data/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME= mysql_test)) (HS = OK))
OK (10 msec)


6.配置initdg4odbc.ora
在hs/admin下
复制一份，命名为initmysql_test.ora
修改成类似这样：

HS_FDS_CONNECT_INFO = mysql_test
HS_FDS_TRACE_LEVEL = OFF
#HS_FDS_SHAREABLE_NAME = /data/gateway/unixODBC/lib/libodbc.so
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so #此处不要改成其他的.so
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_NLS_NCHAR = UCS2
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
#
# ODBC specific environment variables
#
#set ODBCINI = /data/gateway/unixODBC/etc/odbc.ini
set ODBCINI = /etc/odbc.ini


7.创建dblink并测试

create DATABASE LINK TOMYSQL connect to "srm" identified by "srm" using 'mysql_test';

select 1 from dual@TOMYSQL;