折腾了oracle到greenplum的dblink访问，分享给大家：
要连接的两端：
oracle rhel 6.5 64位 11.2.0.4
greenplum cenos6.5 64位
操作都在oracle服务器上完成：
root登录操作
安装驱动(第一次操作yum安装的小伙伴可参考我的另一个yum安装步骤贴)
yum install -y
unixODBC-devel-2.2.14-12.el6_3.x86_64.rpm
unixODBC-2.2.14-12.el6_3.x86_64.rpm
postgresql-8.4.18-1.el6_4.x86_64.rpm
postgresql-contrib-8.4.18-1.el6_4.x86_64.rpm
postgresql-devel-8.4.18-1.el6_4.x86_64.rpm
postgresql-libs-8.4.18-1.el6_4.x86_64.rpm
postgresql-odbc-08.04.0200-1.el6.x86_64.rpm
postgresql-plperl-8.4.18-1.el6_4.x86_64.rpm
postgresql-plpython-8.4.18-1.el6_4.x86_64.rpm
postgresql-pltcl-8.4.18-1.el6_4.x86_64.rpm
postgresql-server-8.4.18-1.el6_4.x86_64.rpm
—————————————————————-
查看驱动配置
cat /etc/odbcinst.ini
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib64/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
—————————————————————-
测试ODBC驱动是否安装成功
# odbcinst -q -d
[PostgreSQL]
—————————————————————-
配置DSN
/etc/odbc.ini
[gp]
Description = Test to gp
Driver = PostgreSQL
Trace = Yes
TraceFile = /tmp/sql.log
Database = mygp
Servername = 192.168.1.101
UserName = gpuser
Password = gpuser
Port = 5431
ReadOnly = 0
—————————————————————-
测试连通性
su – oracle
isql -v gp — /etc/odbc.ini中的中括号指定的名字
报错：[28000][unixODBC]FATAL: no pg_hba.conf entry for host “192.168.1.99”, user “gpuser”, database “mygp”, SSL off
/*
此问题：PostgreSQ数据库为了安全，它不会监听除本地以外的所有连接请求
需要在master机器上 /data/master/gpseg-1
vim pg_hba.conf
添加：(信任192.168.1.99 登录数据库)
host all all 192.168.1.99/32 trust
*/
netstat -tuln 测试可以看到192.168.1.99
再测试：
isql -v gp
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>
测试成功!
—————————————————————-
–配置 HSODBC
vim $ORACLE_HOME/hs/admin/initgp.ora
HS_FDS_CONNECT_INFO = gp #对应odbc.ini中括号
HS_FDS_TRACE_LEVEL = debug #当为’debug’时，用于检查报错，在$ORACLE_HOME/hs/log/下; 成功后最好设置为off;
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so #对应odbcinst.ini Driver
HS_LANGUAGE=american_america.we8mswin1252 #必须写，否则dblink出现ora-28500报错
HS_NLS_NCHAR=UCS2 #必须写，否则dblink出现ora-28500报错
set ODBCINI=/etc/odbc.ini
—————————————————————-
–配置监听
vim $ORACLE_HOME/network/admin/listener.ora
添加
SID_LIST_LISTENER=
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = gp)
(ENVS=LD_LIBRARY_PATH = /u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/odbc/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib:/usr/lib64)
)
vim $ORACLE_HOME/network/admin/tnsnames.ora
gp=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.99) (PORT = 1521) )
)
(CONNECT_DATA = (SID = gp) )
(HS=OK)
)
—————————————————————-
–重启监听，要有 gp 服务
$ lsnrctl stop
$ lsnrctl start
—————————————————————-
–tnsping测试服务
[oracle@ttt admin]$ tnsping gp
TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 10-MAY-2017 16:46:12
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.99) (PORT = 1521))) (CONNECT_DATA =(SID= gp)) (HS=OK))
OK (0 msec)
—————————————————————-
–创建dblink
create public database link gpuser_gp connect to “gpuser” identified by “gpuser” using ‘gp’;
–测试
select * from “pg_user”@gpuser_gp
提示ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
connction string lacks some options {08001,NativeErr = 202} 其实是.odbc.ini文件里面少了个steup选项
以及$ORACLE_HOME/hs/admin/initgp.ora 中必须写两个参数
HS_LANGUAGE=american_america.we8mswin1252
HS_NLS_NCHAR=UCS2
select “usename” from “pg_user”@gpuser_gp;
OK!