达梦数据库-达梦数据库中link链接访问远程oracle/sqlserver-记录总结

1DM link访问oracle 19c/11g

达梦通过LINK访问Oracle数据库有两种方式：
一种是通过Oracle oci接口，一种是通过ODBC数据源的方式，推荐使用Oralce OCI的方式去访问Oracle数据库。

示例步骤

示例环境
达梦端操作系统： Kylin Linux Advanced Server V10 (Halberd)
达梦数据库版本：DM V8 03134284368-20260306-316451-20149 Pack62
Oracle数据库：Oracle Database 19c Enterprise Edition Release 19.0.0.0.0- Production
Oracle数据库： 11g Express Edition Release 11.2.0.2.0 - 64bit Production

1.1环境准备

略
准备oracle 19c数据库，创建测试表system.test,system.t01
准备oracle 11g数据库，创建测试表system.test1
下载sqldeveloper访问测试
https://www.oracle.com/database/sqldeveloper/technologies/download/
验证可以正常远程登录访问
下载达梦SQLark百灵连接访问测试
–19c 192.168.118.158 1521 orclcdb

–11g 192.168.118.171 1522 xe

1.2OCI方式

1.2.1下载oralce客户端instantclient-basic-linux并上传到达梦数据库机器
https://www.oracle.com/database/technologies/instant-client/downloads.html
https://www.oracle.com/cn/database/technologies/instant-client/linux-x86-32-downloads.html
mkdir -p /app/
unzip instantclient-basic-linux.x64-19.30.0.0.0dbru.zip -d /app/
1.2.2配置环境变量
vi /home/dmdba/.bash_profile
–新增环境变量
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/app/instantclient_19_30
source /home/dmdba/.bash_profile

1.2.3查看是否缺少依赖
su - dmdba
ldd /app/instantclient_19_30/libclntsh.so

ln -s /usr/lib64/libnsl.so.2.0.1 /usr/lib64/libnsl.so.1

1.2.4重启达梦数据库
DmServiceDAMENG restart​​​​​​​

1.2.5sysdba登录数据库创建LINK访问19c
CREATE OR REPLACE LINK “SYSDBA”.“ORACLE_TEST” CONNECT ‘ORACLE’ WITH “SYSTEM” IDENTIFIED BY “123456” USING ‘192.168.118.158:1521/ORCLCDB’;
select * from “SYSTEM”.“TEST”@ORACLE_TEST;
insert into “SYSTEM”.“TEST”@ORACLE_TEST values(2,‘test2’);
commit;
select * from “SYSTEM”.“TEST”@ORACLE_TEST;
select sysdate from DUAL@ORACLE_TEST;

这里注意用户名大写，否则使用时会报错：
-6033: 第1 行附近出现错误:DBLINK连接丢失
1.2.6sysdba登录数据库创建LINK访问11g
CREATE OR REPLACE LINK “SYSDBA”.“ORACLE11G_TEST” CONNECT ‘ORACLE’ WITH “SYSTEM” IDENTIFIED BY “oracle” USING ‘192.168.118.171:1522/XE’;
select * from “SYSTEM”.“TEST1”@ORACLE11G_TEST;
insert into “SYSTEM”.“TEST1”@ORACLE11G_TEST values(2);
commit;
select * from “SYSTEM”.“TEST1”@ORACLE11G_TEST;
select sysdate from DUAL@ORACLE11G_TEST;
select * from V$VERSION@ORACLE11G_TEST;

1.3ODBC方式

1.3.1下载解压instantclient-basic-linux和instantclient-odbc-linux
–下载 、上传到/dmsoft解压
unzip instantclient-basic-linux.x64-19.30.0.0.0dbru.zip -d /app
unzip instantclient-odbc-linux.x64-19.30.0.0.0dbru.zip -d /app
–新增LD_LIBRARY_PATH环境变量路径/app/instantclient_19_30
vi /home/dmdba/.bash_profile
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/app/instantclient_19_30"
1.3.2编译安装ODBC
–上传unixODBC-2.3.0.tar.gz解压后编译安装
tar -xvzf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0/
./configure
make
make install
–检查依赖是否正常
ldd /app/instantclient_19_30/libsqora.so.19.1

1.3.3​​​​​​​配置tnsnames.ora，并配置TNS_ADMIN系统环境变量
vi /app/instantclient_19_30/network/admin/tnsnames.ora
ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.158)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLCDB)
)
)
–配置TNS_ADMIN系统环境变量
vim /etc/profile
export TNS_ADMIN=/app/instantclient_19_30/network/admin
source /etc/profile
注：一定要配置，否则执行isql -v ORA19时一直卡住。
1.3.4配置odbc相关参数-以19c为示例，11g配置类似
[root@localhost ~]# odbcinst -j

root@loaclhost unixODBC-2.3.0]# vi /usr/local/etc/odbcinst.ini
[Oracle ODBC1930]
Description = Oracle ODBC Driver
DRIVER = /app/instantclient_19_30/libsqora.so.19.1

[root@loaclhost unixODBC-2.3.0]# vi /usr/local/etc/odbc.ini
[ORA19]
DSN = 192.168.118.158:1521
Driver = Oracle ODBC1930
ServerName = ORCLCDB
UserID=SYSTEM
Password=123456
TCP_PORT=1521

[root@loaclhost unixODBC-2.3.0]# isql -v ORA19

[root@loaclhost unixODBC-2.3.0]# su - dmdba
[dmdba@loaclhost ~]$ isql -v ORA19

1.3.5示例-达梦中创建LINK访问ORACLE 19c使用
create or replace link “LINKODBC” connect ‘ODBC’ with “SYSTEM” identified by “123456” using ‘ORA19’;
select * from “SYSTEM”.“T01”@LINKODBC;
–查询link信息
select * from DBA_DB_LINKS;

2 DM link访问Sqlserver

示例步骤

示例需求
从本地环境192.168.118.217:5237库中创建link访问远程192.168.2.20 SQL server库中用户sa(HUN_admin2026)模式表t1；

具体步骤

2.1编译安装freetds

tar -xvzf freetds-1.4.27.tar.gz
cd freetds-1.4.27
./configure -prefix=/usr/local/freetds -with-unixodbc=/usr/local/unixODBC
make
make install
–查看版本信息
tsql -C

2.2配置freetds

vi /usr/local/freetds/etc/freetds.conf
[mssqlserver]
host = 192.168.2.20
port = 1433
tds version = 8.0
client charset = UTF-8
–使用freetds测试连接sql server
/usr/local/freetds/bin/tsql -S mssqlserver -U sa -P HUN_admin2025
1> select getdate()
2> go

/usr/local/freetds/bin/tsql -H 192.168.2.20 -p 1433 -U sa -P HUN_admin2025 -D TEST

2.3odbc配置连接sql server

vi /etc/odbcinst.ini
添加内容：
[FreeTDS]
Description = ODBC of FreeTDS for MS SQL
DRIVER = /usr/local/freetds/lib/libtdsodbc.so

vi /etc/odbc.ini
添加内容：
[TEST2dsn]
#Driver = /usr/local/freetds/lib/libtdsodbc.so --直接指定不行
Driver = FreeTDS
Description = My Second Test DSN
Trace = No
Server = 192.168.2.20
Database = TEST
Port = 1433
TDS_Version = 8.0

–测试连接SQLSERVER，使用root、dmdba测试
isql -v TEST2dsn sa HUN_admin2025

2.4 DM访问sql server

create link MSSQL connect ‘ODBC’ with “sa” identified by “HUN_admin2026” using ‘TEST2dsn’;
select * from t1@MSSQL;