ORACLE到达梦数据库DBLINK配置

ORACLE到达梦数据库DBLINK配置

1. ORACLE到达梦数据库DBLINK说明

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

2. 配置ORACLE到DM的DBLINK

2.1 环境说明

源数据库： ORACLE 11.0.2.4
目标数据库： DM8

2.2 安装DM客户端

在ORACLE服务器安装DM客户端（此安装过程略），安装后生成的$DM_HOME/libdodbc.so文件即DM数据库的ODBC驱动，本案例路径为：/opt/dmdba/dmdbms/bin/libdodbc.so，后面配置ODBC要用到。

2.3 安装unixODBC

在ORACLE服务器安装unixODBC，库文件路径指定/usr/lib64，会省去LD_LIBRARY_PATH等环境变量配置。

[oracle@centos7 ~]$ tar xf unixODBC-2.3.2.tar
[oracle@centos7 ~]$ cd unixODBC-2.3.2/
[oracle@centos7 unixODBC-2.3.2]$ ./configure --prefix=/usr/local/unixODBC-2.3.0 --includedir=/usr/include --libdir=/usr/lib64 --bindir=/usr/bin --sysconfdir=/etc
[oracle@centos7 unixODBC-2.3.2]$ make
[oracle@centos7 unixODBC-2.3.2]$ su
[root@centos7 unixODBC-2.3.2]# make install
Libraries have been installed in:
/usr/lib64
touch /etc/odbcinst.ini
touch /etc/odbc.ini

2.4 配置ODBC

2.4.1 查看ODBC相关配置路径

[root@centos7 ~]# odbcinst -j
unixODBC 2.3.2
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@centos7 ~]# vi /etc/odbcinst.ini
[DM8 ODBC DRIVER]
Description = ODBC DIRVER FOR DM8
DRIVER = /opt/dmdba/dmdbms/bin/libdodbc.so

2.4.3 配置达梦数据库连接

[root@centos7 ~]# vi /etc/odbc.ini
[dm8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = 192.168.228.228
UID = test
PWD = 123456789
TCP_PORT = 5236

2.4.4 Oracle用户环境变量配置

将ODBC和DM8的库路径加入LD_LIBRARY_PATH：

[oracle@centos7 ~]$ vi .bash_profile
export LD_LIBRARY_PATH=/usr/lib64:/opt/dmdba/dmdbms/bin:$LD_LIBRARY_PATH

[oracle@centos7 ~]$ source .bash_profile

2.4.5 验证连接达梦数据库

[oracle@centos7 ~]$ isql -v dm8 TEST 123456789
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+

2.5 透明网关配置

[oracle@centos7 ~]$ vi $ORACLE_HOME/hs/admin/initdm8.ora
HS_FDS_CONNECT_INFO=dm8
HS_FDS_TRACE_LEVEL = debug #生产环境配置off
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_LANGUAGE="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" #语言根据自己环境修改
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini

2.6 配置监听

[oracle@centos7 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = dg4odbc) #dg4odbc是Oracle的透明网关协议无需修改
(ORACLE_HOME = /opt/oracle/product/11g/oradb1)
(SID_NAME = dm8) #名称保持跟透明网关的SID一致
(ENV="LD_LIBRARY_PATH=/usr/lib64:/opt/dmdba/dmdbms/bin:$ORACLE_HOME/lib") #这行如果配置了系统环境变量可以不配置
)
(SID_DESC =
(GLOBAL_DBNAME = oradb1) #本地数据库实例名称
(ORACLE_HOME = /opt/oracle/product/11g/oradb1)
(SID_NAME = oradb1)
)
)

配置后重启监听：

停止监听: lsnrctl stop
启动监听: lsnrctl start

2.7 配置TNS

[oracle@centos7 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
dm8 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)
)
(CONNECT_DATA=(SID=dm8))
(HS=OK)
)

通过TNSPING检查TNS配置：

[oracle@centos7 ~]$ tnsping dm8
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JUN-2023 22:06:54
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=localhost) (PORT=1521)) (CONNECT_DATA=(SID=dm8)) (HS=OK))
OK (0 msec)

2.8 创建DBLINK

create public database link ora_to_dm connect to TEST identified by "123456789" USING 'dm8';

2.9 验证链接

返回记录则说明链接正常：

SQL> select * from v$version@ora_to_dm;
BANNER
DM Database Server 64 V8
DB Version: 0x7000c
03134284044-20230420-188270-20040