简介
在PG中访问PG可以通过dblink，在PG中访问Oracle可以通过oracle_fdw，访问MySQL可以通过mysql_fdw，具体过程可以参考：https://www.xmmup.com/pgzhongdefile_fdwpostgres_fdwhedblink.html
在Oracle访问SQL server需要配置Oracle Database Gateways透明网关，Oracle中访问SQL Server和MySQL的配置可以参考：https://www.xmmup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html
在Oracle中连接PG的详细内容请参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
本文只简单写出Oracle 21c到PG 13.8的配置过程。
环境准备
代码语言：javascript复制Oracle 21.3 CentOS 7.6.1810 172.17.0.2 PG 13.8 ，Debian GNU/Linux 11 172.17.0.4
-- 创建Oracle主机，11.2.0.4环境
docker run -d --name lhroracle21c -h lhroracle21c
-p 5510:5500 -p 55100:5501 -p 1530:1521 -p 3400:3389
-v /sys/fs/cgroup:/sys/fs/cgroup
--privileged=true lhrbest/oracle21c_ee_db_21.3.0.0
/usr/sbin/init
-- 创建PG主机，已安装PG 13数据库
docker rm -f lhrpg13
docker run --name lhrpg13 -h lhrpg13 -d -p 54326:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:13.8
docker exec -it lhrpg13 bash
su - postgres
create database lhrdb;
c lhrdb
create table test(id int);
insert into test values(1),(2);
安装postgresql的odbc驱动包
代码语言：javascript复制-- 可以直接安装
yum install -y unixODBC.x86_64
-- 正确的安装方式
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-odbc postgresql14-libs
配置/etc/odbc.ini
代码语言：javascript复制cat > /etc/odbc.ini <<"EOF"
#[$DSN]定义数据源名称，根据实际情况自定义
[PG_LINK]
#数据源说明，根据实际情况自定义
Description = PostgreSQL connection to lhrdb
#使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置
Driver = /usr/pgsql-14/lib/psqlodbcw.so
Setup = /usr/pgsql-14/lib/psqlodbcw.so
#数据库名，远程访问的数据库名
Database = lhrdb
#数据库所在的主机名或IP
Servername = 172.17.0.4
#数据库用户名(可不填，在代码中指定即可)
UserName = postgres
#数据库用户密码(可不填，在代码中指定即可)
Password = lhr
#数据库端口
Port = 5432
SocketBufferSize = 4096
FetchBufferSize = 500
ReadOnly = Yes
RowVersioning = No
ShowSystemTables = No
#查询结果的字符编码
ConnSettings = set client_encoding to UTF8
EOF
odbcinst -j
export ODBCINI=/etc/odbc.ini
isql --v
isql PG_LINK -v
select 1;
select * from test;
ln -sf /etc/odbc.ini /home/oracle/.odbc.ini
结果如下说明配置正确：
代码语言：javascript复制[oracle@lhroracle21c ~]$ export ODBCINI=/etc/odbc.ini
[oracle@lhroracle21c ~]$ isql --v
unixODBC 2.3.1
[oracle@lhroracle21c ~]$ isql PG_LINK -v
---------------------------------------
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
---------------------------------------
SQL> select * from test;
------------
| id |
------------
| 1 |
| 2 |
| 3 |
------------
SQLRowCount returns 3
3 rows fetched
配置透明网关
若Oracle是21c之前的版本，请参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
由于Oracle是21c，默认开启了只读主目录特性：
代码语言：javascript复制[oracle@lhroracle21c ~]$ orabasehome
/u01/app/oracle/homes/OraDB21Home1
[oracle@lhroracle21c ~]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle/product/21c/dbhome_1:/u01/app/oracle:OraDB21Home1:Y:
[oracle@lhroracle21c ~]$ cd /u01/app/oracle/homes/OraDB21Home1
[oracle@lhroracle21c OraDB21Home1]$ ll
total 56
drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 assistants
drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 cfgtoollogs
drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 dbs
drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 drdaas
drwxr-x--- 1 oracle oinstall 4096 Oct 9 15:51 hs
drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 install
drwxr-xr-t 1 oracle oinstall 4096 Aug 16 2021 log
drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 mgw
drwxr-x--- 5 oracle oinstall 4096 Aug 16 2021 network
drwxr-x--- 1 oracle oinstall 4096 Aug 16 2021 rdbms
drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 sqlpatch
[oracle@lhroracle21c OraDB21Home1]$ cd hs/admin
[oracle@lhroracle21c admin]$ pwd
/u01/app/oracle/homes/OraDB21Home1/hs/admin
[oracle@lhroracle21c admin]$
所以，网关的配置文件应该放在目录/u01/app/oracle/homes/OraDB21Home1/hs/admin，而不是/u01/app/oracle/product/21c/dbhome_1/hs/admin，且不能配置HS_NLS_NCHAR和HS_LANGUAGE变量，否则会报错，这里应特别注意。
cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initPG_LINK.ora <<"EOF"
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-14/lib/psqlodbcw.so
#HS_NLS_NCHAR=UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
set ODBCINI=/home/oracle/.odbc.ini
EOF
后续通过dblink连接PG的时候连接日志文件路径：/u01/app/oracle/homes/OraDB21Home1/hs/log/，可以进行排错。
配置tnsnames.ora文件
代码语言：javascript复制cat >> /u01/app/oracle/product/21c/dbhome_1/network/admin/tnsnames.ora <<"EOF"
PG_LINK =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)
)
(CONNECT_DATA=
(SID=PG_LINK)
)
(HS=OK)
)
EOF
配置监听文件
代码语言：javascript复制cat >> /u01/app/oracle/product/21c/dbhome_1/network/admin/listener.ora <<"EOF"
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PG_LINK)
(ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH="/usr/pgsql-14/lib:/u01/app/oracle/product/21c/dbhome_1/lib")
)
)
EOF
lsnrctl reload
lsnrctl status
tnsping PG_LINK
结果：
代码语言：javascript复制[oracle@lhroracle21c admin]$ lsnrctl status
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 11-OCT-2022 15:38:31
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date 11-OCT-2022 14:27:36
Uptime 0 days 1 hr. 10 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/21c/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/lhroracle21c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhroracle21c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=lhroracle21c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/LHRCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "LHRCDB" has 1 instance(s).
Instance "LHRCDB", status READY, has 1 handler(s) for this service...
Service "LHRCDBXDB" has 1 instance(s).
Instance "LHRCDB", status READY, has 1 handler(s) for this service...
Service "PG_LINK" has 1 instance(s).
Instance "PG_LINK", status UNKNOWN, has 1 handler(s) for this service...
Service "c9a73ae76f543a73e0530e0011acc4b4" has 1 instance(s).
Instance "LHRCDB", status READY, has 1 handler(s) for this service...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "LHRCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@lhroracle21c admin]$ tnsping PG_LINK
TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 11-OCT-2022 15:38:33
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA= (SID=PG_LINK)) (HS=OK))
OK (0 msec)
[oracle@lhroracle21c admin]$
创建DBLINK和测试
代码语言：javascript复制create database link to_pglink connect to "postgres" identified by "lhr" using 'PG_LINK';
-- 访问postgre的数据库表是需要表名字小写并加上双引号
select * from "test"@to_pglink;
select * from "public"."test"@to_pglink;
SYS@LHRCDB> select * from "test"@to_pglink;
id
----------
1
2
SYS@LHRCDB> select * from "public"."test"@to_pglink;
id
----------
1
2
总结
1、若Oracle开启了只读主目录特性，那么需要注意网关文件的配置路径，且不能配置HS_NLS_NCHAR和HS_LANGUAGE变量，正确配置如下：
代码语言：javascript复制cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initPG_LINK.ora <<"EOF"
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-14/lib/psqlodbcw.so
#HS_NLS_NCHAR=UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
set ODBCINI=/home/oracle/.odbc.ini
EOF
