oracle通过dblink访问PostgreSQL数据库

duqk1982

已于 2023-10-21 15:39:17 修改

阅读量1.3k

点赞数 1

分类专栏： PostgreSQL 文章标签：数据库 oracle postgresql

于 2023-10-21 15:29:22 首次发布

本文链接：https://blog.csdn.net/dqk1982/article/details/133961627

版权

PostgreSQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

Oracle dblink的底层是通过ODBC连接PostgreSQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。

本实验要求先安装好Oracle和PostgreSQL数据库，以下操作都是在Oracle服务器上进行：

1.环境配置

操作系统：CentOS Linux release 7.9.2009 (Core)
oracle数据库：Version 19.3.0.0.0
PostgreSQL数据库：PostgreSQL 14.7

2. 安装unixODBC 和 PostgreSQL ODBC驱动

root安装unixODBC：

yum install unixODBC unixODBC-devel.x86_64

unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器，yum安装的版本为2.3.1，通过isql -v查看版本。

[root@du104 ~]# isql --v
unixODBC 2.3.1
[root@du104 ~]#

root安装PostgreSQL ODBC驱动

由于PostgreSQL ODBC驱动需要指定libpq，所以先在Oracle服务器上安装postgresql14.7 ：

yum -y install systemtap-sdt-devel readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel  tcl tcl-devel gcc-c++ perl-ExtUtils-Embed lz4 lz4-devel

tar -xvf postgresql-14.7.tar.gz 
cd postgresql-14.7
./configure --prefix=/opt/pgsql --with-pgport=1921
gmake world
gmake install-world

安装完pg后设置环境变量：

vi /etc/bashrc
export PATH=$PATH:/opt/pgsql/bin
source /etc/bashrc


lib库：
vi /etc/ld.so.conf.d/postgresql-x86_64.conf
/opt/pgsql/lib

-- 生效
ldconfig

安装PostgreSQL ODBC驱动

tar xf psqlodbc-13.02.0000.tar.gz
cd psqlodbc-13.02.0000
./configure --with-libpq=/opt/pgsql
make
make install

– 默认安装到/user/local/lib

此处安装需要指定–with-libpq=/opt/pgsql，否则在make阶段出现如下报错：

make[1]: Entering directory `/root/psqlodbc-13.02.0000'
/bin/sh ./libtool  --tag=CC   --mode=compile gcc -DHAVE_CONFIG_H -I.   -I/usr/local/include -I/usr/include -I/usr/include/pgsql/internal -DUNICODE_SUPPORT  -g -O2 -Wall -MT psqlodbcw_la-connection.lo -MD -MP -MF .deps/psqlodbcw_la-connection.Tpo -c -o psqlodbcw_la-connection.lo `test -f 'connection.c' || echo './'`connection.c
libtool: compile:  gcc -DHAVE_CONFIG_H -I. -I/usr/local/include -I/usr/include -I/usr/include/pgsql/internal -DUNICODE_SUPPORT -g -O2 -Wall -MT psqlodbcw_la-connection.lo -MD -MP -MF .deps/psqlodbcw_la-connection.Tpo -c connection.c  -fPIC -DPIC -o .libs/psqlodbcw_la-connection.o
connection.c: In function 'handle_pgres_error':
connection.c:900:45: error: 'PG_DIAG_SCHEMA_NAME' undeclared (first use in this function)
   errschemaname = PQresultErrorField(pgres, PG_DIAG_SCHEMA_NAME);
                                             ^
connection.c:900:45: note: each undeclared identifier is reported only once for each function it appears in
connection.c:901:44: error: 'PG_DIAG_TABLE_NAME' undeclared (first use in this function)
   errtablename = PQresultErrorField(pgres, PG_DIAG_TABLE_NAME);
                                            ^
connection.c:902:45: error: 'PG_DIAG_COLUMN_NAME' undeclared (first use in this function)
   errcolumnname = PQresultErrorField(pgres, PG_DIAG_COLUMN_NAME);
                                             ^
connection.c:903:47: error: 'PG_DIAG_DATATYPE_NAME' undeclared (first use in this function)
   errdatatypename = PQresultErrorField(pgres, PG_DIAG_DATATYPE_NAME);
                                               ^
make[1]: *** [psqlodbcw_la-connection.lo] Fehler 1
make[1]: Leaving directory `/root/psqlodbc-13.02.0000'

3. 配置odbcinst.ini和odbc.ini

root用户执行：
vim /etc/odbcinst.ini（不区分大小写）

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
#Driver         = /usr/lib/psqlodbcw.so
#Setup          = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/local/lib/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

本次只是用到PostgreSQL相关的配置
/usr/local/lib/psqlodbcw.so 是PostgreSQL ODBC驱动的库
/usr/lib64/libodbcpsqlS.so 是unixODBC的库

vi /etc/odbc.ini

[PG]
Description = Test to pg14
Driver = PostgreSQL
Database = postgres
Servername = 192.168.10.11
UserName = postgres
Password = postgres
Port = 1921
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

配置ODBC源，指向PostgreSQL14.7 。

