在Oracle中使用DBLink连接PostgreSQL和KingbaseES数据库测试文档

最新推荐文章于 2023-07-26 22:18:57 发布

肉啃肉er

最新推荐文章于 2023-07-26 22:18:57 发布

阅读量994

点赞数 1

本文链接：https://blog.csdn.net/icreasy3/article/details/114832968

版权

PostgreSQL 同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

Linux

13 篇文章 0 订阅

订阅专栏

Oracle

2 篇文章 0 订阅

订阅专栏

测试环境

服务器：1台（VMware虚拟机，配置不低于2C/4GB/60GB/1Nic）
操作系统版本：Oracle Linux 7.4 64-bit
Oracle数据库版本：12c（12.2）
KingbaseES版本：V8R3（B0160）
PostgreSQL ODBC驱动版本：9.6

操作系统要求

安装时采用最小化安装即可；
安装时添加中文字符集支持；
关闭kdump功能；
系统时区设置为Asia/Shanghai；
文件系统划分：SWAP空间不低于4GB，/文件系统不低于20GB。
关闭SELinux功能；
关闭和禁用系统防火墙（firewalld服务）；
为后续安装方便，请使用系统安装镜像（ISO文件）来配置好本地YUM源。若服务器能访问互联网也可以跳过。

系统基础配置

使用root用户身份登录测试服务器；
配置系统时区和用户默认语言环境：

echo "export LANG=en_US.utf-8" >> /etc/profile
echo "export TZ=Asia/Shanghai" >> /etc/profile

关闭SELINUX功能和系统防火墙服务：

systemctl disable firewalld && systemctl stop firewalld
sed -i "/SELINUX=/s/enforcing/disabled/g" /etc/selinux/config

配置主机名及主机名解析：

hostnamectl set-hostname oratest1
echo "`hostname -I` `hostname -s`" >> /etc/hosts

调整内核启动文件参数，并重启让配置生效：

sed -i "s/timeout=5/timeout=1/g" /boot/grub2/grub.cfg 
sed -i "s/rhgb //g" /boot/grub2/grub.cfg
reboot

将系统ISO文件挂载到测试服务器的光驱，配置好本地YUM仓库：

mkdir /opt/oel74_dvd
mount /dev/cdrom /opt/oel74_dvd
mv /etc/yum.repos.d/public-yum-ol7.repo /etc/yum.repos.d/public-yum-ol7.repo.backup

cat << EOF > /etc/yum.repos.d/oel74-local.repo
[oel74-local]
name=oel74-local
baseurl=file:///opt/oel74_dvd
gpgcheck=0
enabled=1
EOF

yum clean all && yum makecache fast
yum info oracle-database-server-12cR2-preinstall

echo "mount /dev/cdrom /opt/oel74_dvd" >> /etc/rc.local
chmod +x /etc/rc.d/rc.local

安装Oracle Database 12cR2

本例我们采用静默安装方式来安装Oracle Database 12cR2.

使用root用户登录测试服务器；
创建所需用户和组：

groupadd -g 500 oinstall
groupadd -g 501 dba
groupadd -g 502 oper
groupadd -g 503 backupdba
groupadd -g 504 dgdba
groupadd -g 505 kmdba
groupadd -g 506 asmdba
groupadd -g 507 asmoper
groupadd -g 508 asmadmin
groupadd -g 509 racdba

useradd -u 500 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle
echo oracle |passwd --stdin oracle

安装Oracle Database预安装环境配置包：

yum install -y oracle-database-server-12cR2-preinstall

检查安装Oracle Database所需的依赖包（复制如下命令即可）是否已安装完成：

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils compat-libcap1 compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXtst libX11 libXau libxcb libXi sysstat unixODBC unixODBC-devel

安装Oracle Database所需的依赖包（复制如下命令即可）：

yum install -y `rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils compat-libcap1 compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXtst libX11 libXau libxcb libXi sysstat unixODBC unixODBC-devel |grep not |awk '{print $2}'`

优化系统Shell资源限制：

cat <<EOF >> /etc/security/limits.conf
# shell limits for users oracle 12gR1
oracle soft nofile 10240
oracle hard nofile 65536
oracle soft nproc 10240
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
EOF

