ORACLE到达梦数据库DBLINK配置案例

一、说明
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

三、透明网关配置
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

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;