Oracle使用psqlODBC访问PostgreSQL

[说明]

Oracle数据库网关可以透明地访问其他数据库，Oracle HS Agent将SQL语句转换为非Oracle数据库（PostgreSQL）可以理解的SQL语句，

并通过ODBC 数据源发送该SQL语句，在PostgreSQL中执行后将结果返回Oracle数据库，本文介绍如何配置并访问PostgreSQL数据库。

[测试环境]

Oracle 服务器

操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo)

软件版本：Oracle 11.2.0.4

主机地址：192.168.56.101

监听端口：1521

PostgreSQL 服务器

操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo)

软件版本：PostgreSQL 12.0

主机地址：192.168.56.101

服务端口：5432

数据库名：runoobdb

用户密码：testuser/123456

[配置]

1  PostgreSQL 服务器

安装并创建相关数据库，使用pgadmin测试，确保远程连接正常。

2  Oracle 服务器

2.1 安装相关包

安装 unixODBC 软件包

yum -y install unixODBC*

安装 PostgreSQL ODBC 驱动

yum -y install postgresql-odbc postgresql-libs

2.2创建数据源

为驱动创建数据源

odbcinst -j

odbc_config --odbcini --odbcinstini

2.3配置 psqlODBC驱动库

/etc/odbcinst.ini

默认已经创建了[PostgreSQL]条目。

 # Example driver definitions

  # Driver from the postgresql-odbc package

  # Setup from the unixODBC package

  [PostgreSQL]

  Description     = ODBC for PostgreSQL

  Driver          = /usr/lib/psqlodbc.so

  Setup           = /usr/lib/libodbcpsqlS.so

  Driver64        = /usr/lib64/psqlodbc.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

2.4创建数据源

数据源名称为 pgdsn，可自定义其他名称，注意在后继配置中引用时保持一致。

PostgreSQL数据库名为 runoobdb, 服务器/端口为 192.168.56.101/5432, 用户名/密码为 testuser/123456

/etc/odbc.ini

[pgdsn]

Driver = PostgreSQL

Description = PostgreSQL ODBC Driver

Database = runoobdb

Servername = 192.168.56.101

Username = testuser

Password = 123456

Port = 5432

UseDeclareFetch = 1

CommLog = /tmp/pgodbclink.log

Debug = 1

LowerCaseIdentifier = 1

2.5 测试 ODBC 连接

[oracle@rhel7 admin]$ isql pgdsn

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> select current_database(),inet_server_port();

+-----------------------------------------------------------------+-----------------+

| current_database                                                | inet_server_port|

+-----------------------------------------------------------------+-----------------+

| runoobdb                                                        | 5432            |

+-----------------------------------------------------------------+-----------------+

2.6 创建 Oracle 数据库网关

在 $ORACLE_HOME/hs/admin 目录下创建 init<sid>.ora 文件，本测试为 initpgdsn.ora。

HS_FDS_CONNECT_INFO = pgdsn

HS_FDS_TRACE_LEVEL = DEBUG

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_NLS_NCHAR=UCS2

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

set ODBCINI=/etc/odbc.ini

2.7 调整 Oracle 监听配置

注意集群环境的监听文件位置为$ORACLE_HOME/network/admin/listener.ora ，rac环境为grid 下面的文件

SID_LIST_LISTENER =

 (SID_LIST =

    (SID_DESC=

     (SID_NAME = pgdsn)

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

      (ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1")

      (PROGRAM=dg4odbc)

   )

 ) 

2.8 添加 Oracle TNS 配置

配置文件为 $ORACLE_HOME/network/admin/tnsnames.ora

pgdsn =

  (DESCRIPTION=

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

    (CONNECT_DATA=(sid=pgdsn))

    (HS=OK)

) 

2.9 重启监听

lsnrctl stop

lsnrctl start

2.10 创建 Database Link

SQL> create public database link pglink connect to "testuser" identified by "123456" using 'pgdsn';

2.11 访问测试

注意表名需要添加双引号。

SQL> select count(*) from "pg_settings"@pglink;

select * from "t1"@pglink;

create public database link pglink connect to "postgres" identified by "123123" using 'pgdsn';

19.3 连接14.5过程中报错如下：

[oracle@zc ~]$ isql -v pgdsn

[08001][unixODBC]SCRAM authentication requires libpq version 10 or above

[oracle@zc ~]$ odbcinst -j

unixODBC 2.3.11

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /home/oracle/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

检查应该是密码验证方式的问题 将密码改为trust 后暂时过去了，正规的办法应该升级客户端