创建oracle数据库到达梦数据库的dblink

实际上是通过odbc使oracle连接到达梦数据库，需要安装odbc驱动，达梦驱动（达梦经典安装），配置odbc，透明网关，oracle监听服务

环境：（ redhat6.9，oracle11g，达梦8）的安装配置步骤

一 安装unixODBC
1、安装
[root]# tar -zxvf unixODBC-2.3.9.tar.gz

[root]# cd unixODBC-2.3.9

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

[root]# make && make install

安装成功后，unixODBC所需的头文件被安装到/usr/inlucde目录下，编译好的库文件安装到/usr/lib目录下，与unixODBC相关的可执行文件安装到/usr/bin目录下，配置文件放到了/etc目录下。
如果/usr/lib不在lib path中执行以下命令，并在.bash_profile文件中持久化该path
[root]# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib

2、安装完成之后查看配置信息 
[root]# odbcinst -j
unixODBC 2.3.9
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

二 安装达梦客户端和驱动
1、创建达梦用户
[root]#useradd -m -d /home/dmdba -s /bin/bash dmdba
[root]#passwd dmdba
2、下载并mount安装镜像dm8_20210712_x86_rh6_64_ent.zip
[root]#mkdir -p /home/dmdba/dmiso
[root]#cd /home/dmdba/
[root]#mv /root/dm8_20210712_x86_rh6_64_ent_8.1.2.38_pack1.iso .
[root]#mount -o loop dm8_20210712_x86_rh6_64_ent_8.1.2.38_pack1.iso  dmiso/
[root]#chown dmdba:dmdba -R *
[root]#su - dmdba
#cd dmiso
#./DMInstall.bin -i 
#exit
[root]#/home/dmdba/dmdbms/script/root/root_installer.sh

三 配置odbc.ini

1、配置odbc.ini
[root]# cat /etc/odbc.ini
[dm8]
Driver          = DM8 ODBC DRIVER
Description     = DM ODBC DSND
SERVER          = 10.22.160.94
UID             = XXXXXX
PWD             = XXXXXX
TCP_PORT        = 5236 

2、配置odbcinst.ini
[root]# cat /etc/odbcinst.ini 
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
Driver = /home/dmdba/dmdbms/bin/libdodbc.so
threading = 0

切换到Oracle用户测试

1.把动态库路径添加到环境变量LD_LIBRARY_PATH中，并且修改.bash_profile持久化：
[oracle@]$vim .bash_profile
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/dmdba/dmdbms/bin/

2.使用dmdba用户打开bin文件权限
#su - dmdba
#cd /home
#chmod -R 755 dmdba
使用oracle用户测试打开dm8连接
source .bash_p
oracle@p4oa15dbtest:/home/oracle>isql -v dm8

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

四 配置透明网关

Oracle服务器11g之后自带透明网关，新建配置文件initdm8.ora：

[oracle]$ cd $ORACLE_HOME/hs/admin
[oracle]$ ls
extproc.ora  initdg4odbc.ora  initdm8.ora  listener.ora.sample  tnsnames.ora.sample
[oracle]$ cat initdm8.ora
HS_FDS_CONNECT_INFO = dm8
HS_FDS_TRACE_LEVEL = on
set ODBCINI=/etc/odbc.ini
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so
HS_LANGUAGE="simplified chinese_china.al32utf8"
HS_NLS_NCHAR=UCS2

五 配置Oracle监听

1、配置静态监听：
执行vim $ORACLE_HOME/network/admin/listener.ora（没有的话创建一个） 配置静态监听：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (ORACLE_HOME = /opt/oracle/products/11.2.0)
      (SID_NAME = dm8) 
    )
    (SID_DESC =
   (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /opt/oracle/products/11.2.0)
      (SID_NAME = orcl)
  )

)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.22.160.94)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC1521))
    )
  )

2、配置tnsnames.ora

vim $ORACLE_HOME/network/admin/tnsnames.ora

dm8=
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.22.160.94)(PORT = 1521))
         (CONNECT_DATA =
          (SID = dm8) 
         )
         (HS = OK)
  )

3、测试之后tnsping dm8测试检查
[oracle]$ tnsping dm8

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-NOV-2021 10:26:45

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.22.160.94)(PORT = 1521)) (CONNECT_DATA = (SID = dm8)) (HS = OK))
OK (0 msec)

4、配置完成之后重启监听文件

停止监听 lsnrctl stop；

启动监听 lsnrctl start；

查看监听状态 lsnrctl status；

重启之后你会发现有一个DM8的实例被监听，状态为unknown；

[oracle]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-NOV-2021 10:27:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.22.160.94)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-NOV-2021 22:46:39
Uptime                    0 days 11 hr. 40 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/products/11.2.0/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/p4oa15dbtest/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.23.137.94)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "dm8" has 1 instance(s).
  Instance "dm8", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

六 创建dblink
create  database link  dmtestlink  connect to XXXXXX identified by XXXXXX using 'dm8';
select * from v$version@dmtestlink;