基于RHEL 6.5 x86_64系统，以Oracle 11gR2为例。
Oracle参考：
http://docs.oracle.com/cd/B28359_01/gateways.111/b31042/configodbc.htm
1. 以下步骤以单机为例。
2. <span style="font-family: Arial, Helvetica, sans-serif;">如果是Oracle RAC，则每台机器上需要配置 HS，与单机基本相同：每台rac主机上需要新增</span><span style="font-family: Arial, Helvetica, sans-serif;">SID_LIST_LISTENER</span><span style="font-family: Arial, Helvetica, sans-serif;">，</span><span style="font-family: Arial, Helvetica, sans-serif;">listener和tns中的host的地址设置为localhost，监听1521端口。目的是区别rac上的其它实际地址 和 浮动虚地址 监听1521的服务。</span>
(a)unixODBC版本（unixODBC-2.3.3）直接使用RHEL 中的rpm包，存在版本与PostgreSQL不能匹配的问题。建议安装标准的unixODBC的2.3.x的版本。wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gztar xzvf unixODBC-2.3.3.tar.gzcd unixODBC-2.3.3./configure --sysconfdir=/etcmakemake install提示：make install执行后，会在sysconfdir指定的目录创建odbc.ini和odbcinst.ini。touch /etc/odbcinst.initouch /etc/odbc.inimkdir -p /etc/ODBCDataSourcescp unixodbc_conf.h /usr/local/include/unixodbc_conf.h检查ODBC：[bruce@R1 etc]$ odbcinst -junixODBC 2.3.2DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /etc/odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW Size.: 8(b)安装PostgreSQL ODBC驱动（psqlodbc-09.02.0100）下载 psqlodbc-09.02.0100： wget https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-09.02.0100.tar.gz[bruce@R1 psqlodbc-09.02.0100]$ pwd/utxt/tarball/psqlodbc-09.02.0100[bruce@R1 psqlodbc-09.02.0100]$ lsaclocal.m4 connection.h execute.c Makefile.am odbcapi30w.c pgtypes.c psqlodbc.vcproj statement.hbind.c convert.c gsssvcs.c Makefile.in odbcapi.c pgtypes.h qresult.c testbind.h convert.h gsssvcs.h md5.c odbcapiw.c pgxalib.cpp qresult.h tuple.ccatfunc.h descriptor.c info30.c md5.h odbc-drop.sql pgxalib.def readme.txt tuple.hcolumninfo.c descriptor.h info.c misc.c odbc.sql psqlodbca.def resource.h version.hcolumninfo.h dlg_specific.c inouealc.c misc.h options.c psqlodbc.c results.c win32.makconfig dlg_specific.h installer msdtc_enlist.cpp parse.c psqlodbc.def setup.c win64.makconfig.h.in dlg_wingui.c license.txt multibyte.c pgapi30.c psqlodbc.dsp socket.c win_setup.hconfig.log docs loadlib.c multibyte.h pgapifunc.h psqlodbc.h socket.h win_unicode.cconfigure drvconn.c loadlib.h mylog.c pgenlista.def psqlodbc.rc sspisvcs.c xalibname.cconfigure.ac environ.c lobj.c odbcapi25w.c pgenlist.def psqlodbc.reg sspisvcs.hconnection.c environ.h lobj.h odbcapi30.c pgenlist.h psqlodbc.sln statement.c[bruce@R1 psqlodbc-09.02.0100]$ sudo yum install openssl-devel 编译中需要用到openssl[bruce@R1 psqlodbc-09.02.0100]$ whereis pgsql pgsql: /usr/lib64/pgsql /usr/include/pgsql /usr/local/pgsql /usr/share/pgsql[bruce@R1 psqlodbc-09.02.0100]$ ./configure --with-unixodbc --with-libpq=/usr/local/pgsql[bruce@R1 psqlodbc-09.02.0100]$ make[bruce@R1 psqlodbc-09.02.0100]$ sudo make install提示：make install执行后，相关的文件会默认拷贝到/usr/local/lib下。libtool: install: /usr/bin/install -c .libs/psqlodbcw.so /usr/local/lib/psqlodbcw.solibtool: install: /usr/bin/install -c .libs/psqlodbcw.lai /usr/local/lib/psqlodbcw.lalibtool: finish: PATH="/sbin:/bin:/usr/sbin:/usr/bin:/sbin" ldconfig -n /usr/local/lib(c)配置ODBC配置涉及odbc.ini和odbcinst.ini。修改需要root权限。配置后文件内容示例：[bruce@R1 etc]$ pwd/etc[bruce@R1 etc]$ cat odbc.ini[test]Description=testDriver=PostgreSQLTrace=YesTraceFile=/tmp/sql.logDatabase=pgdemoServername=192.168.110.233UserName=pgdemoPassword=pgdemoPort=5432Protocol=9.0ReadOnly=NoRowVersioning=NoShowSystemTables=NoShowOidColumn=NoFakeOidIndex=NoConnSettings=set client_encoding to gbk [bruce@R1 etc]$ cat odbcinst.ini# Example driver definitions# Driver from the postgresql-odbc package# Setup from the unixODBC package[PostgreSQL]Description = ODBC for PostgreSQLDriver = /usr/local/lib/psqlodbcw.so <- 此处使用PostgreSQL自带的ODBC驱动。#Driver = /usr/lib64/libodbcpsql.so#Setup = /usr/lib64/libodbcpsqlS.so#Driver64 = /usr/lib64/psqlodbc.so#Setup64 = /usr/lib64/libodbcpsqlS.soFileUsage = 1[bruce@R1 etc]$ (d)检查ODBC配置[bruce@R1 ~]$ odbcinst -junixODBC 2.3.2DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /etc/odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW Size.: 8使用isql查看test连接是否成功连接。[bruce@R1 ~]$ isql test+---------------------------------------+| Connected! || || sql-statement || help [tablename] || quit || |+---------------------------------------+SQL>如果isql test出错，则可以查看/tmp/sql.log中的提示信息：比如，odbcinst –j 显示的DATA SOURCES的路径下没有正确配置的odbc.ini和odbcinst.in，则可能出现错误：SQLState = IM002Native = 0x7fff7d168694 -> 0Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified][ODBC][3143][1434201581.066915][SQLGetDiagRec.c][680](e)配置Oracle HS假定ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1/。进入Oracle安装目录。创建HS初始化参数文件inittest.ora，并编辑内容。[oracle@R1 admin]$ pwd/opt/app/oracle/product/11.2.0/db_1/hs/admin[oracle@R1 admin]$ lsextproc.ora initdg4odbc.ora inittest.ora listener.ora.sample tnsnames.ora.sample[oracle@R1 admin]$ ls /usr/local/lib/*odbc*/usr/local/lib/libodbccr.la /usr/local/lib/libodbcinst.la /usr/local/lib/libodbc.la /usr/local/lib/psqlodbcw.la/usr/local/lib/libodbccr.so /usr/local/lib/libodbcinst.so /usr/local/lib/libodbc.so /usr/local/lib/psqlodbcw.so/usr/local/lib/libodbccr.so.2 /usr/local/lib/libodbcinst.so.2 /usr/local/lib/libodbc.so.2/usr/local/lib/libodbccr.so.2.0.0 /usr/local/lib/libodbcinst.so.2.0.0 /usr/local/lib/libodbc.so.2.0.0其中：/usr/local/lib/psqlodbcw.so： 为PostgreSQL自带的支持odbc的库/usr/local/lib/libodbc.so： 为编译安装后的unixODBC库。一般来说，ODBC自己的库带有的函数更多。HS的配置项HS_FDS_SHAREABLE_NAME 一般设置为unixODBC库。否则，可能会出现以下问题（可以通过设置HS的trace level，在$ORACLE_HOME/hs/log中查看trace文件输出）：Failed to load ODBC library symbol: /usr/local/lib/psqlodbcw.so(SQLAllocHandle)示例如下：[oracle@R1 ~]$ cd $ORACLE_HOME[oracle@R1 db_1]$ pwd/opt/app/oracle/product/11.2.0/db_1[oracle@R1 db_1]$ cd hs/admin[oracle@R1 admin]$ lsextproc.ora initdg4odbc.ora inittest.ora listener.ora.sample tnsnames.ora.sample[oracle@R1 admin]$ [oracle@R1 admin]$ cat inittest.ora HS_FDS_CONNECT_INFO = test# HS异常时，可以通过设置HS_FDS_TRACE_LEVEL=255，查看trace文件#进入Oracle安装目录$ORACLE_HOME/hs/log下查看最新的trace文件。HS_FDS_TRACE_LEVEL = 0 # 此处若设置为psqlodbc library，HS可能产生错误: Failed to load ODBC library symbol: /usr/local/lib/psqlodbcw.so(SQLAllocHandle)#HS_FDS_SHAREABLE_NAME = /usr/local/lib/psqlodbcw.so <- 错误HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so <- 此处设置为unixODBC的library。有时候psqlodbc自己带的库可能缺少某些函数。HS_FDS_DEFAULT_SCHEMA_NAME = public#HS_LANGUAGE=AMERICAN_AMERICA.UTF8#HS_NLS_NCHAR=GBKset ODBCINI=/etc/odbc.ini(f)配置Oracle监听vi /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora添加： (SID_DESC = (PROGRAM = dg4odbc) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) (SID_NAME = test) (ENVS=LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0/db_1/lib:/usr/local/lib:/etc/odbc.ini) )完整文件示例如下：[oracle@R1 ~]$ cd $ORACLE_HOME [oracle@R1 db_1]$ cd network/admin[oracle@R1 admin]$ lslistener.ora listener.ora.20150612 samples shrept.lst sqlnet.ora tnsnames.ora[oracle@R1 admin]$ cat listener.ora# listener.ora Network Configuration File: /uloc/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = demo) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) (SID_NAME = demo) ) (SID_DESC = (PROGRAM = dg4odbc) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) (SID_NAME = test) (ENVS=LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0/db_1/lib:/usr/local/lib:/etc/odbc.ini) )) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.233)(PORT = 1521)) <- 如果是RAC, 此处host地址使用 localhost ) ) ADR_BASE_LISTENER = /opt/app/oracle[oracle@R1 admin]$(g)配置Oracle TNStns配置添加如下：test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.233)(PORT = 1521)) <span style="font-family: Arial, Helvetica, sans-serif;"><- 如果是RAC, 此处host地址使用</span><span style="font-family: Arial, Helvetica, sans-serif;"> localhost</span><span style="font-family: Arial, Helvetica, sans-serif;"></span> (CONNECT_DATA = (SID = test) ) (HS = OK) ) 完整文件示例如下：[oracle@cats_db admin]$ pwd/opt/app/oracle/product/11.2.0/db_1/network/admin[oracle@R1 admin]$ lslistener.ora listener.ora.20150612 samples shrept.lst sqlnet.ora tnsnames.ora[oracle@R1 admin]$ cat tnsnames.ora demo = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.233)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = demo) ) )test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.233)(PORT = 1521)) (CONNECT_DATA = (SID = test) ) (HS = OK) )(h)重载Oracle监听[oracle@R1 admin]$ pwd/opt/app/oracle/product/11.2.0/db_1/network/admin[oracle@R1 admin]$ lsnrctl reload(i)测试dblink使用isql查看test连接是否成功连接。[oracle@R1 admin]$ isql test+---------------------------------------+| Connected! || || sql-statement || help [tablename] || quit || |+---------------------------------------+SQL>验证访问某个表：[oracle@R1 admin]$ pwd/opt/app/oracle/product/11.2.0/db_1/hs/admin[oracle@R1 admin]$ sqlplus / as sysdbaSQL> drop public database link test;SQL> CREATE PUBLIC DATABASE LINK test CONNECT TO "pgdemo" IDENTIFIED BY "pgdemo" using 'test';SQL>select * from "food"@"test";
