Oracle--DM8的dblink创建

原创已于 2022-04-26 18:06:17 修改 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#dba

于 2022-04-26 18:03:26 首次发布

本文详细指导如何在Oracle 11g环境中配置ODBC驱动，创建达梦DM8的DBLink，并通过实例演示了dblink的创建、测试和验证过程。

Oracle–DM8的dblink的创建

1、环境信息

操作系统	RedHat 7.5
Oracle版本	11g
达梦版本	DM8

IP地址：

Oracle	192.168.111.10
DM	192.168.111.22

2、Oracle端配置

2.1安装ODBC

[root@localhost upload]# ls
unixODBC-2.3.0.tar.gz
[root@localhost upload]# tar zxvf unixODBC-2.3.0.tar.gz
[root@localhost upload]# cd unixODBC-2.3.0
[root@localhost unixODBC-2.3.0]# ./configure --with-iconv-char-enc=GB18030 --enable-iconv=yes
[root@localhost unixODBC-2.3.0]# make && make install
[root@localhost unixODBC-2.3.0]# cd /usr/local/etc/
[root@localhost etc]# ls
ODBCDataSources  odbc.ini  odbcinst.ini

2.2 配置ODBC

[root@localhost etc]# cat odbc.ini 
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = 192.168.111.22
UID = SYSDBA
PWD = SYSDBA
TCP_PORT = 5236
CHARSET = GB18030

[root@localhost etc]# cat odbcinst.ini 
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
Driver = /dmdbms/bin/libdodbc.so  #此处的/dmdbms/bin/是存放了达梦数据库中安装目录下的 bin 目录，需要手工上传到 Oracle 服务器
threading = 0

2.3环境变量修改

[root@localhost etc]# cat odbc.ini 
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = 192.168.111.22
UID = SYSDBA
PWD = SYSDBA
TCP_PORT = 5236
CHARSET = GB18030

[root@localhost etc]# cat odbcinst.ini 
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
Driver = /dmdbms/bin/libdodbc.so  #此处的/dmdbms/bin/是存放了达梦数据库中安装目录下的 bin 目录，需要手工上传到 Oracle 服务器
threading = 0
2.3 添加环境变量库文件路径并使环境变量生效
[oracle@localhost ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$LD_LIBRARY_PATH:/dmdbms/bin
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
umask 022
export PATH
[oracle@localhost ~]$ source .bash_profile

在这里插入图片描述
2.4修改文件属性

[root@localhost etc]# chmod 755 /usr/local/etc/odbc.ini 
[root@localhost etc]# chmod 755 /usr/local/etc/odbcinst.ini 
[root@localhost etc]#  chown oracle:oinstall /usr/local/etc/odbc.ini
[root@localhost etc]#  chown oracle:oinstall /usr/local/etc/odbcinst.ini

2.5登录测试

[oracle@localhost ~]$ isql -v dm8
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select id_code;
+---------------------------------------------------------------------------------------------------------------------------------+
| ID_CODE                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------+
| 1-1-88-20.06.24-123627-ENT                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched

2.6 编辑透明网关文件

[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/db_home/hs/admin/
[oracle@localhost admin]$ ls
extproc.ora  initdg4odbc.ora  listener.ora.sample  tnsnames.ora.sample
[oracle@localhost admin]$ vi initDM8.ora
[oracle@localhost admin]$ cat initDM8.ora 
HS_FDS_CONNECT_INFO = DM8
HS_FDS_TRACE_LEVEL = 255
set ODBCINI=/usr/local/etc/odbc.ini
HS_FDS_SHAREABLE_NAME =/usr/local/lib/libodbc.so
HS_LANGUAGE =American_America.ZHS16GBK

2.7 配置静态监听listener.ora和 tnsnames.ora（如果文件不存在则新建）
如果直接复制文件内容粘贴到文件中以后监听重启失败；
解决方法：
把/u01/app/oracle/product/11.2.0/db_home/hs/admin/下的listener.ora.sample文件复制到/u01/app/oracle/product/11.2.0/db_home/network/admin目录下，并修改相应路径，
添加一行参数：(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_home/lib:/lib:/usr/lib:/dmdbms/bin)

[oracle@localhost admin]$ pwd
/u01/app/oracle/product/11.2.0/db_home/network/admin

[oracle@localhost admin]$ cat listener.ora 
# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=DM8)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
          (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_home/lib:/lib:/usr/lib:/dmdbms/bin)
         (PROGRAM=dg4odbc)
      )
  )
 
 [oracle@localhost admin]$ cat tnsnames.ora 
DM8 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.10)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = DM8)
 (SID = DM8)
 )
 (HS=OK)
 )

2.8 重启监听并测试是否可以识别

[oracle@localhost ~]$ lsnrctl stop  |  start  |  status

[oracle@localhost admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-NOV-2020 11:09:25

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

Starting /u01/app/oracle/product/11.2.0/db_home/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                25-NOV-2020 11:09:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "DM8" has 1 instance(s).
  Instance "DM8", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


测试是否可以识别：
[oracle@localhost ~]$ tnsping DM8

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 18:07:38

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.10)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DM8)) (HS=OK))
OK (0 msec)

2.9 创建dblink并验证是否可用

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 18:08:06 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create public database link DMLINK connect to "SYSDBA" identified by "SYSDBA" USING 'DM8';

Database link created.

SQL> select * from v$version@dmlink;

BANNER
--------------------------------------------------------------------------------
DM Database Server 64 V8
DB Version: 0x7000a

SQL> select * from aaa@dmlink;

	ID
----------
       222
       444

SQL>