最近帮客户配置了一下通过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>
敢达决战官方正版 安卓版v6.7.9
下载敢达决战 安卓版v6.7.9
下载像素火影骨架佐助 (Perseverance Fire Shadow)手机版v1.16
下载要塞英雄 安卓版v33.20.0-39082670-Android
下载梦想城镇vivo最新版本 安卓版v12.0.1
梦想城镇vivo版是这款卡通风模拟经营类手游的渠道服版本,玩
怦然心动的瞬间 安卓版v1.0
怦然心动的瞬间是一款真人向的恋爱互动游戏,在游戏中玩家将扮演
曼尼汉堡店游戏 安卓版v1.0.3
曼尼汉堡店是一款非常好玩的精品恐怖类型冒险游戏,在这款游戏中
现代总统模拟器去广告版 安卓版v1.0.46
现代总统模拟器是一款休闲养成类游戏,可能对于不少的玩家来说都
现代总统模拟器付费完整版 安卓版v1.0.46
现代总统模拟器高级版在商店是需要付费的,相对于普通版本,高级