在Oracle中通过dblink访问PG数据库
在Oracle中通过dblink访问PG数据库
简介
在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
-- 创建Oracle主机，11.2.0.4环境
docker run -itd --name lhrora11204 -h lhrora11204 -p 3394:3389 \
-p 1524:1521 -p 1124:1158 -p 224:22 \
--privileged=true \
lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 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驱动包
安装ODBC驱动分成两部分：
1.安装unixODBC
-- 可以直接安装
yum 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下。
-- 通过yum安装的postgresql-odbc包驱动太低，会导致后边的报错
yum install -y unixODBC.x86_64 postgresql-odbc.x86_64
[root@lhrora11204 /]# cat /etc/redhat-release
CentOS release 6.6 (Final)
[root@lhrora11204 /]# rpm -qa | grep postgres
postgresql-libs-8.4.20-8.el6_9.x86_64
postgresql-odbc-08.04.0200-1.el6.x86_64
[root@lhrora11204 /]# rpm -qa | grep unixODBC
unixODBC-devel-2.2.14-14.el6.x86_64
unixODBC-2.2.14-14.el6.x86_64
[root@lhr ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@lhr ~]# rpm -qa | grep postgres
postgresql-odbc-09.03.0100-2.el7.x86_64
postgresql-libs-9.2.24-7.el7_9.x86_64
[root@lhr ~]# rpm -qa | grep unixODBC
unixODBC-devel-2.3.7-1.rh.x86_64
unixODBC-2.3.7-1.rh.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 postgresql12-odbc postgresql12-libs
配置/etc/odbc.ini
cat > /etc/odbc.ini <<"EOF"
#[$DSN]定义数据源名称，根据实际情况自定义
[PG_LINK]
#数据源说明，根据实际情况自定义
Description = PostgreSQL connection to lhrdb
#使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置
Driver = /usr/pgsql-12/lib/psqlodbcw.so
Setup = /usr/pgsql-12/lib/psqlodbcw.so
#数据库名，远程访问的数据库名
Database = lhrdb
#数据库所在的主机名或IP
Servername = 172.17.0.3
#数据库用户名(可不填，在代码中指定即可)
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
结果如下说明配置正确：
[root@lhrora11204 /]# export ODBCINI=/etc/odbc.ini
[root@lhrora11204 /]# isql --v
unixODBC 2.2.14
[root@lhrora11204 /]# isql PG_LINK -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select 1;
+------------+
| ?column? |
+------------+
| 1 |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> select * from test;
+------------+
| id |
+------------+
| 1 |
| 2 |
+------------+
SQLRowCount returns 2
2 rows fetched
SQL>
配置透明网关
在
在ORACLEHOME/hs/admin/下面创建initPGLINK.ora文件，这个文件名字中的PGLINK是上面自定义的名字，其中HSFDSCONNECTINFO=PG这个参数指向隐藏文件.odbc.ini中的ODBC定义数据源名称[PGLINK]。在ORACLE_HOME/hs/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字
cat > $ORACLE_HOME/hs/admin/initPG_LINK.ora <<"EOF"
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-12/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
set ODBCINI=/home/oracle/.odbc.ini
EOF
后续通过dblink连接PG的时候连接日志文件路径：$ORACLE_HOME/hs/log/，可以进行排错。
配置tnsnames.ora文件
在
cat >> $ORACLE_HOME/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
配置监听文件
在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.ora的文件名，例如initPG_LINK.ora。
cat >> $ORACLE_HOME/network/admin/listener.ora <<"EOF"
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PG_LINK)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH="/usr/pgsql-12/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/lib")
)
)
EOF
lsnrctl reload
lsnrctl status
tnsping PG_LINK
结果：
[oracle@lhrora11204 log]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-OCT-2022 16:32:32
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 09-OCT-2022 07:22:10
Uptime 0 days 9 hr. 10 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/lhrora11204/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora11204)(PORT=1521)))
Services Summary...
Service "LHR11G" has 1 instance(s).
Instance "LHR11G", status READY, has 1 handler(s) for this service...
Service "LHR11GXDB" has 1 instance(s).
Instance "LHR11G", 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...
The command completed successfully
[oracle@lhrora11204 log]$ tnsping PG_LINK
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 09-OCT-2022 16:32:45
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.4/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)
创建DBLINK和测试
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@LHR11G> select * from "test"@to_pglink;
id
----------
1
2
报错ORA-28500和ORA-02063
若查询报错，类似如下：
SYS@LHR11G> select count(*) from "test"@to_pglink;
select count(*) from "test"@to_pglink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: current transaction is aborted, commands ignored until end of transaction block;
No query has been executed with that handle {HY000,NativeErr = 1}
ORA-02063: preceding 3 lines from TO_PGLINK
SYS@LHR11G> select count(*) from "test"@to_pglink;
select count(*) from "test"@to_pglink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: current transaction is aborted, commands ignored until end of transaction block;
No query has been executed with that handle {25P02,NativeErr = 1}
ORA-02063: preceding 3 lines from TO_PGLINK
SYS@LHR11G> select count(*) from "test"@to_pglink;
select count(*) from "test"@to_pglink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from TO_PGLINK
解决：
1、在Oracle端安装最新的PostgreSQL的驱动文件
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 postgresql13-odbc postgresql13-libs
2、odbc.ini文件中的Driver和HS_FDS_SHAREABLE_NAME内容需要配置最新的lib文件，不能使用“/usr/lib64/libodbc.so”文件。
3、注意监听文件中的LD_LIBRARY_PATH的变量需要配置正确：
(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的日志也可以这样配置：
cat > /etc/odbc.ini <<"EOF"
[PG_LINK]
Description = PostgreSQL connection to lhrdb
Driver = /usr/pgsql-12/lib/psqlodbcw.so
Setup = /usr/pgsql-12/lib/psqlodbcw.so
Database = lhrdb
Servername = 172.17.0.3
UserName = postgres
Password = lhr
Port = 5432
SocketBufferSize = 4096
FetchBufferSize = 500
ReadOnly = Yes
ConnSettings = set client_encoding to UTF8
Trace = yes
TraceFile = /tmp/odbctrace.txt
EOF
参考
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