安装dblink步骤:
1.环境部署目录:
mkdir -p /home/kingbase/oracle/
2..下载Oracleinstantclien及ODBC
下载网址:
zip:https://www.oracle.com/database/technologies/instant-client/downloads.html
rpm:https://pkgs.org/download/python-cryptography
#rpm -ivh libtool-ltdl-2.4.2-22.el7_3.x86_64.rpm
#rpm -ivh unixODBC-2.3.1-14.el7.x86_64.rpm
$cd /home/kingbase/oracle/
$unzip instantclient-basic-linux.x64-19.15.0.0.0dbru-2.zip
$unzip instantclient-odbc-linux.x64-19.15.0.0.0dbru-2.zip
按照规划命名目录
mv instantclient_19_15 instantclient
2.查看odbc版本
[root@localhost ~]# isql --version
unixODBC 2.3.1
3.查看odbc配置文件目录
[root@localhost ~]# odbcinst -j
unixODBC 2.3.1
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
4.修改odbcinst.ini文件
[root@localhost ~]# cat /etc/odbcinst.ini
#末尾添加
[Oracle ODBC Driver]
Description = ODBC for Oracle
Driver = /home/kingbase/oracle/instantclient/libsqora.so.19.1
5.创建odbc.ini文件
[root@localhost ~]# cat /etc/odbc.ini
[orcl]
Description=Oracle
Driver=Oracle ODBC Driver
ServerName=ORCLPDB1
UserID=system
Password=123456
注:ServerName名字可以随便取
6.修改.odbc.ini文件
[root@localhost ~]# cat /root/.odbc.ini
[orcl]
Description=Oracle
Driver=Oracle ODBC Driver
ServerName=ORACLE_PDB
UserID=system
Password=123456
注:与/etc/odbc.ini文件内容一样
7.准备连接oracle的tnsnames.ora
[kingbase@localhost ~]$ cp oracle/instantclient/tnsnames.ora oracle/instantclient/network/admin/tnsnames.ora
[kingbase@localhost ~]$ cat /home/kingbase/oracle/instantclient/network/admin/tnsnames.ora
ORACLE_PDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPDB1)
)
)
注:ORACLE_PDB修改为与/etc/odbc.ini文件中ServerName一样
8.驱动验证
[root@localhost ORCL]# ldd /home/kingbase/oracle/instantclient/libsqora.so.19.1
linux-vdso.so.1 => (0x00007ffffbbbb000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f00d850b000)
libm.so.6 => /lib64/libm.so.6 (0x00007f00d8209000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f00d7fed000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f00d7dd3000)
librt.so.1 => /lib64/librt.so.1 (0x00007f00d7bcb000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f00d79c9000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f00d77b0000)
libclntsh.so.19.1 => /usr/lib/oracle/19.17/client64/lib/libclntsh.so.19.1 (0x00007f00d366e000)
libclntshcore.so.19.1 => /usr/lib/oracle/19.17/client64/lib/libclntshcore.so.19.1 (0x00007f00d30ca000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f00d2eb8000)
libc.so.6 => /lib64/libc.so.6 (0x00007f00d2aea000)
/lib64/ld-linux-x86-64.so.2 (0x00007f00d89c9000)
libnnz19.so => /usr/lib/oracle/19.17/client64/lib/libnnz19.so (0x00007f00d2475000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f00d226b000)
9.修改环境变量
[root@localhost ~]# cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_HOME=/home/kingbase/oracle/instantclient
export TNS_ADMIN=/home/kingbase/oracle/instantclient/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export ORACLE_SID=ORCLPDB1
export PATH
#source .bash_profile
10.检查是否可以连接识别
[root@localhost ~]# isql -v orcl
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
11.修改kingbase.conf文件
vim /opt/Kingbase/ES/V8/data/kingbase.conf
修改shared_preload_libraries = 参数,在kdb_ora_expr后或末尾加上kdb_database_link插件
---此步骤需要重启kingbase数据库集群才生效
12.进入数据库建立扩展
create extension oracle_fdw;
create extension kdb_database_link;
13.修改sys_database_link.conf文件
[kingbase@localhost ~]$ cat /opt/Kingbase/ES/V8/data/sys_database_link.conf
#末尾添加
[oradb]
dbtype=Oracle
dbname=ORCLPDB1
DriverName="Oracle ODBC Driver"
host=192.168.0.11
port=1525
注:dbname为oracle实例名
12.创建数据链并验证
[kingbase@localhost ~]$ ksql test2 system
ksql (V8.0)
输入 "help" 来获取帮助信息.
test2=# drop database link public.orcl;
DROP DATABASE LINK
test2=# create public database link orcl connect to 'SYSTEM' identified by '123456' using 'oradb';
CREATE DATABASE LINK
test2=# select count(*) FROM sys.COL@orcl;
count
-------
5993
(1 行记录)