[安装配置] 使用 unixODBC 实现 PostgreSQL 与 Oracle 互访

1. 环境
CentOS 5.6 x86_64 (2.6.18-194.32.1.el5.centos.plus)
Oracle 10g XE
Postgresql 8.4.10, 9.0.6, 9.1.2

注意:
在 Oracle 服务器上查看 hsodbc 版本
hsodbc 一般是 32 位的, 需要安装用 32bit 的 unixODBC 和 ODBC 驱动

$ file $ORACLE_HOME/bin/hsodbc
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/hsodbc: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), for GNU/Linux 2.2.5, stripped
--------------------------------------------------------------------------------
2. 下载
2-1. unixODBC
http://dev.centos.org/centos/5/testing/i386/RPMS/
unixODBC-2.2.12-1.el5s2.i386.rpm
unixODBC-devel-2.2.12-1.el5s2.i386.rpm
unixODBC-kde-2.2.12-1.el5s2.i386.rpm [可选]

http://dev.centos.org/centos/5/testing/x86_64/RPMS/
unixODBC-2.2.12-1.el5s2.x86_64.rpm
unixODBC-devel-2.2.12-1.el5s2.x86_64.rpm
unixODBC-kde-2.2.12-1.el5s2.x86_64.rpm [可选]

2. Oracle Instant Client (客户端免安装版)
http://www.oracle.com/technetwork/jp/topics/linuxx86-64soft-085244-ja.html
basic-10.2.0.5.0-linux-x64.zip
odbc-10.2.0.5.0-linux-x64.zip

3. ODBC-Link
http://www.cybertec.at/en/ressources/postgresql_downloads
ODBC-Link-1.0.5.tar.gz
--------------------------------------------------------------------------------
3. 安装

3-1. 安装 unixODBC RPMS
$ su - root
# rpm -ivh unixODBC-2.2.12-1.el5s2.i386.rpm unixODBC-devel-2.2.12-1.el5s2.i386.rpm
# rpm -ivh unixODBC-2.2.12-1.el5s2.x86_64.rpm unixODBC-devel-2.2.12-1.el5s2.x86_64.rpm
# ldconfig
# odbcinst -j
unixODBC 2.2.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini

3-2. 安装 Oracle Instant Client
如果已经安装过 Oracle 客户端, 则不需要安装
# locate libsqora.so.10.1
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1

# su - root
# unzip basic-10.2.0.5.0-linux-x64.zip
# unzip odbc-10.2.0.5.0-linux-x64.zip
# mv instantclient_10_2 /usr/local/src/instantclient_10_2

3-3. 安装 ODBC-Link
不同版本数据库需要重新编译安装, 已确认版本 (8.4.10, 9.0.4, 9.1.2)

# su - root
# pg_config <= 确认 PostgreSQL 9.1.2 配置信息
# tar zxvf ODBC-Link-1.0.5.tar.gz
# mv ODBC-Link-1.0.5 /usr/local/src/ODBC-Link-1.0.5
# cd /usr/local/src/ODBC-Link-1.0.5
# USE_PGXS=1 make clean <= 清除编译信息
# USE_PGXS=1 make
# USE_PGXS=1 make install
# USE_PGXS=1 make uninstall <= 卸载
# su - postgres
$ cd <PostgreSQL 9.1.2 安装路径>/share/contrib
$ psql -f odbclink.sql <db_name>
--------------------------------------------------------------------------------
4. 配置
4-1. 配置 ODBC 驱动
# su - root
# vim /etc/odbcinst.ini (添加)
[PG_ODBC_DRIVER]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

[ORA_ODBC_DRIVER]
Description     = ODBC for Oracle
Driver          = /usr/local/src/instantclient_10_2/libsqora.so.10.1

4-2. 配置数据源
# vim /etc/odbc.ini (添加)
[PG] <= Oracle 端使用
Driver = PG_ODBC_DRIVER
FileUsage = 1
Database = go
Servername = localhost
Port = 9112
Username = go
Password = go
# Trace = Yes
# TraceFile = /tmp/pg91x_odbc.log
# Debug = 0
# CommLog = 1

[ORA] <= PostgreSQL 端使用
Driver = ORA_ODBC_DRIVER
Database = //localhost:1521/XE
Port = 1521
UserID = go
Password = go
# Trace = Yes
# TraceFile = /tmp/ora10g_odbc.log
# Debug = 1
# CommLog = 1

重启 PostgreSQL 数据库
# su - postgres
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/src/instantclient_10_2
$ export TWO_TASK=//localhost:1521/XE
$ pg_ctl restart -mf

4-3. 配置 Oracle 端
# su - oracle
$ vim $ORACLE_HOME/hs/admin/init<SID>.ora (新建) <= 此处 SID 设置为 PG
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = topg.trc
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
set ODBCINI = /usr/lib/oracle/xe/.odbc.ini
set LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/lib/libodbc.so

$ vim $ORACLE_HOME/network/admin/tnsnames.ora (添加)
PG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost) (PORT = 1521))
    (CONNECT_DATA =
        (SID = <SID>) <= 此处 SID 设置为 PG
    )
    (HS = OK)
  )

$ vim $ORACLE_HOME/network/admin/tnsnames.ora (添加)
SID_LIST_LISTENER =
  (SID_LIST =
    <-- 开始 -->
    (SID_DESC =
      (SID_NAME = <SID>) <= 此处 SID 设置为 PG
      (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
      (PROGRAM = hsodbc)
    )
    <-- 结束 -->
  )

重启 Oracle 监听
$ lsnrctl stop
$ lsnrctl start
--------------------------------------------------------------------------------
5. 测试
5-1. PostgreSQL to Oracle
# su - postgres
$ psql <db_name>
=# select odbclink.connect('DSN=ORA'); <= 4-2 配置的数据源
 connect
---------
       1
(1 row)

=# select * from odbclink.query(1,'SELECT dummy FROM dual') as (name text);
 name
------
 X
(1 row)

5-2. Oracle to PostgreSQL
# su - oracle
$ sqlplus / as sysdba
SQL> create public database link <db_link_name> connect to "username" identified by "password" using '<DSN>';
# user: PostgreSQL username <= 4-2 中的配置
# pass: PostgreSQL password
# DSN : PostgreSQL 数据源

例:
SQL> create public database link PGLINK connect to "hoge" identified by "hoge" using 'PG';
SQL> select * from "t"@PGLINK;
a
-------
1

1 rows selected.