创建达梦到Oracle的DBLINK（OCI方式）

二狗不懂IT
已于 2023-03-07 16:28:11 修改
阅读量1.6k
点赞数
分类专栏：达梦数据库文章标签：数据库 centos database
于 2022-05-17 16:00:58 首次发布
本文链接：https://blog.csdn.net/baidu_41727061/article/details/124823583
版权
达梦数据库专栏收录该内容
16 篇文章 0 订阅
订阅专栏
应用场景：
因业务需求：oracle作为目地端，DM作为使用端，需要在达梦里创建到oracle的dblink连接。以下为实操记录。


推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。
推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。
推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。
一、下载Oracle客户端驱动（Instant Client）
下载地址：Oracle Instant Client Downloads
目前Oracle Instant Client驱动包已经支持ARM架构平台环境。
选择对应平台的Basic和ODBC驱动包：
Basic Package（instantclient-basic） *必须
ODBC Package （instantclient-odbc）*可选
另外，还需要注意Oracle Instant Client高版本包对glibc版本有要求，需要glibc 2.14或者以上环境
本次测试环境：
操作系统版本：Centos 7
Oracle数据库版本：Oracle 11.2.0.4
DM数据库版本：DM V8 1-2-70 ENT
Oracle Instant Client包版本：19.13
数据库字符集：Oracle ZHS16GBK，DM GB18030
二、查看系统环境
1、检查glibc版本
[root@localhost ~]# rpm -qi glibc
Name        : glibc
Version     : 2.17
Release     : 307.el7.1
Architecture: x86_64
Install Date: Mon 02 Aug 2021 03:28:10 PM CST
Group       : System Environment/Libraries
Size        : 14100570
License     : LGPLv2+ and LGPLv2+ with exceptions and GPLv2+
Signature   : RSA/SHA256, Sat 04 Apr 2020 04:52:25 AM CST, Key ID 24c6a8a7f4a80eb5
Source RPM  : glibc-2.17-307.el7.1.src.rpm
Build Date  : Wed 01 Apr 2020 06:33:57 AM CST
Build Host  : x86-01.bsys.centos.org
Relocations : (not relocatable)
Packager    : CentOS BuildSystem <http://bugs.centos.org>
Vendor      : CentOS
URL         : http://www.gnu.org/software/glibc/
Summary     : The GNU libc libraries
Description :
The glibc package contains standard libraries which are used by
multiple programs on the system. In order to save disk space and
memory, as well as to make upgrading easier, common system code is
kept in one place and shared between programs. This particular package
contains the most important sets of shared libraries: the standard C
library and the standard math library. Without these two libraries, a
Linux system will not function.

