欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle通过ODBC连接GreenPlum

程序员文章站 2022-12-10 17:29:46
折腾了oracle到greenplum的dblink访问,分享给大家: 要连接的两端: oracle rhel 6.5 64位 11.2.0.4 greenplum ceno...

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