Oracle通过ODBC连接GreenPlum

折腾了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!