达梦通过DBLINK访问Oracle数据库有两种方式一种是通过Oracle oci接口，一种是通过ODBC数据源的方式。
推荐达梦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目录下
复制
注意：
如果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
[Oracle ODBC1913] Description = Oracle ODBC Driver Driver = /opt/oracle/instantclient_19_13/libsqora.so.19.1 Setup = FileUsage = CPTimeout = CPReuse =
复制
3、配置odbc.ini
[ORA11] ---主要配置项 DSN = ORA11 ---主要配置项 Driver = Oracle ODBC1913 ---主要配置项（odbcinst.ini中的驱动配置名称） ServerName = DB10 ---主要配置项 （tnsnames.ora网络服务名） UserID = AggregateSQLType = FLOAT Application Attributes = T Attributes = W BatchAutocommitMode = IfAllSuccessful BindAsFLOAT = F CacheBufferSize = 20 CloseCursor = F DisableDPM = F DisableMTS = T DisableRULEHint = T EXECSchemaOpt = EXECSyntax = T Failover = T FailoverDelay = 10 FailoverRetryCount = 10 FetchBufferSize = 64000 ForceWCHAR = F LobPrefetchSize = 8192 Lobs = T Longs = T MaxLargeData = 0 MaxTokenSize = 8192 MetadataIdDefault = F QueryTimeout = T ResultSets = T SQLGetData extensions = F SQLTranslateErrors = F StatementCache = F Translation DLL = Translation Option = 0 UseOCIDescribeAny = F
复制
4、测试ODBC访问Oracle数据库
[dmdba@localhost ~]$ isql -v ORA11 scott 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数据库。
369
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