[root@localhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.


2、检查是否有安装libaio包
[root@localhost ~]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64

三、解压安装Oracle Instant Client包
上传安装包到服务器，然后创建目录并解压
[root@localhost ~]# mkdir -p /opt/oracle
[root@localhost ~]# unzip instantclient-basic-linux.x64-19.13.0.0.0dbru.zip -d /opt/oracle
[root@localhost ~]# unzip instantclient-odbc-linux.x64-19.13.0.0.0dbru.zip -d /opt/oracle
[root@localhost oracle]# cd instantclient_19_13/
[root@localhost instantclient_19_13]# pwd
/opt/oracle/instantclient_19_13
##所有的驱动包都在/opt/oracle/instantclient_19_13目录下


image-20211025115130032
注意：
如果Instant Client安装包是18.3 之前的版本，还需要创建几个动态库的软链接。如下：cd /opt/oracle/instantclient_12_2
ln -s libclntsh.so.12.1 libclntsh.so
ln -s libocci.so.12.1 libocci.so

四、配置环境变量
-添加到/etc/ld.so.conf.d目录下
[root@localhost ~]# echo /opt/oracle/instantclient_19_13 > /etc/ld.so.conf.d/oracle-instantclient.conf
[root@localhost ~]# cat /etc/ld.so.conf.d/oracle-instantclient.conf
/opt/oracle/instantclient_19_13
[root@localhost ~]# ldconfig

-配置LD_LIBRARY_PATH环境变量
修改dmdba用户下的.bash_profile文件，添加如下内容：
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/oracle/instantclient_19_13"

生效：
[dmdba@localhost ~]$ source .bash_profile

查看：
env 或者env |grep LD_LIBRARY_PATH
五、通过ORACLE OCI接口的方式访问
上面配置成功完成后，达梦数据库可以使用Oracle OCI接口进行访问。创建语法以及使用方法可以参考《DM8 SQL语言使用手册》外部链接章节。Oracle OCI接口相关动态库文件在instantclient-basic包中，配置好上面的环境变量后可以直接创建DBLINK访问。
DBLINK创建语句：
create link "LINKORA" connect 'oracle' with "SCOTT" identified by "oracle"  using '192.168.15.10:1521/orcl';
##注意用户名密码大小写

测试：
---Oracle数据库，创建测试表
sqlplus / as sysdba
create user scott identified by oracle;
grant dba to scott;
conn scott/oracle
create table scott.T1
(
  name  VARCHAR2(20),
  age   INTEGER,
  sex   VARCHAR2(3),
  grade INTEGER
);
insert into scott.t1 values('丽丽',18,'女',1000);
commit;


---DM数据库测试
---重启数据库加载Oracle OCI驱动
[dmdba@localhost ~]$ cd $DM_HOME/bin
[dmdba@localhost bin]$ ./DmServiceDMSERVER restart
[dmdba@localhost bin]$ ./disql
disql V8
username:
password:

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 17.706(ms)
/***创建访问Oracle数据库的DBLINK***/
SQL> create link "LINKORA" connect 'oracle' with "SCOTT" identified by "oracle"  using '192.168.15.10:1521/orcl';
executed successfully
used time: 224.920(ms). Execute id is 53700.

/***测试***/
SQL> select * from v$version@linkora;

LINEID     BANNER                                                                      
---------- ----------------------------------------------------------------------------
1          Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2          PL/SQL Release 11.2.0.4.0 - Production
3          CORE 11.2.0.4.0 Production
4          TNS for Linux: Version 11.2.0.4.0 - Production
5          NLSRTL Version 11.2.0.4.0 - Production

used time: 38.224(ms). Execute id is 54000.

SQL> SELECT * FROM t1@linkora;

LINEID     NAME   AGE SEX GRADE
---------- ------ --- --- -----
1          丽丽 18  女 1000

used time: 11.281(ms). Execute id is 54004.

SQL> select * from emp@linkora;

LINEID     EMPNO ENAME  JOB       MGR  HIREDATE            SAL     COMM    DEPTNO
---------- ----- ------ --------- ---- ------------------- ------- ------- ------
1          7369  SMITH  CLERK     7902 1980-12-17 00:00:00 800.00  NULL    20
2          7499  ALLEN  SALESMAN  7698 1981-02-20 00:00:00 1600.00 300.00  30
3          7521  WARD   SALESMAN  7698 1981-02-22 00:00:00 1250.00 500.00  30
4          7566  JONES  MANAGER   7839 1981-04-02 00:00:00 2975.00 NULL    20
5          7654  MARTIN SALESMAN  7698 1981-09-28 00:00:00 1250.00 1400.00 30
6          7698  BLAKE  MANAGER   7839 1981-05-01 00:00:00 2850.00 NULL    30
7          7782  CLARK  MANAGER   7839 1981-06-09 00:00:00 2450.00 NULL    10
8          7839  KING   PRESIDENT NULL 1981-11-17 00:00:00 5000.00 NULL    10
9          7844  TURNER SALESMAN  7698 1981-09-08 00:00:00 1500.00 0       30
10         7900  JAMES  CLERK     7698 1981-12-03 00:00:00 950.00  NULL    30
11         7902  FORD   ANALYST   7566 1981-12-03 00:00:00 3000.00 NULL    20

LINEID     EMPNO ENAME  JOB   MGR  HIREDATE            SAL     COMM DEPTNO
---------- ----- ------ ----- ---- ------------------- ------- ---- ------
12         7934  MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 NULL 10

12 rows got

used time: 7.949(ms). Execute id is 54005.

六、通过ODBC数据源进行访问
使用ODBC方式访问Oracle需要Oracle ODBC驱动包，Oracle odbc驱动上面已解压。
6.1 安装ODBC（如果未安装）
两种方式：源码编译和yum安装
（1）源码编译安装（需要gcc环境）：
[root@localhost ~]# tar -zxvf unixODBC-2.3.9.tar.gz
[root@localhost ~]# cd unixODBC-2.3.9
[root@localhost unixODBC-2.3.9]# ./configure --enable-gui=no
[root@localhost unixODBC-2.3.9]# make
[root@localhost unixODBC-2.3.9]# make install
[root@localhost ~]# odbcinst -j
unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

源码编译安装完成之后，还需要检查oracle odbc的依赖是否正常。
有的时候，将操作系统yum安装的unixODBC卸载之后，可能会导致Oracle的odbc动态库缺少依赖库。如下：
[root@localhost instantclient_19_13]# ldd libsqora.so.19.1 
	linux-vdso.so.1 =>  (0x00007ffeef3cd000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007ffafac45000)
	libm.so.6 => /lib64/libm.so.6 (0x00007ffafa943000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ffafa727000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007ffafa50d000)
	librt.so.1 => /lib64/librt.so.1 (0x00007ffafa305000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007ffafa103000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x00007ffaf9ee9000)
	libclntsh.so.19.1 => /opt/oracle/instantclient_19_13/libclntsh.so.19.1 (0x00007ffaf5d76000)
	libclntshcore.so.19.1 => /opt/oracle/instantclient_19_13/libclntshcore.so.19.1 (0x00007ffaf57d2000)
	libodbcinst.so.2 => not found        ###这里缺少依赖
	libc.so.6 => /lib64/libc.so.6 (0x00007ffaf5404000)
	/lib64/ld-linux-x86-64.so.2 (0x00007ffafb103000)
	libnnz19.so => /opt/oracle/instantclient_19_13/libnnz19.so (0x00007ffaf4d92000)

所以在源码编译安装完成之后，需要将/usr/local/lib或者使用find命令查找系统上相关的库文件将对应路径目录加入到LD_LIBRARY_PATH系统环境变量，或者添加到 /etc/ld.so.conf.d目录下指定的配置文件中，如下：
[root@localhost ~]# echo /usr/local/lib > /etc/ld.so.conf.d/libodbc.conf
[root@localhost ~]# ldconfig

（2）yum安装，配置好yum源之后直接安装即可
[root@localhost ~]# yum install -y unixODBC unixODBC-devel
[root@localhost ~]# 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

6.2 配置ODBC数据源
1、配置tnsnames.ora，并配置TNS_ADMIN系统环境变量
[root@localhost ~]# cat /opt/oracle/instantclient_19_13/network/admin/tnsnames.ora
DB10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

###配置TNS_ADMIN系统环境变量
[root@localhost ~]# vim /etc/profile    ---添加下面export
export TNS_ADMIN=/opt/oracle/instantclient_19_13/network/admin

[root@localhost ~]# source /etc/profile

2、配置odbcinst.ini
odbcinsi.ini:
[Oracle in OraDb11g_home1]
Description = ODBC DRIVER FOR ORACLE
Driver = /opt/odbc/instantclient_11_2/libsqora.so.11.1
Threading = 0

odbc.ini:
[ORACLE]
Description = ORACLE ODBC DSN
Driver = Oracle in OraDb11g_home1
SERVER = 192.168.208.175
UID = DMHS
PWD = DMHS
Servername = ORCL
PORT = 1521

4、测试ODBC访问Oracle数据库
[dmdba@localhost ~]$ isql -v ORACLE
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from emp;
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| EMPNO | ENAME     | JOB      | MGR   | HIREDATE           | SAL      | COMM     | DEPTNO|
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| 7369  | SMITH     | CLERK    | 7902  | 1980-12-17 00:00:00| 800      |          | 20    |
| 7499  | ALLEN     | SALESMAN | 7698  | 1981-02-20 00:00:00| 1600     | 300      | 30    |
| 7521  | WARD      | SALESMAN | 7698  | 1981-02-22 00:00:00| 1250     | 500      | 30    |
| 7566  | JONES     | MANAGER  | 7839  | 1981-04-02 00:00:00| 2975     |          | 20    |
| 7654  | MARTIN    | SALESMAN | 7698  | 1981-09-28 00:00:00| 1250     | 1400     | 30    |
| 7698  | BLAKE     | MANAGER  | 7839  | 1981-05-01 00:00:00| 2850     |          | 30    |
| 7782  | CLARK     | MANAGER  | 7839  | 1981-06-09 00:00:00| 2450     |          | 10    |
| 7839  | KING      | PRESIDENT|       | 1981-11-17 00:00:00| 5000     |          | 10    |
| 7844  | TURNER    | SALESMAN | 7698  | 1981-09-08 00:00:00| 1500     | 0        | 30    |
| 7900  | JAMES     | CLERK    | 7698  | 1981-12-03 00:00:00| 950      |          | 30    |
| 7902  | FORD      | ANALYST  | 7566  | 1981-12-03 00:00:00| 3000     |          | 20    |
| 7934  | MILLER    | CLERK    | 7782  | 1982-01-23 00:00:00| 1300     |          | 10    |
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
SQLRowCount returns -1
12 rows fetched
SQL> select * from t1;
+---------------------+-----------------------------------------+----+-----------------------------------------+
| NAME                | AGE                                     | SEX| GRADE                                   |
+---------------------+-----------------------------------------+----+-----------------------------------------+
| ??                  | 18                                      | ?  | 1000                                    |
+---------------------+-----------------------------------------+----+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> 

###可以看到上面中文会显示乱码，主要与客户端字符集环境有关

6.3 使用ODBC数据源创建DBLINK访问Oracle
DBLINK创建语法：
create link "LINKODBC1" connect 'ODBC' with "SCOTT" identified by "oracle" using 'ORA11';

测试：
[dmdba@localhost ~]$ cd $DM_HOME/bin
[dmdba@localhost bin]$ ./disql
disql V8
username:
password:

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.101(ms)

/***创建DBLINK***/
SQL> create link "LINKODBC1" connect 'ODBC' with "SCOTT" identified by "oracle" using 'ORA11';
executed successfully
used time: 7.763(ms). Execute id is 800.

SQL> select * from t1@linkodbc1;

LINEID     NAME AGE SEX GRADE
---------- ---- --- --- -----
1          ??   18  ?   1000

used time: 678.249(ms). Execute id is 801.
SQL> 
---上面中文问号是由于数据库启动时未加载NLS_LANG环境变量导致，需要在配置NLS_LANG环境变量并生效后重启数据库后可以正常显示

七、总结
DM DBLINK使用Oracle OCI方式和ODBC数据源的方式创建访问Oracle数据库，通过简单的测试对比发现，使用Oracle OCI方式兼容性等都要更好。
使用Oracle OCI方式，如果数据库启动时未加载Oracle OCI驱动，需要重启数据库加载Oracle OCI驱动后，才能正常使用DBLINK。
使用ODBC数据源方式，如果目的表数据有中文，那么需要在DM数据库服务器上配置正确的NLS_LANG环境变量并生效后在当前窗口重启DM数据库。
通过测试对比，建议使用Oracle OCI方式来创建DM DBLINK访问Oracle数据库。
达梦支持
=======================================

有任何问题请到技术社区反馈。

24小时免费服务热线：400 991 6599

达梦技术社区：https://eco.dameng.com
二狗不懂IT
关注
0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
创建达梦到Oracle的DBLINK（OCI方式）

推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。推荐达梦DBLINK使用Oralce OCI的方式去访问Oracle数据库。一、下载Oracle客户端驱动（Instant Client）下载地址：Oracle Instant Client Downloads目前Oracle Instant Client驱动包已经支持ARM架构平台环境。选择对应平台的Basic和ODBC驱动包：Basic .
复制链接

扫一扫
专栏目录