人大金仓（kes）o2k的dblink安装部署

安装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 行记录)