简介
在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该如何配置呢？请看下文。
Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。
环境准备
Oracle 11.2.0.4 CentOS 6.6
PG 13.8 ，Debian GNU/Linux 11
1-- 创建Oracle主机，11.2.0.4环境
2docker run -itd --name lhrora11204 -h lhrora11204 -p 3394:3389 \
3 -p 1524:1521 -p 1124:1158 -p 224:22 \
4 --privileged=true \
5 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
6
7
8-- 创建PG主机，已安装PG 13数据库
9docker rm -f lhrpg13
10docker run --name lhrpg13 -h lhrpg13 -d -p 54326:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:13.8
11
12
13docker exec -it lhrpg13 bash
14
15su - postgres
16create database lhrdb;
17\c lhrdb
18create table test(id int);
19insert into test values(1),(2);
安装postgresql的odbc驱动包
安装ODBC驱动分成两部分：
1.安装unixODBC
1-- 可以直接安装
2yum install -y unixODBC.x86_64
unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器。建议Linux环境安装unixODBC 2.3.7以上版本。可以使用isql --v
查询获取安装unixODBC版本。在CentOS 7中，通过yum安装后版本为2.3.7-；在CentOS 6中，通过yum安装后版本为2.2.14，也可以使用，若使用编译安装，则具体安装方法如下，在root用户下进行操作：
Ø 解压文件：tar -xf unixODBC-2.3.7.tar.gz
Ø 配置安装：cd unixODBC-2.3.7;./configure --prefix=/usr/local/odbc
Ø 编译安装：make && make install
Ø 默认安装到/usr/local/odbc下
Ø 退出当前会话，重新登录查询ODBC版本isql --v
2.安装PostgreSQL的ODBC驱动。
安装完成后，在目录/usr/pgsql-12/下生成lib和share相关目录。pg的odbc驱动放在lib下。
1-- 通过yum安装的postgresql-odbc包驱动太低，会导致后边的报错
2yum install -y unixODBC.x86_64 postgresql-odbc.x86_64
3
4[root@lhrora11204 /]# cat /etc/redhat-release
5CentOS release 6.6 (Final)
6[root@lhrora11204 /]# rpm -qa | grep postgres
7postgresql-libs-8.4.20-8.el6_9.x86_64
8postgresql-odbc-08.04.0200-1.el6.x86_64
9[root@lhrora11204 /]# rpm -qa | grep unixODBC
10unixODBC-devel-2.2.14-14.el6.x86_64
11unixODBC-2.2.14-14.el6.x86_64
12
13[root@lhr ~]# cat /etc/redhat-release
14CentOS Linux release 7.6.1810 (Core)
15[root@lhr ~]# rpm -qa | grep postgres
16postgresql-odbc-09.03.0100-2.el7.x86_64
17postgresql-libs-9.2.24-7.el7_9.x86_64
18[root@lhr ~]# rpm -qa | grep unixODBC
19unixODBC-devel-2.3.7-1.rh.x86_64
20unixODBC-2.3.7-1.rh.x86_64
21
22
23
24-- 正确的安装方式
25yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
26# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
27
28
29yum install -y postgresql12-odbc postgresql12-libs
配置/etc/odbc.ini
1cat > /etc/odbc.ini <<"EOF"
2#[$DSN]定义数据源名称，根据实际情况自定义
3[PG_LINK]
4#数据源说明，根据实际情况自定义
5Description = PostgreSQL connection to lhrdb
6#使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置
7Driver = /usr/pgsql-12/lib/psqlodbcw.so
8Setup = /usr/pgsql-12/lib/psqlodbcw.so
9#数据库名，远程访问的数据库名
10Database = lhrdb
11#数据库所在的主机名或IP
12Servername = 172.17.0.3
13#数据库用户名(可不填，在代码中指定即可)
14UserName = postgres
15#数据库用户密码(可不填，在代码中指定即可)
16Password = lhr
17#数据库端口
18Port = 5432
19SocketBufferSize = 4096
20FetchBufferSize = 500
21ReadOnly = Yes
22RowVersioning = No
23ShowSystemTables = No
24#查询结果的字符编码
25ConnSettings = set client_encoding to UTF8
26EOF
27
28
29odbcinst -j
30export ODBCINI=/etc/odbc.ini
31isql --v
32isql PG_LINK -v
33select 1;
34select * from test;
35
36
37
38ln -sf /etc/odbc.ini /home/oracle/.odbc.ini
结果如下说明配置正确：
1[root@lhrora11204 /]# export ODBCINI=/etc/odbc.ini
2[root@lhrora11204 /]# isql --v
3unixODBC 2.2.14
4[root@lhrora11204 /]# isql PG_LINK -v
5+---------------------------------------+
6| Connected! |
7| |
8| sql-statement |
9| help [tablename] |
10| quit |
11| |
12+---------------------------------------+
13SQL> select 1;
14+------------+
15| ?column? |
16+------------+
17| 1 |
18+------------+
19SQLRowCount returns 1
201 rows fetched
21SQL> select * from test;
22+------------+
23| id |
24+------------+
25| 1 |
26| 2 |
27+------------+
28SQLRowCount returns 2
292 rows fetched
30SQL>
配置透明网关
在
在ORACLEHOME/hs/admin/下面创建initPGLINK.ora文件，这个文件名字中的PGLINK是上面自定义的名字，其中HSFDSCONNECTINFO=PG这个参数指向隐藏文件.odbc.ini中的ODBC定义数据源名称[PGLINK]。在ORACLE_HOME/hs/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字
1cat > $ORACLE_HOME/hs/admin/initPG_LINK.ora <<"EOF"
2HS_FDS_CONNECT_INFO = PG_LINK
3HS_FDS_TRACE_LEVEL = 255
4HS_FDS_SHAREABLE_NAME=/usr/pgsql-12/lib/psqlodbcw.so
5HS_NLS_NCHAR=UCS2
6HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
7set ODBCINI=/home/oracle/.odbc.ini
8EOF
后续通过dblink连接PG的时候连接日志文件路径：$ORACLE_HOME/hs/log/
，可以进行排错。
配置tnsnames.ora文件
在
1cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<"EOF"
2
3PG_LINK =
4 (DESCRIPTION=
5 (ADDRESS=
6 (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)
7 )
8 (CONNECT_DATA=
9 (SID=PG_LINK)
10 )
11 (HS=OK)
12)
13
14EOF
配置监听文件
在ORACLEHOME/network/admin/下面文件tnsnames.ora添加如下内容。其中PGLINK是客户端到PostgreSQL实例的连接配置，注意一点：“SID=PGLINK”中，SID应设置为listener.ora中PostgreSQL的实例名，例如这里就是PGLINK。PGLINK这个名称，将在创建PGLINK时使用。¨G6G¨K24K在ORACLE_HOME/network/admin/下面文件listener.ora添加如下内容。它代表PostgreSQL提供的数据库服务，Oracle将PostgreSQL也看成一个Oracle实例，SID_NAME = PGINSTANCE是给它定义一个实例名。这个实例名决定了配置透明网关配置文件$ORACLE_HOME/hs/admin/init
1cat >> $ORACLE_HOME/network/admin/listener.ora <<"EOF"
2
3SID_LIST_LISTENER=
4 (SID_LIST=
5 (SID_DESC=
6 (SID_NAME=PG_LINK)
7 (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
8 (PROGRAM=dg4odbc)
9 (ENVS=LD_LIBRARY_PATH="/usr/pgsql-12/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/lib")
10 )
11 )
12
13EOF
14
15
16lsnrctl reload
17lsnrctl status
18tnsping PG_LINK
结果：
1[oracle@lhrora11204 log]$ lsnrctl status
2
3LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-OCT-2022 16:32:32
4
5Copyright (c) 1991, 2013, Oracle. All rights reserved.
6
7Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
8STATUS of the LISTENER
9------------------------
10Alias LISTENER
11Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
12Start Date 09-OCT-2022 07:22:10
13Uptime 0 days 9 hr. 10 min. 22 sec
14Trace Level off
15Security ON: Local OS Authentication
16SNMP OFF
17Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
18Listener Log File /u01/app/oracle/diag/tnslsnr/lhrora11204/listener/alert/log.xml
19Listening Endpoints Summary...
20 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora11204)(PORT=1521)))
21Services Summary...
22Service "LHR11G" has 1 instance(s).
23 Instance "LHR11G", status READY, has 1 handler(s) for this service...
24Service "LHR11GXDB" has 1 instance(s).
25 Instance "LHR11G", status READY, has 1 handler(s) for this service...
26Service "PG_LINK" has 1 instance(s).
27 Instance "PG_LINK", status UNKNOWN, has 1 handler(s) for this service...
28The command completed successfully
29[oracle@lhrora11204 log]$ tnsping PG_LINK
30
31TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 09-OCT-2022 16:32:45
32
33Copyright (c) 1997, 2013, Oracle. All rights reserved.
34
35Used parameter files:
36/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora
37
38
39Used TNSNAMES adapter to resolve the alias
40Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA= (SID=PG_LINK)) (HS=OK))
41OK (0 msec)
创建DBLINK和测试
1create database link to_pglink connect to "postgres" identified by "lhr" using 'PG_LINK';
2
3
4-- 访问postgre的数据库表是需要表名字小写并加上双引号
5select * from "test"@to_pglink;
6select * from "public"."test"@to_pglink;
7
8SYS@LHR11G> select * from "test"@to_pglink;
9
10 id
11----------
12 1
13 2
报错ORA-28500和ORA-02063
若查询报错，类似如下：
1SYS@LHR11G> select count(*) from "test"@to_pglink;
2select count(*) from "test"@to_pglink
3 *
4ERROR at line 1:
5ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
6ERROR: current transaction is aborted, commands ignored until end of transaction block;
7No query has been executed with that handle {HY000,NativeErr = 1}
8ORA-02063: preceding 3 lines from TO_PGLINK
9
10
11SYS@LHR11G> select count(*) from "test"@to_pglink;
12select count(*) from "test"@to_pglink
13 *
14ERROR at line 1:
15ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
16ERROR: current transaction is aborted, commands ignored until end of transaction block;
17No query has been executed with that handle {25P02,NativeErr = 1}
18ORA-02063: preceding 3 lines from TO_PGLINK
19
20SYS@LHR11G> select count(*) from "test"@to_pglink;
21select count(*) from "test"@to_pglink
22 *
23ERROR at line 1:
24ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
25ORA-02063: preceding line from TO_PGLINK
解决：
1、在Oracle端安装最新的PostgreSQL的驱动文件
1yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
3
4yum install -y postgresql13-odbc postgresql13-libs
2、odbc.ini文件中的Driver和HS_FDS_SHAREABLE_NAME内容需要配置最新的lib文件，不能使用“/usr/lib64/libodbc.so”文件。
3、注意监听文件中的LD_LIBRARY_PATH的变量需要配置正确：
1(ENVS=LD_LIBRARY_PATH="/usr/pgsql-12/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/lib")
4、一句话，不能使用“/usr/lib64/libodbc.so”文件。
总结
1、主要步骤和Oracle连接MySQL过程一样
2、postgresql-odbc包的驱动需要使用官方的包，不能使用默认的包，不能使用yum install -y postgresql-odbc.x86_64
方式安装，不能使用“/usr/lib64/libodbc.so”文件。
3、通过dblink连接PG的时候连接日志文件路径：$ORACLE_HOME/hs/log/
，可以进行排错。
4、每次修改监听文件后，必须重启监听才能起作用
5、ODBC的日志也可以这样配置：
1cat > /etc/odbc.ini <<"EOF"
2[PG_LINK]
3Description = PostgreSQL connection to lhrdb
4Driver = /usr/pgsql-12/lib/psqlodbcw.so
5Setup = /usr/pgsql-12/lib/psqlodbcw.so
6Database = lhrdb
7Servername = 172.17.0.3
8UserName = postgres
9Password = lhr
10Port = 5432
11SocketBufferSize = 4096
12FetchBufferSize = 500
13ReadOnly = Yes
14ConnSettings = set client_encoding to UTF8
15Trace = yes
16TraceFile = /tmp/odbctrace.txt
17EOF
参考
https://www.modb.pro/db/429796
https://blog.gahanzwart.nl/oracle/how-to-connect-oracle-19c-database-on-red-hat-linux-to-a-postgresql-database-with-an-oracle-database-link-over-odbc/
https://blog.csdn.net/howard_shooter/article/details/123612684
https://copyfuture.com/blogs-details/20210522134022932y
http://www.itpub.net/thread-1892551-1-1.html
