微信公众号： 老杜随笔
如有问题或建议，请公众号留言;
**[如果你觉得对你有帮助，欢迎关注

一、说明

ORACLE到达梦数据库的DBLINK，基于ORACLE透明网关+ODBC协议连接达梦数据库。以下为创建DBLINK过程案例。

二、配置ORACLE到DM的DBLINK

2.1 环境说明

源数据库： ORACLE19.3
目标数据库： DMV8.1.0.147
源主机ip：LINUX 192.168.137.102
DM主机ip：LINUX 192.168.137.101

2.2 安装DM客户端

在ORACLE服务器安装DM客户端（详达梦数据库（DM8）安装)。

2.3 安装unixODBC

2.3.1、下载unixODBC

https://www.unixodbc.org/
在右边导航选择Download 选项，即可下载unixODBC-2.3.12.tar.gz。

2.3.2安装odbc

[root@oradb unixODBC-2.3.12]# pwd

/root/unixODBC-2.3.12

[root@oradb unixODBC-2.3.12]# ./configure --prefix=/usr/local/unixODBC-2.3.0 --includedir=/usr/include --libdir=/usr/lib --bindir=/usr/bin --sysconfdir=/etc

[root@oradb unixODBC-2.3.12]# make && make install

Libraries have been installed in:

/usr/lib64

touch /etc/odbcinst.ini

touch /etc/odbc.ini

安装成功后，unixODBC所需的头文件被安装到/usr/inlucde目录下，编译好的库文件安装到/usr/lib目录下，与unixODBC相关的可执行文件安装到/usr/bin目录下，配置文件放到了/etc目录下。

2.4配置ODBC

2.4.1 查看ODBC相关配置路径

[root@oradb ~]#  odbcinst -j

odbcinst: error while loading shared libraries: libodbcinst.so.2: cannot open shared object file: No such file or directory

报错解决：ldconfig命令解决

[root@oradb ~]# ldconfig  -- 手动执行其目的为了让动态链接库为系统所共享

[root@oradb ~]#  odbcinst -j

unixODBC 2.3.12

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

2.4.2配置达梦数据库驱动路径及连接

[root@oradb ~]# cat /etc/odbcinst.ini

[DM8 ODBC DRIVER]

Description = ODBC DIRVER FOR DM8

DRIVER = /home/dmdba/dmdbms/bin/libdodbc.so

threading     = 0

[root@oradb ~]# cat /etc/odbc.ini

[dm8]

Driver       = DM8 ODBC DRIVER

Description  = DM ODBC DSN

SERVER  = 192.168.137.101

UID         = SYSDBA

PWD     = SYSDBA

TCP_PORT     = 5236

2.4.3 Oracle用户环境变量配置

[oracle@oradb ~]$ cat  .bash_profile

export PATH

export ORACLE_SID=db19c

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1

export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib/:/home/dmdba/dmdbms/bin

2.4.4切换到Oracle用户验证连接达梦数据库

[oracle@oradb ~]$  isql -v dm8 

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| echo [string]                         |

| quit                                  |

|                                       |

+---------------------------------------+

三、透明网关配置

3.1新建initdm8.ora

[oracle@oradb ~]$ cat $ORACLE_HOME/hs/admin/initdm8.ora

HS_FDS_CONNECT_INFO=dm8

HS_FDS_TRACE_LEVEL = off 


#HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

HS_FDS_SHAREABLE_NAME=/home/dmdba/dmdbms/bin/libdodbc.so

HS_LANGUAGE=
"SIMPLIFIED CHINESE_CHINA.ZHS16GBK" 

HS_NLS_NCHAR=UCS2

set ODBCINI=/etc/odbc.ini


#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 

3.2配置监听

[oracle@oradb ~]$ cat  $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.



LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oradb)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (PROGRAM = dg4odbc)

      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)

      (SID_NAME = dm8) 

    )

    (SID_DESC =

   (GLOBAL_DBNAME = db19c)

      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)

      (SID_NAME = db19c)

  )

)

3.3配置TNS

[oracle@oradb ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

dm8=

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.102)(PORT = 1521))

         (CONNECT_DATA =

          (SID = dm8)

         )

         (HS = OK)

  )

)

)

重启监听

[oracle@oradb ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-APR-2024 15:05:43



Copyright (c) 1991, 2019, Oracle.  All rights reserved.



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                18-APR-2024 12:35:57

Uptime                    0 days 2 hr. 29 min. 46 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oradb/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "160d6706c2ada034e0657fb56a6d205f" has 1 instance(s).

  Instance "db21c", status READY, has 1 handler(s) for this service...

Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).

  Instance "db21c", status READY, has 1 handler(s) for this service...

Service "db19c" has 2 instance(s).

  Instance "db19c", status UNKNOWN, has 1 handler(s) for this service...

  Instance "db19c", status READY, has 1 handler(s) for this service...

Service "db19cXDB" has 1 instance(s).

  Instance "db19c", status READY, has 1 handler(s) for this service...

Service "db21c" has 1 instance(s).

  Instance "db21c", status READY, has 1 handler(s) for this service...

Service "db21cXDB" has 1 instance(s).

  Instance "db21c", status READY, has 1 handler(s) for this service...

Service "dbpdb" has 1 instance(s).

  Instance "db21c", status READY, has 1 handler(s) for this service...

Service "dm8" has 1 instance(s).

  Instance "dm8", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

3.4创建DBLINK

SQL> create  public  database link  dmlink  connect to "SYSDBA" identified by SYSDBA using 'dm8';



Database link created.

3.5测试

SQL> select * from v$version@dmlink; 

select * from v$version@dmlink                      *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from DMLINK

报错解决方法：

#HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

HS_FDS_SHAREABLE_NAME=/home/dmdba/dmdbms/bin/libdodbc.so-- odbc目录

SQL>

SQL> select * from v$version@dmlink;



BANNER

--------------------------------------------------------------------------------

DM Database Server x64 V8.1.0.147-Build(2019.03.27-104581)ENT

DB Version: 0x7000a

SQL>

SQL> select  * from  tt@dmlink;



       ID

----------

        1

创建view

SQL> 

SQL> create  or replace  view tt_dm as

  2  select  * from  tt@dmlink;



View created.



SQL> select  * from  tt_dm;



        ID

----------

         1