优化系统内核参数：

cat << EOF >> /etc/sysctl.conf
# Set Oracle 11g Release 2 Database Server 
kernel.sem = 250 32000 100 128
kernel.shmmax = 169393387520
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
/sbin/sysctl -p

创建Oracle Database安装目录结构：

chmod 777 /opt
mkdir -p /opt/app/oracle/product/12.2.0/dbhome_1
mkdir -p /opt/app/oraInventory
chown oracle:oinstall -R /opt/app

配置oracle用户环境变量：

cat <<EOF >> /home/oracle/.bash_profile
umask 022
export TMP=/tmp
export ORACLE_HOSTNAME=oratest1
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=/usr/sbin:\$PATH:\$ORACLE_HOME/bin
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib;
alias cdob='cd \$ORACLE_BASE'
alias cdoh='cd \$ORACLE_HOME'
alias cdtns='cd \$ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
EOF

安装软件包解压工具unzip：

yum install -y unzip wget lrzsz

上传Oracle Database软件包到/opt目录下并解压（这里我还上传了rlwrap包）：

chown oracle.oinstall /opt/linuxx64_12201_database.zip
su - oracle
cd /opt
unzip rlwrap-0.43-2.el7.x86_64.rpm && rm -f rlwrap-0.43-2.el7.x86_64.rpm
12.	安装Oracle Database软件：
cd /opt/database
cp ./response/db_install.rsp ./response/db_install.rsp.backup

cat <<EOF > /opt/database/response/db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/opt/app/oraInventory
ORACLE_HOME=/opt/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/opt/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rac.configurationType=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.rac.serverpoolName=
oracle.install.db.rac.serverpoolCardinality=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryOption=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
COLLECTOR_SUPPORTHUB_URL=
EOF

./runInstaller -silent -responseFile /opt/database/response/db_install.rsp

根据安装提示，使用root用户身份执行如下两个脚本：

/opt/app/oraInventory/orainstRoot.sh
/opt/app/oracle/product/12.2.0/dbhome_1/root.sh

创建并检查监听状态：

netca -silent -responsefile /opt/database/response/netca.rsp
lsnrctl status

创建数据库实例。本例中使用PDB模式，创建一个PDB用于测试：

cd /opt/database
cp ./response/dbca.rsp ./response/dbca.rsp.backup

cat << EOF > ./response/dbca.rsp
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=orcl
sid=orcl
databaseConfigType=SI
RACOneNodeServiceName=
policyManaged=
createServerPool=
serverPoolName=
cardinality=
force=
pqPoolName=
pqCardinality=
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=orcl_pdb
useLocalUndoForPDBs=true
pdbAdminPassword=orclsysdba
nodelist=
templateName=General_Purpose.dbc
sysPassword=orclsysdba
systemPassword=orclsysdba
oracleHomeUserPassword=
emConfiguration=DBEXPRESS
emExpressPort=5500
runCVUChecks=
dbsnmpPassword=orclsysdba
omsHost=
omsPort=
emUser=
emPassword=
dvConfiguration=
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=
datafileJarLocation=
datafileDestination=/opt/app/oracle/oradata
recoveryAreaDestination=/opt/app/oracle/flash_recovery_area
storageType=FS
diskGroupName=
asmsnmpPassword=
recoveryGroupName=
characterSet=
nationalCharacterSet=AL16UTF16
registerWithDirService=
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=
variablesFile=
variables=
initParams=
sampleSchema=
memoryPercentage=40
databaseType=OLTP
automaticMemoryManagement=FALSE
totalMemory=
EOF

mkdir -p /opt/app/oracle/oradata
mkdir -p /opt/app/oracle/flash_recovery_area
dbca -silent -createDatabase -responseFile /opt/database/response/dbca.rsp

至此，Oracle Database安装完毕。
使用root用户补充安装rlwrap软件包。此软件包为可选安装，主要用于让SQLPlus命令行工具支持上下翻页、删除字符的功能：

yum install -y /opt/rlwrap-0.43-2.el7.x86_64.rpm
cat << EOF >> /home/oracle/.bash_profile
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias lsnrctl="rlwrap lsnrctl"
alias asmcmd="rlwrap asmcmd"
alias adrci="rlwrap adrci"
EOF

