最近帮客户配置了一下通过odbc透明网关，实现在oracle内通过db link访问postgres DB。
简单记录一下：
（1）listener.ora和tnsnames.ora的配置：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
[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下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[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
|
1 2 3 4 5 |
SQL> create database link pgdb connect to "postgres" identified by "abcd4321" using 'pgdb'; Database link created. SQL> |
测试连接，注意在pg中的表要加双引号：
|
1 2 3 4 5 |
SQL> select * from "t1"@pgdb; no rows selected SQL> |