一、目标

实现通过Oracle dblink 调用PostgreSQL

二、环境

• CentOS 7.6.1810
• PostgreSQL 14.6
• Oralce 19c

三、步骤

下载介质

3.1. 安装unixODBC 和 PostgreSQL ODBC驱动

Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。以下操作都是在Oracle服务器上进行：

3.1.1. root安装unixODBC：

yum install unixODBC unixODBC-devel.x86_64

unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器

3.1.2. root安装PostgreSQL ODBC驱动

-- 编译安装，root执行：
-- 安装postgresql14.6 
tar -xvf postgresql-14.6.tar.gz
cd postgresql-14.6/
./configure --prefix=/opt/pg14.6/ --with-pgport=1921
gmake world
gmake install-world

ln -s /opt/pg14 /opt/pgsql

-- 设置环境变量
PATH=$PATH:/opt/pgsql/bin

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

-- 生效
ldconfig

-- 驱动安装
tar xf psqlodbc-13.02.0000.tar.gz
cd psqlodbc-13.02.0000
./configure
make
make install

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

3.2. 配置odbcinst.ini和odbc.ini

root用户执行：

vim /etc/odbcinst.ini（不区分大小写）

# Driver from the postgresql-odbc package

# Setup from the unixODBC package

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver64        = /usr/local/lib/psqlodbcw.so

Setup64         = /usr/lib64/libodbcpsqlS.so

FileUsage       = 1

/usr/local/lib/psqlodbcw.so 是PostgreSQL ODBC驱动的库
/usr/lib64/libodbcpsqlS.so 是unixODBC的库

[root@ora19c ~]# cat /etc/odbc.ini

[PG]

Description = Test to pg

Driver = PostgreSQL

Database = postgres

Servername = 192.168.150.132

UserName = testuser

Password = postgres

Port = 1921

ReadOnly = 0

ConnSettings = set client_encoding to UTF8

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

3.3.测试ODBC连接能否工作:

[root@ora19c ~]# isql -v pg

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                    |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from bar;

+------------+---------------------------------------------------+
| id         | name                                              |
+------------+---------------------------------------------------+
| 1          | a                                                 |
| 2          | c                                                 |
+------------+---------------------------------------------------+
SQLRowCount returns 2
2 rows fetched

3.4. 配置Oracle网络

grid用户配置listener.ora；oracle用户配置其余两个文件，涉及3个配置文件的修改：

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

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

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

#Backup file is  /oracle/app/grid/crsdata/ora19c/output/listener.ora.bak.ora19c.grid line added by Agent

# listener.ora Network Configuration File: /oracle/app/19.0.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON            # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = pdb)
        (ORACLE_HOME = /oracle/app/19.0.0/grid)
        (SID_NAME = orcl)
    )
    (SID_DESC =
        (PROGRAM = dg4odbc)
        (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)
        (SID_NAME = pginstance)
    )
)

3.4.2. $ORACLEHOME/hs/admin/init<SID_NAME>.ora

实例pginstance连接PostgreSQL数据库时，Oracle会到$ORACLE_HOME/hs/admin/下找名为initpginstance.ora的配置文件，里面是关于ODBC连接的信息，Oracle使用这些信息连接PostgreSQL，例如，在测试环境中文件内容如下：

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/local/lib/psqlodbcw.so

# postgresql encoding=utf8
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

HS_FDS_CONNECT_INFO = PG 这个参数指向odbc.ini文件中的ODBC连接名称[PG]，参考前面。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

3.4.3. $ORACLE_HOME/network/admin/tnsnames.ora中，增加一项黄底字体：

# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
)

pglink =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.150.135)(PORT=1521))
    (CONNECT_DATA=(SID=pginstance))
    (HS=OK)
  )

pglink是客户端到PostgreSQL实例的连接配置，只要注意一点：“SID = pginstance”中，SID应设置为listener.ora中PostgreSQL的实例名，例如这里是pginstance。

pglink这个名称，将在创建dblink时使用。

3.4.4. 创建dblink

在sqlplus或其它终端执行

drop database link dl_pgsql;
create database link dl_pgsql connect to "testuser" identified by "postgres" using 'pglink';

其中’pglink’是tnsnames.ora文件里定义的，这里把Oracle数据库作为客户端，去连接pglink所指的实例。
“testuser” identified by “postgres” 是指PostgreSQL中的用户。

这各名称为dl_pgsql的dblink所连接的PostgreSQL数据库名由odbc.ini的Database参数指定。postgres用户应该对Database所指的数据库，有足够访问权限。

3.5. 通过dblink访问PostgreSQL

假设emp表在名为postgres的数据库中的名为public的schema下，Oracle完成上面的配置后，可以这样访问：

注意：查询时用户名与表名 一定带双引号。oracle 默认大写；pg默认都是小写。

SQL> select * from "pniuser"."test_dblink"@dl_pgsql;

        id

----------

         1

SQL> select * from "pniuser"."bar"@dl_pgsql;

        id name

---------- --------------------------------------------------

         1 a

         2 c

3.6. 创建和使用同义词:

SQL> CREATE SYNONYM test_dblink FOR "pniuser"."bar"@dl_pgsql;
Synonym created.

SQL> select * from test_dblink;
        id name
---------- --------------------------------------------------

         1 a

         2 c

四、 参考

ORA-28500: Connection From ORACLE To A Non-Oracle System Returned This Message: C (Doc ID 2325424.1)

五、 问题解决

cd psqlodbc-13.02.0000
./configure 时报错：

configure: error: libpq library version >= 9.2 is required

解决：

**1.安装postgresql14.6 **

2.pg bin path设置环境变量 
PATH=$PATH:/opt/pgsql/bin

**3.lib库**
cat /etc/ld.so.conf.d/postgresql-x86_64.conf
/opt/pgsql/lib

-- 生效
ldconfig