安装KingbaseES数据库

具体KingbaseES数据库的安装过程此处省略。
本例中，KingbaseES数据库工作端口为54321，且数据库实例为大小写不敏感，使用kingbase用户来运行。

export KBHOME=/opt/Kingbase/ES/V8R3/Server
export KBDATA=/opt/Kingbase/ES/V8R3/data
export LD_LIBRARY_PATH=$KBHOME/lib:$LD_LIBRARY_PATH
export PATH=$KBHOME/bin:$PATH
alias start_kingbase="sys_ctl start -D $KBDATA -l $KBDATA/kingbase.log"
alias restart_kingbase="sys_ctl restart -D $KBDATA -l $KBDATA/kingbase.log -m fast"
alias stop_kingbase="sys_ctl stop -D $KBDATA -l $KBDATA/kingbase.log -m fast"
alias reload_kingbase="sys_ctl reload -D $KBDATA -l $KBDATA/kingbase.log"
alias super_ksql="ksql -Usystem -W123123 -p54321 -hlocalhost test"

initdb -Usystem -W123123 --case-insensitive -E utf-8 -D $KBDATA -k -A md5

安装PostgreSQL数据库

本例中所用的PostgreSQL安装包为前期从PGDG仓库下载的安装包集合，包含了EL7系列Linux系统中安装PostgreSQL 9.6的所需依赖，因此本例中我们将其做成一个本地的YUM仓库，通过yum命令进行安装。如果没有提前下载安装包，可参考官方文档的描述进行安装，可以使用编译安装，也可以使用在线的YUM仓库进行安装，如PGDG仓库。

将下载好的PostgreSQL安装包上传至/opt目录下；
解压，并将其配置为本地YUM仓库：

cd /opt
tar -xzvf postgresql96-9.6.19-1PGDG.rhel7.x86_64.tar.gz 
rm -f postgresql96-9.6.19-1PGDG.rhel7.x86_64.tar.gz
yum install -y createrepo

cd postgresql96-9.6.19-1PGDG.rhel7.x86_64/
createrepo --workers=2 --repo=pgsql96-local .

cat << EOF >> /etc/yum.repos.d/pgsql96-local.repo 
[pgsql96-local]
name=pgsql96-local
baseurl=file:///opt/postgresql96-9.6.19-1PGDG.rhel7.x86_64
gpgcheck=0
enabled=1
EOF

yum clean all && yum makecache fast

安装PostgreSQL 9.6数据库：

groupadd -g 5432 postgres
useradd -u 5432 -g postgres postgres
echo postgres |passwd --stdin postgres
yum install -y postgresql96-server postgresql96-devel postgresql96-test postgresql96-contrib

初始化PostgreSQL数据库集群：

su - postgres
cat << EOF >> ~/.bashrc
export PGHOME=/usr/pgsql-9.6
export PGDATA=/opt/pgdata/9.6
export PATH=\$PGHOME/bin:\$PATH
alias start_pgsql="pg_ctl start -D \$PGDATA -l \$PGDATA/postgresql.log"
alias stop_pgsql="pg_ctl start -D \$PGDATA -l \$PGDATA/postgresql.log -m fast"
alias restart_pgsql="pg_ctl start -D \$PGDATA -l \$PGDATA/postgresql.log -m fast"
alias reload_pgsql="pg_ctl start -D \$PGDATA -l \$PGDATA/postgresql.log"
EOF

source ~/.bashrc
echo postgres > ~/.pgpass && chmod 600 ~/.pgpass
initdb -U postgres --pwfile=/home/postgres/.pgpass -E UTF-8 -A md5 -D $PGDATA

cat << EOF >> /opt/pgdata/9.6/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 100
EOF

echo 'host all all 0.0.0.0/0 md5' >> /opt/pgdata/9.6/pg_hba.conf

start_pgsql
psql -Upostgres -W -d postgres

配置PostgreSQL ODBC数据源

本例中，为了方便我们将Oracle数据库和PostgreSQL数据库部署在同一台服务器上进行测试。理论上Oracle实例可以使用本地的ODBC数据源来访问任意远程的PostgreSQL数据库，因此我们需要在Oracle实例所在的服务器上配置PostgreSQL的ODBC数据源，具体配置命令及过程如下：

