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>