使用ORACLE GATEWAY 与Greenplum HAWQ 1.0 互通

现场环境:Oracle 11g 64bit linux edition

Greenplum Pivotal HD HAWQ 1.0

操作系统 RHEL 6.3 x86_64

为了实现在Oracle中直接访问HAWQ中的数据,需要首先安装Oracle Gateway 11g x86_64 linux edition

 

需要安装的软件:Oracle Gateway 11g x86_64 linux edition

DATADIRECT_CONNECT64_ODBC_7.0.1for Greenplum (必须是7版本的,6的不行,rhel6带的unixodbc也不行)

 

( 后记,Oracle 11g database server 中貌似也带了hs的功能,直接使用database server 当gateway应也可,不用再单独安装gateway)

安装路径: Oracle Gateway:/home/oracle/product/11.2.0/tg_1

Datadirect ODBC :/home/oracle/ddodbc

Oracle database :/home/oracle/app/oracle/product/11.2.0/dbhome_1

 

配置:

修改/home/oracle/product/11.2.0/tg_1/hs/admin/initdg4odbc.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = Greenplum
#HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_LEVEL= ODBC
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc_trace.log
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_FDS_TIMESTAMP_MAPPING = “TIMESTAMP(6)”
HS_FDS_FETCH_ROWS=1
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
HS_NLS_NCHAR = UCS2
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
set ODBCINST=/etc/odbcinst.ini

修改/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

备注,此处直接使用database的listener。未使用gateway的listener ,在文件后追加:

SID_LIST_LISTENER =
        (SID_LIST =
                (SID_DESC =
                        (SID_NAME = dg4odbc)
                        (ORACLE_HOME =/home/oracle/product/11.2.0/tg_1 )
                        (ENV=”LD_LIBRARY_PATH=/usr/local/lib:/home/oracle/product/11.2.0/tg_1/lib:/home/oracle/ddodbc/lib”)
                        (PROGRAM = dg4odbc)
                )
        )
)
修改/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
增加:
dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  )
修改/etc/odbc.ini
[oracle@server3 admin]$ cat /etc/odbc.ini
[ODBC Data Sources]
Greenplum=Greenplum
[ODBC Data Sources]
Greenplum Wire Protocol=DataDirect 7.0 Greenplum Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/home/oracle/ddodbc
Trace=0
TraceFile=odbctrace.out
TraceDll=/home/oracle/ddodbc/lib/ddtrc26.so
[Greenplum]
Driver=/home/oracle/ddodbc/lib/ddgplm26.so
Description=DataDirect 7.0 Greenplum Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=haier
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EnableKeysetCursors=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=server3
InitializationString=
KeyPassword=
KeysetCursorOptions=0
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=54321
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10
Charset=utf8
[ODBC]
TraceFile=/tmp/sql.log
Trace=1

 

 

修改/etc/odbcinst.ini

[ODBC Drivers]
DataDirect 7.0 Greenplum Wire Protocol=Installed
[ODBC Translators]
OEM to ANSI=Installed
[Administrator]
HelpRootDirectory=/home/oracle/ddodbc/adminhelp
[ODBC]
#This section must contain values for DSN-less connections
#if no odbc.ini file exists. If an odbc.ini file exists,
#the values from that [ODBC] section are used.
[DataDirect 7.0 Greenplum Wire Protocol]
Driver=/home/oracle/ddodbc/lib/ddgplm26.so
Setup=/home/oracle/ddodbc/lib/ddgplm26.so
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=/home/oracle/ddodbc/help
SQLLevel=0

 

重启listener

lsnrctl status 中应有一下内容:

Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server3)(PORT=1521)))
Services Summary…
Service “dg4odbc” has 1 instance(s).
  Instance “dg4odbc”, status UNKNOWN, has 1 handler(s) for this service…
而后在数据库中创建dblink,注意用户名和密码需要使用双引号

create database link DBLINK
connect to “gpadmin” identified by “gpadmin”
using ‘dg4odbc’;

执行sql语句即可返回成功(注意HAWQ中的表名和字段名需要使用双引号,用小写表示):

select count(*) from “poc1″@dblink;

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据