su - postgres
start_pgsql
psql -Upostgres -W
create user dltest with password 'dltest' superuser;
\c postgres dltest
create table tb1 as select name,setting,unit from pg_settings;
create table tb2 as select * from tb1;
\q
exit

cat << EOF >> /etc/odbcinst.ini 
[pgsql96]
Description = ODBC Driver for PostgreSQL 9.6
Driver          = /usr/pgsql-9.6/lib/psqlodbc.so
Setup           = /usr/lib64/libodbcpsqlS.so
Driver64        = /usr/pgsql-9.6/lib/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
EOF

cat << EOF >> /etc/odbc.ini 
[pgsql96]
Description = pgsql96
Driver = /usr/pgsql-9.6/lib/psqlodbc.so
ServerName = localhost
Username = dltest
Password = dltest
Port = 5432
Database = postgres
Trace = yes
TraceFile = /tmp/pgsql96_odbctrace.log
EOF

#测试ODBC数据源：
# isql -v pgsql96
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from tb2;
+---------------------+
| count               |
+---------------------+
| 262                 |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

配置Oracle使用PostgreSQL ODBC数据源

配置Oracle数据库以使用本地的PostgreSQL ODBC数据源来访问目标PostgreSQL数据库：

su - oracle
lsnrctl start 
sqlplus / as sysdba
startup
alter pluggable database orcl_pdb open;
alter session set container = orcl_pdb;
show pdbs;
create user dblink identified by dblink; 
grant dba to dblink;
exit;

cd $ORACLE_HOME/hs/admin/
touch initpgsql96.ora
cat << EOF >> $ORACLE_HOME/hs/admin/initpgsql96.ora
HS_FDS_CONNECT_INFO = pgsql96
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.6/lib/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI=/etc/odbc.ini
EOF

cat << EOF >> $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (SID_NAME = pgsql96)
      (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )
EOF

cat << EOF >> $ORACLE_HOME/network/admin/tnsnames.ora
pgsql96 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SID = pgsql96)
    )
   (HS=OK)
  )
EOF

# RELOAD LISTENER:
lsnrctl
status
reload
status    #reload之后会看到其中有pgsql96的内容
exit

tnsping pgsql96
sqlplus / as sysdba
alter session set container = orcl_pdb;
create database link dl_pgsql96 connect to "dltest" identified by "dltest" using 'pgsql96';
select count(*) from "tb1"@dl_pgsql96;

#查询测试结果如下：
SQL> select count(*) from "tb1"@dl_pgsql96;

  COUNT(*)
----------
       262

SQL> select count(*) from "tb2"@dl_pgsql96;

  COUNT(*)
----------
       262

SQL>

配置KingbaseES ODBC数据源

本例中，为了方便我们将Oracle数据库和KingbaseES数据库部署在同一台服务器上进行测试。理论上Oracle实例可以使用本地的ODBC数据源来访问远程的KingbaseES数据库，因此我们需要在Oracle实例所在的服务器上配置KingbaseES的ODBC数据源，具体配置命令及过程如下：

su - kingbase
start_kingbase
super_ksql
create user dltest with password 'dltest' superuser;
create database db_dltest with owner dltest template template2 encoding utf8;
\c db_dltest dltest
create table tb1 as select relname,relowner,relkind from sys_class;
create table tb2 as select * from tb1;
\q
exit

#从金仓官网下载V8R3 ODBC驱动包并上传到服务器/opt目录（官网默认提供rar格式压缩包，但Linux默认不存在unrar命令，因此建议在本地Windows解压并打包为zip格式）：
cd /opt
unzip ODBC.zip && rm -f ODBC.zip
ls -l /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64

cat << EOF >> /etc/odbcinst.ini 
[kingbase8]
Description = ODBC Driver for Kingbase ES V8
Driver          = /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64/kdbodbcw.so
Setup           = /usr/lib64/libodbcpsqlS.so
Driver64        = /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64/kdbodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
EOF

cat << EOF >> /etc/odbc.ini 
[kingbase8]
Description = kingbase8
Driver = /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64/kdbodbcw.so
ServerName = 127.0.0.1
Username = dltest
Password = dltest
Port = 54321
Database = db_dltest
Trace = yes
TraceFile = /tmp/kes_odbctrace1.log
EOF