这里Driver都应与odbcinst.ini中的[PostgreSQL]对应，可以取任何名字，但两者需要相同。其它参数视具体PostgreSQL服务器而定。[PG]和[ATLASDB]是连接具体数据源的配置名称，其它应用程序（如Oracle）就是引用这个名称访问相应的数据库。

文件odbc.ini的具体内容说明：

#[$DSN]定义数据源名称，根据实际情况自定义
[PG]

#数据源说明，根据实际情况自定义
Description = Test to pg14

#使用的驱动，/etc/odbcinst.ini中的配置
Driver = PostgreSQL

#数据库名，远程访问的数据库名
Database = postgres

#数据库所在的主机名或IP
Servername = 192.168.10.11

#数据库用户名(可不填，在代码中指定即可)
UserName = postgres

#数据库用户密码(可不填，在代码中指定即可)
Password = postgres

#数据库端口
Port = 1921

#查询结果的字符编码
ConnSettings = set client_encoding to UTF8

4.测试ODBC连接能否工作

测试通过odbc访问PostgreSQL14.7：

[root@du104 ~]# isql -v pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from t;
+------------+
| id         |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
[root@du104 ~]#

5. 配置Oracle网络和透明网关

涉及3个配置文件的修改（oracle用户去操作）：

$ORACLEHOME/network/admin/listener.ora
$ORACLEHOME/hs/admin/init<SID_NAME>.ora
$ORACLEHOME/network/admin/tnsnames.ora

在listener.ora中，增加静态监听配置，代表提供的数据库服务

[oracle@du104 ~]$ cat /opt/oracle/db/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = du104)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
(SID_LIST=
   (SID_DESC=
      (SID_NAME = PG14)
      (ORACLE_HOME = /opt/oracle/db)
      (PROGRAM = dg4odbc)
   )
)
[oracle@du104 ~]$

解释：Oracle将PostgreSQL也看成一个Oracle实例，SID_NAME 是给它定义一个实例名，这个名称任意，但此名称决定第二个配置文件$ORACLEHOME/hs/admin/init<SID_NAME>.ora的文件名。

配置 $ORACLEHOME/hs/admin/init<SID_NAME>.ora

[oracle@du104 ~]$ cat /opt/oracle/db/hs/admin/initPG14.ora
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/local/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
set ODBCINI=/etc/odbc.ini
[oracle@du104 ~]$

在$ORACLE_HOME/hs/admin/下面创建initPG14.ora文件，这个文件名字中的PG14是listener.ora中自定义的名字；
HS_FDS_CONNECT_INFO 这个参数指向odbc.ini文件中的ODBC连接名称，参考前面。
HS_FDS_SHAREABLE_NAME =/usr/local/lib/psqlodbcw.so 这个参数设置PostgreSQL ODBC驱动的路径，与odbcinst.ini中Driver64相同。
如果PostgreSQL的字符编码是UTF8，那么下面两个参数应该这样设置：
注意：字符编码必须一致，不然会报错 ORA-28500: Connection From ORACLE To A Non-Oracle System Returned This Message: C (Doc ID 2325424.1)

HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

修改 $ORACLE_HOME/network/admin/tnsnames.ora文件

[oracle@du104 ~]$ cat /opt/oracle/db/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/oracle/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = du104)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = du104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

MYPG =   
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SID=PG14)  
    )  
    (HS=OK)  
  )
[oracle@du104 ~]$

MYPG是客户端到PostgreSQL实例的连接配置，将在创建dblink时使用。只要注意：

(SID=PG14) 应设置为listener.ora中SID的实例名；
(HS=OK)：表示通过透明网关访问异构数据

检查数据库监听和tns解析

注意：在数据库监听状态中，这个实例PG14对应的状态都为“UNKNOWN”

[oracle@du104 ~]$ lsnrctl start

[oracle@du104 ~]$ lsnrctl status 

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-OCT-2023 12:04:17

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=du104)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-OCT-2023 12:04:02
Uptime                    0 days 0 hr. 0 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/db/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/du104/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=du104)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "PG14" has 1 instance(s).
  Instance "PG14", status UNKNOWN, has 1 handler(s) for this service...
Service "fd87ad59f36e192ce0530e0aa8c0c969" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@du104 ~]$

[oracle@du104 ~]$ tnsping mypg                               

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-OCT-2023 12:06:47

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/db/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=PG14)) (HS=OK))
OK (0 msec)
[oracle@du104 ~]$

6.通过dblink实现跨库访问

注意：

查询时schema名与表名一定带双引号。oracle 默认大写；pg默认都是小写；
connect to “postgres” identified by “postgres” 是指PostgreSQL中的用户；
"MYPG"是tnsnames.ora中指定的;

[oracle@du104 ~]$  sqlplus sys/oracle@192.168.10.14:1521/orclpdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 21 12:08:27 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@ORCLPDB> create database link mypg14 connect to "postgres" identified by "postgres" using 'MYPG';

Database link created.

sys@ORCLPDB> select * from "public"."t"@mypg14;

        id
----------
         1

sys@ORCLPDB>