oracle to polardb-o (postgresql) dblink创建步骤

一、 架构图

本质上polardb-o的底层是postgresql数据库，因此该需求可以转换为创建 oracle to postgresql的dblink。

1. 原理图

2. 实际架构

下面为实际创建步骤

二、 安装依赖包

yum install -y unixODBC
yum install -y unixODBC-devel
yum install -y libtool
yum install -y libicu
yum install -y libaio*

三、 pg客户端与odbc安装配置

1. 安装 pgsql 客户端

下载地址

RepoView: PostgreSQL PGDG 11 Updates RPMsRepoView: PostgreSQL PGDG 11 Updates RPMs

[root@gateway ~]# ll -h p*
-rw-r--r-- 1 root root 1.7M Apr 20 18:07 postgresql11-11.11-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root 364K Apr 20 18:11 postgresql11-libs-11.11-1PGDG.rhel7.x86_64.rpm

安装rpm包

[root@gateway ~]# rpm -ivh postgresql11-*
warning: postgresql11-11.11-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql11-libs-11.11-1PGDG.rhe################################# [ 50%]
2:postgresql11-11.11-1PGDG.rhel7 ################################# [100%]

[root@gateway ~]# psql -V
psql (PostgreSQL) 11.11

2. 下载安装 psqlodbc

下载地址

https://opensuse.pkgs.org/15.3/opensuse-oss-x86_64/psqlODBC-12.01.0000-3.6.1.x86_64.rpm.html

安装rpm包

[root@gateway ~]# rpm -ivh psqlODBC-12.01.0000-3.6.1.x86_64.rpm
warning: psqlODBC-12.01.0000-3.6.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 39db7c82: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:psqlODBC-12.01.0000-3.6.1 ################################# [100%]

3. 配置odbc

[root@gateway ~]# cd /etc
[root@gateway etc]# ll *odbc*
-rw-r--r-- 1 root root 0 Apr 20 14:25 odbc.ini
-rw-r--r-- 1 root root 505 Apr 20 18:31 odbcinst.ini

odbcinst.ini文件配置

[root@gateway etc]# vi /etc/odbcinst.ini
 [ODBC]
 Trace = yes
 TraceFile = /var/log/odbcinst.log
 # Driver from the postgresql-odbc package
 # Setup from the unixODBC package[PostgreSQL]
 Description = ODBC for PostgreSQL
 Driver = /usr/lib64/psqlodbcw.so Setup = /usr/lib64/libodbc.so FileUsage = 1

odbc.ini文件配置

[root@gateway etc]# vi /etc/odbc.ini
 [uat_srm]
 Description = Test to polardb-o
 Driver = PostgreSQL
 Database = uat_srm
 Servername = polardb-o连接串
 UserName = 用户名
 Password = xxxxx  注意这里Password不能有特殊字符
 Port = 1521
 Protocol = 6.4
 ReadOnly = No
 RowVersioning = No
 ShowSystemTables = No
 ConnSettings =

测试连接

[root@gateway ~]# isql -v uat_srm
 +---------------------------------------+
 | Connected! |
 | |
 | sql-statement |
 | help [tablename] |
 | quit |
 | |
 +---------------------------------------+

odbc.ini 去掉账号密码

这步测通之后说明odbc已经ok，此时可以把odbc.ini的UserName和Password去掉，避免服务器中有明文密码，也避免db账号密码不能设置特殊字符

[root@gateway etc]# vi /etc/odbc.ini
 [uat_srm]
 Description = Test to polardb-o
 Driver = PostgreSQL
 Database = uat_srm
 Servername = polardb-o连接串
 Port = 1521
 Protocol = 6.4
 ReadOnly = No
 RowVersioning = No
 ShowSystemTables = No
 ConnSettings =

四、 透明网关安装配置

1. 安装准备

安装要求

Installing Oracle Database Gateway for ODBC

下载地址，搜gateway

https://edelivery.oracle.com/osdc/faces/SoftwareDelivery

安装包的类型没什么关系，都是包含全部组件的，静默安装时再选具体组件即可。

创建组和用户

groupadd -g 54321 oinstall
groupadd -g 54322 dba
useradd -g oinstall -G dba oracle

创建相关目录

mkdir -p /data/prd/oraInventory
mkdir -p /data/prd/oracle/database/19.3.0.0/prod_cdb
chown oracle.oinstall -R /data

2. 静默安装

解压安装包

unzip V982066-01.zip

编辑响应文件，参考 How to Make a Silent Install of Oracle Database Gateway 12c or higher (Doc ID 2639375.1)，Below example is for an Oracle Database Gateway for ODBC 18c (DG4ODBC) install部分