#测试KingbaseES ODBC数据源：
isql -v pgsq96


#本例，上述测试命令报错如下：
# isql -v kingbase8
[01000][unixODBC][Driver Manager]Can't open lib '/opt/V8R3-ODBC-Driver/Linux_x86/kingbase-odbc-linux-x86_64/kdbodbcw.so' : file not found
[ISQL]ERROR: Could not SQLConnect

#使用ldd命令检查驱动文件所需的依赖项是否满足：
# ldd kdbodbcw.so
ldd: warning: you do not have execution permission for `./kdbodbcw.so'
        linux-vdso.so.1 =>  (0x00007fffd49c2000)
        libkci.so.5 => /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64/././libkci.so.5 (0x00007f11902d7000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f11900b2000)
        libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f118fea0000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f118fadd000)
        libssl.so.6 => not found
        libcrypto.so.6 => not found
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f118f8a5000)
        /lib64/ld-linux-x86-64.so.2 (0x000055d12c859000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f118f69a000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f118f496000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007f118f293000)

#经过检查，我们本地操作系统中缺少部分库文件。因此，执行下面命令查找并安装相应的依赖包（本例实际缺少openssl098e）：
yum provides libc.so.6
yum provides libcrypto.so.6
yum install -y openssl098e

#再次执行isql进行测试：
# isql -v kingbase8
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from tb1;
+---------------------+
| count               |
+---------------------+
| 543                 |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

配置Oracle使用KingbaseES ODBC数据源

配置Oracle数据库以使用本地的KingbaseES ODBC数据源来访问目标KingbaseES数据库：

su - oracle
cd $ORACLE_HOME/hs/admin/
touch initkingbase8.ora
cat << EOF >> $ORACLE_HOME/hs/admin/initkingbase8.ora
HS_FDS_CONNECT_INFO = kingbase8
HS_FDS_TRACE_LEVEL = ON   #改为DEBUG可用于调试，trace日志在$ORACLE_HOME/hs/log目录
HS_FDS_SHAREABLE_NAME = /opt/ODBC/Linux_x86/kingbase-odbc-linux-x86_64/kdbodbcw.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI=/etc/odbc.ini
EOF

#修改$ORACLE_HOME/network/admin/listener.ora，在SID_LIST_LISTENER代码块中添加如下红色字体的内容：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (SID_NAME = pgsql96)
      (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )

    (SID_DESC =
      (SID_NAME = kingbase8)
      (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

#修改$ORACLE_HOME/network/admin/tnsnames.ora文件,追加如下内容：
cat << EOF >> $ORACLE_HOME/network/admin/tnsnames.ora
kingbase8 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SID = kingbase8)
    )
   (HS=OK)
  )
EOF

# RELOAD LISTENER:
lsnrctl
status
reload
status    #reload之后会看到其中有kingbase8的内容
exit

tnsping kingbase8
sqlplus / as sysdba
alter session set container = orcl_pdb;
create database link dl_kingbase8 connect to "dltest" identified by "dltest" using 'kingbase8';
select count(*) from "tb1"@dl_kingbase8;

#查询测试结果如下：
SQL> select count(*) from "tb1"@dl_kingbase8;

  COUNT(*)
----------
       543

SQL> select count(*) from "tb2"@dl_kingbase8;

  COUNT(*)
----------
       543

SQL>

补充信息

从Oraccle数据库中查询我们创建好的dblink信息，如下所示：

SQL> alter session set container = orcl_pdb;
SQL> col owner for a10;
SQL> col db_link for a15;
SQL> col username for a10;
SQL> col host for a10;
SQL> col created for a12;
SQL> col hidden for a8;
SQL> set linesize 120;
SQL> select * from dba_db_links;

OWNER      DB_LINK         USERNAME   HOST       CREATED      HIDDEN
---------- --------------- ---------- ---------- ------------ --------
SYS        SYS_HUB                    SEEDDATA   26-JAN-17    NO
SYS        DL_PGSQL96      dltest     pgsql96    12-MAR-21    NO
SYS        DL_KINGBASE8    dltest     kingbase8  12-MAR-21    NO