在Oracle中通过dblink访问PG数据库

AiDBA宝典

发布于 2022-11-07 19:02:11

3.7K0

发布于 2022-11-07 19:02:11

简介

在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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-10-10，如有侵权请联系 cloudcommunity@tencent.com 删除

图片加速