su - oracle
mkdir -p /data/gateways/etc
cp /data/gateways/response/*.rsp /data/gateways/etc
cd /data/gateways/etc
vi tg.rsp

修改以下项

oracle.install.responseFileVersion=/oracle/install/rspfmt_tginstall_response_schema_v19.0.0
 UNIX_GROUP_NAME=dba
 INVENTORY_LOCATION=/data/prd/oraInventory
 ORACLE_HOME=/data/prd/oracle/database/19.3.0.0/prod_cdb
 ORACLE_BASE=/data/prd/oracle/database
oracle.install.tg.customComponents=oracle.rdbms.hsodbc:19.0.0.0.0

配置环境变量

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=/data/prd/oracle/database/19.3.0.0/prod_cdb

PATH=$PATH:$HOME/bin:/usr/local/nginx/sbin:$ORACLE_HOME/bin

export PATH

export LD_LIBRARY_PATH=/usr/lib64:/usr/local/lib:$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin

安装

[oracle@gateway gateways]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /home/oracle/gateways/etc/tg.rsp
 Starting Oracle Universal Installer...Checking Temp space: must be greater than 415 MB. Actual 40485 MB Passed
 Checking swap space: must be greater than 150 MB. Actual 1023 MB Passed
 Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-04-22_02-21-58PM. Please wait ...[WARNING] [INS-13001] Oracle Transparent Gateways is not supported on this operating system. Installer will not perform prerequisite checks on the system.
 CAUSE: This operating system may not have been in the certified list at the time of the release of this software.
 ACTION: Refer to My Oracle Support portal for the latest certification information for this operating system. Proceed with the installation if the operating system has been certified after the release of this software.
 The response file for this session can be found at:
 /data/prd/oracle/database/19.3.0.0/prod_cdb/install/response/tg_2022-04-22_02-21-58PM.rsp Prepare in progress.
 .................................................. 8% Done.Prepare successful.
Copy files in progress.
 .................................................. 16% Done.
 .................................................. 21% Done.
 .................................................. 26% Done.
 .................................................. 31% Done.
 .................................................. 36% Done.
 .................................................. 41% Done.
 .................................................. 46% Done.
 .................................................. 51% Done.
 .................................................. 56% Done.
 .................................................. 61% Done.
 .................................................. 66% Done.
 .................................................. 71% Done.Copy files successful.
Link binaries in progress.
Link binaries successful.
Setup files in progress.
 ........................................
 Setup files successful.Setup Inventory in progress.
Setup Inventory successful.
 .................................................. 76% Done.Finish Setup successful.
 The installation of Oracle Database Gateways was successful.
 Please check '/data/prd/oraInventory/logs/silentInstall2022-04-22_02-21-58PM.log' for more details.Oracle Gateway Configuration in progress.
Oracle Net Configuration Assistant in progress.
 .................................................. 95% Done.Oracle Net Configuration Assistant failed.
 [WARNING] [INS-32091] Software installation was successful. But some configuration assistants failed, were cancelled or skipped.
 ACTION: Refer to the logs or contact Oracle Support Services.
 The log of this install session can be found at:
 /data/prd/oraInventory/logs/installActions2022-04-22_02-21-58PM.log

五、 透明网关配置

• 监听与TNS文件路径：$ORACLE_HOME/network/admin
• init文件路径：$ORACLE_HOME/hs/admin

1. 监听文件配置

cd $ORACLE_HOME/network/admin
 vi listener.ora# HOST为透明网关服务器，SID_NAME为odbc.ini文件[]中配置的名字
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 透明网关ip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )ADR_BASE_LISTENER = /data/prd/oracle/database/19.3.0.0/prod_cdb
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC=
 (SID_NAME=uat_srm)
 (ORACLE_HOME=/data/prd/oracle/database/19.3.0.0/prod_cdb)
 (PROGRAM=dg4odbc)
 )
 )

2. TNS文件配置

vi tnsnames.ora
# HOST为透明网关服务器，SID_NAME为odbc.ini文件[]中配置的名字
uat_srm =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 透明网关ip)(PORT = 1521))
 (CONNECT_DATA =
 (SID = uat_srm)
 )
(HS = OK)
 )

3. init文件配置

新建对应init文件，文件名为 init[SID_NAME].ora，本例即为 inituat_srm..ora

cd $ORACLE_HOME/hs/admin
vi inituat_srm.ora
HS_FDS_CONNECT_INFO = uat_srm
 HS_FDS_TRACE_LEVEL = 255
 HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbcw.so HS_NLS_NCHAR=UCS2
 HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
 set ODBCINI=/etc/odbc.ini
 set ODBCINSTINI=/etc/odbcinst.ini

六、 oracle端配置

1. TNS文件配置

cd $ORACLE_HOME/network/admin
vi tnsnames.ora
# HOST为透明网关服务器，SID_NAME为odbc.ini文件[]中配置的名字
uat_srm =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 透明网关ip)(PORT = 1521))
 (CONNECT_DATA =
 (SID = uat_srm)
 )
(HS = OK)
 )

2. 创建dblink

create public database link uat_srm connect to "srm_user" identified by "XXXX" using 'uat_srm';

create public database link link名 connect to "用户名" identified by "密码" using 'tns连接名';

3. 测试dblink

注意需要给表名加上双引号

SQL> select * from "dual"@uat_srm;

dum
---
X

如果不加，你发现会报错，但其实源库是有这个表的。原因是pg大小写敏感（默认是小写），oracle大小写不敏感（默认是大写）。

SQL> select * from dual@uat_srm;
select * from dual@uat_srm
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation "DUAL" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from UAT_SRM

参考

Installing Oracle Database Gateway for ODBC

How to Make a Silent Install of Oracle Database Gateway 12c or higher (Doc ID 2639375.1)

ORA-28500: Connection From ORACLE To A Non-Oracle System Returned This Message: C(Doc ID 2325424.1)