配置odbc透明网关实现oracle访问postgres DB

最近帮客户配置了一下通过odbc透明网关，实现在oracle内通过db link访问postgres DB。

简单记录一下：

（1）listener.ora和tnsnames.ora的配置：

[wsj81@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /wsj/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_ORCL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = pgdb)
      (ORACLE_HOME = /wsj/oracle/app/product/11.2.0/dbhome_1)
      (ENV="LD_LIBRARY_PATH=/usr/lib64:/wsj/oracle/app/product/11.2.0/dbhome_1/lib:usr/local/lib")
      (PROGRAM=dg4odbc)
    )
  )
 
ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.89.64.111)(PORT = 1531))
      )
    )
  )
 
 
[wsj81@localhost admin]$
[wsj81@localhost admin]$ cat  tnsnames.ora
pgdb =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=19.89.64.111)(PORT=1521))
    (CONNECT_DATA=(SID=pgdb))
    (HS=OK)
  )

（2）透明网关的配置，这2个文件在$ORACLE_HOME/hs/admin下

[wsj81@localhost admin]$ cat odbc.ini
[pgdb]
Driver  = /usr/local/lib/psqlodbcw.so
Setup  = /usr/lib64/libodbc.so
Description = POSTGRESQL
Servername = 88.23.19.121
Port      =  5434
Protocl   =  6.4
FetchBufferSize = 99
Username       = postgres
Password       = abcd4321
Database       = pgdb
ReadOnly       = no
Debug          = 1
ConnSettings= 
[wsj81@localhost admin]$
[wsj81@localhost admin]$
[wsj81@localhost admin]$
[wsj81@localhost admin]$ cat initpgdb.ora
HS_FDS_CONNECT_INFO = pgdb
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
set ODBCINI=/wsj/oracle/app/product/11.2.0/dbhome_1/hs/admin/odbc.ini
[wsj81@localhost admin]$
创建db link

SQL> create database link pgdb connect to "postgres" identified by "abcd4321" using 'pgdb';
 
Database link created.
 
SQL>

测试连接，注意在pg中的表要加双引号：

SQL> select * from "t1"@pgdb;
 
no rows selected
 
SQL>