一、 架构图

本质上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 RPMs
RepoView: 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)