1. 达梦通过 DBLINK 访问 Oracle 数据库有两种方式

   • 通过 Oracle oci 接口
   • 通过 ODBC 数据源的方式
   • 推荐使用 Oralce OCI 的方式去访问 Oracle 数据库

2. 环境准备

   • 示例环境

     达梦：麒麟 V10 系统、DM8 数据库

     Oracle: oracle19c、centos7

   • 安装 Oracle 客户端

     oracle 官网

     下载如下压缩包

     instantclient-basic-linux.x64-19.31.0.0.0dbru.zip

     instantclient-odbc-linux.x64-19.31.0.0.0dbru.zip

   • 安装 Oracle client

     unzip instantclient-basic-linux.x64-19.31.0.0.0dbru.zip
     
     su - dmdba
     
     vim .bash_profile 添加如下内容
     
     export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/oracle/instantclient_19_13"
     
     ldd /opt/oracle/instantclient_19_31/libclntsh.so
             linux-vdso.so.1 (0x00007ffedf7b4000)
             libnnz19.so => /opt/oracle/instantclient_19_31/libnnz19.so (0x00007fd24d436000)
             libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007fd24d416000)
             libm.so.6 => /usr/lib64/libm.so.6 (0x00007fd24d293000)
             libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007fd24d272000)
             libnsl.so.1 => /usr/lib64/libnsl.so.1 (0x00007fd24d257000)
             librt.so.1 => /usr/lib64/librt.so.1 (0x00007fd24d24c000)
             libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007fd24d245000)
             libresolv.so.2 => /usr/lib64/libresolv.so.2 (0x00007fd24d22c000)
             libc.so.6 => /usr/lib64/libc.so.6 (0x00007fd24d074000)
             /lib64/ld-linux-x86-64.so.2 (0x00007fd251dc7000)
             libclntshcore.so.19.1 => /opt/oracle/instantclient_19_31/libclntshcore.so.19.1 (0x00007fd24cade000)
     
     #没有缺失代表无依赖问题，在dmdba用户下重启数据库
     dmdbms/bin/DmServiceDMSERVER restart
     

3. 通过 oracle oci 接口访问

CREATE OR REPLACE LINK orlink CONNECT 'ORACLE' WITH "SYSTEM" IDENTIFIED BY "Dameng123" USING '192.168.163.130:1521/orcl';

SQL> select * from v$version@orlink;

行号       BANNER
---------- ----------------------------------------------------------------------
           BANNER_FULL
           -----------------------------------------------------------------------------------------
           BANNER_LEGACY                                                          CON_ID
           ---------------------------------------------------------------------- ------
1          Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0


SQL> insert into test2@orlink values(4);

SQL> commit;

SQL> select * from test2@orlink;

行号       ID
---------- --
1          4

4. ODBC 配置

   unzip instantclient-odbc-linux.x64-19.30.0.0.0dbru.zip -d /opt/oracle
   
   su - dmdba
   
   [dmdba@kydm4 admin]$ cat /opt/oracle/instantclient_19_31/network/admin/tnsnames.ora
   ORCL =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.130)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
       )
     )
   
   ###配置TNS_ADMIN系统环境变量,此配置必须要有
   [root@localhost ~]# vim .bash_profile    ---添加下面export
   export TNS_ADMIN=/opt/oracle/instantclient_19_13/network/admin
   
   source .bash_profile
   
   odbcinst -j
   unixODBC 2.3.7
   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
   
    cat /etc/odbcinst.ini
   [ORACLE19C ODBC]
   Description     = Oracle ODBC driver
   Driver          = /opt/oracle/instantclient_19_31/libsqora.so.19.1
   
   
   cat /etc/odbcinst.ini
   [ORA]
   DSN = 192.168.163.130:1521
   Driver = ORACLE19C ODBC
   ServerName = orcl
   UserID=SYSTEM
   Password=Dameng123
   TCP_PORT=1521
   
   
   #测试
   [dmdba@kydm4 ~]$ isql -v ORA
   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL> SELECT * FROM V$VERSION;
   +---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+-----------------------------------------+
   | BANNER                                                                          | BANNER_FULL                                                                                                                                                     | BANNER_LEGACY                                                                   | CON_ID                                  |
   +---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+-----------------------------------------+
   | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production          | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
   Version 19.3.0.0.0                                                                       | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production          | 0                                       |
   +---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+-----------------------------------------+
   SQLRowCount returns -1
   1 rows fetched
   
   
   
   

5. 通过ODBC创建LINK

   create link "LINKODBC" connect 'ODBC' with "SYSTEM" identified by "Dameng123" using 'ORA';
   
   
   SQL> select * from test2@LINKODBC;
   
   行号       ID
   ---------- --
   1          1
   2          4
   
   

6. 总结

   • 使用Oracle OCI方式，需要重启数据库加载Oracle OCI驱动，才能正常使用DBLINK。配置相对简单。

   • 使用ODBC数据源方式，需先配置ODBC相关配置，在配置ORACLE TNS 相关，才能正常使用DBLINK。

   通过测试对比，建议使用Oracle OCI方式来创建DM DBLINK访问Oracle数据库。