Oracle配置dblink访问PostgreSQL

原创怕晒的太阳 2022-07-06

3972

1. 概述

Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。本文是以RedHat6.5为例进行安装测试。

2. 安装ODBC驱动管理

安装ODBC驱动分成两部分：1.安装unixODBC；2.安装PostgreSQL的ODBC驱动。本文安装操作实在Redhat6.5完成。

2.1. 安装PostgreSQL的ODBC驱动

目前，安装PostgreSQL11版本优化版本，ODBC可以获取pg11相应的ODBC版本。具体见附件。具体安装操作在root用户下完成：

Ø 安装ODBC的驱动rpm包,安装包见附件：

rpm -ivh postgresql11-libs-11.5-1PGDG.rhel6.x86_64.rpm

rpm -ivh postgresql11-odbc-11.01.0000-1PGDG.rhel6.1.x86_64.rpm

安装完成后，在目录/usr/pgsql-11/下生成lib和share相关目录。pg的odbc驱动放在lib下。

2.2. 安装unixODBC

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

建议Linux环境安装unixODBC 2.3.7以上版本。可以使用isql --v查询获取安装unixODBC版本。如果版本不符合要求，请安装附件的unixODBC 2.3.7。具体安装方法如下，在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

3. 配置odbc.ini且连接测试

上一章节介绍过安装ODBC驱动管理，在安装unixODBC管理器部分，在/usr/local/odbc/etc下，找到配置文件odbc.ini。具体添加如下内容：

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

[atlasdb]

#数据源说明，根据实际情况自定义

Description = PostgreSQL connection to atlasdb

#使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置

Driver = /usr/pgsql-11/lib/psqlodbcw.so

Setup = /usr/pgsql-11/lib/psqlodbcw.so

#数据库名，远程访问的数据库名

Database = atlasdb

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

Servername = 192.168.2.10

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

UserName = postgres

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

Password = postgres

#数据库端口

Port = 5432

SocketBufferSize = 4096

FetchBufferSize = 500

ReadOnly = Yes

RowVersioning = No

ShowSystemTables = No

#查询结果的字符编码

ConnSettings = set client_encoding to UTF8

连接ODBC测试，在root用户下isql [定义数据源名称],下图atlasdb是上面的定义数据源名称，如果出现如下图内容表示连接成功。

4. 创建oracle的隐藏.odbc.ini文件

上一章节ODBC连接远程VastbaseE100测试成功。本节在oracle数据库安装用户下，在/home/oracle下创建隐藏文件.odbc.ini。由root用户切换到oracle用户下，使用vi ~/.odbc.ini添加如下内容：

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

[PG_LINK]

#数据源说明，根据实际情况自定义

Description = PostgreSQL connection to atlasdb

#使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置

Driver = /usr/pgsql-11/lib/psqlodbcw.so

Setup = /usr/pgsql-11/lib/psqlodbcw.so

#数据库名，远程访问的数据库名

Database = atlasdb

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

Servername = 192.168.2.10

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

UserName = postgres

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

Password = postgres

#数据库端口

Port = 5432

SocketBufferSize = 4096

FetchBufferSize = 500

ReadOnly = Yes

RowVersioning = No

ShowSystemTables = No

#查询结果的字符编码

ConnSettings = set client_encoding to UTF8

5. 配置监听文件

在$ORACLE_HOME/network/admin/下面文件listener.ora添加如下内容。它代表PostgreSQL提供的数据库服务，Oracle将PostgreSQL也看成一个Oracle实例，SID_NAME = PGINSTANCE是给它定义一个实例名。这个实例名决定了配置透明网关配置文件$ORACLE_HOME/hs/admin/init<SID_NAME>.ora的文件名，例如initPG_LINK.ora。

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=PG_LINK)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

)

6. 配置透明网关

在$ORACLE_HOME/hs/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字，其中HS_FDS_CONNECT_INFO = PG 这个参数指向隐藏文件.odbc.ini中的ODBC定义数据源名称[PG_LINK]。

#HS初始化变量

HS_FDS_CONNECT_INFO = PG_LINK

HS_FDS_TRACE_LEVEL = 255

HS_FDS_SHAREABLE_NAME=/usr/pgsql-11/lib/psqlodbcw.so

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

HS_NLS_NCHAR=UCS2

#ODBC的环境变量，即上面隐藏文件.odbc.ini的位置

set ODBCINI=/home/oracle/.odbc.ini

7. 配置tnsnames.ora文件

在$ORACLE_HOME/network/admin/下面文件tnsnames.ora添加如下内容。其中PG_LINK是客户端到PostgreSQL实例的连接配置，注意一点：“SID = PG_LINK”中，SID应设置为listener.ora中PostgreSQL的实例名，例如这里就是PG_LINK。PG_LINK这个名称，将在创建PG_LINK时使用。

PG_LINK =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST = 192.168.2.11)(PORT=1521))

(CONNECT_DATA=(SID=PG_LINK)) #

(HS=OK)

)

8. 创建DBLINK

创建dblink的语句如下：

create database link dblink的名称 connect to "用户名" identified by "密码" using 'PG_LINK';

其中'PG_LINK'是tnsnames.ora文件里定义的，这里把Oracle数据库作为客户端，去连接pglink所指的实例。"atlasdb" identified by "Gauss_234" 是指PostgreSQL中的用户。名称为to_pglink的dblink所连接的PostgreSQL数据库名由隐藏文件.odbc.ini的Database参数指定。postgres用户应该对Database所指的数据库，有足够访问权限。