【www.bbyears.com--Oracle教程】
最近帮客户配置了一下通过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>