dblink gbase8a-oracle

一、配置步骤
这里假设oracle11g和GBase8a MPP Cluster数据库已经安装完毕。
    配置包含以下步骤：
安装GBaseODBC驱动；
配置透明网关；
配置监听；
配置TNS；
创建oracle11g到GBase8a 的dblink。
二、配置过程演示
1.在oracle的服务器上安装GBase的ODBC驱动
[root@oraclehost ODBC]# ./GBaseODBC-8.3.81.53-build53.7.el6.0.x86_64.bin
安装完成后使用如下命令查看相应文件路径
[root@oraclehost ODBC]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

修改/etc/odbc.ini，修改后内容如下：
[ODBC Data Source]
test = GBase 8a ODBC Driver

[test]
Driver = /usr/lib64/libgsodbc8.so
DATABASE = demo
DESCRIPTION = GBase 8a ODBC 8.3.1.5 Sample
PORT = 5258
SERVER = 10.9.141.10
UID = gbase
PWD = gbase20110531
CHARSET = utf8
TRACEFILE = /home/gbase/odbc.trc
TRACE = OFF

测试isql是否成功：
[root@oraclehost ODBC]# isql test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
注意：odbc.ini文件内test指ODBC的连接名，database后指的是GBase 8a集群的某一个库名。如需添加多个link，需要在文件下方复制多个库名且同时添加多个listen和tns配置信息。
[test]
Driver = /usr/lib64/libgsodbc8.so
DATABASE = demo
DESCRIPTION = GBase 8a ODBC 8.3.1.5 Sample
PORT = 5258
SERVER = 10.9.141.10
UID = gbase
PWD = gbase20110531
CHARSET = utf8
TRACEFILE = /home/gbase/odbc.trc
TRACE = OFF
[gbase]
Driver = /usr/lib64/libgsodbc8.so
DATABASE = gbase
DESCRIPTION = GBase 8a ODBC 8.3.1.5 Sample
PORT = 5258
SERVER = 10.9.141.10
UID = gbase
PWD = gbase20110531
CHARSET = utf8
TRACEFILE = /home/gbase/odbc.trc
TRACE = OFF

2.配置透明网关
进入$ORACLE_HOME/hs/admin/路径，建立一个新文件inittest.ora
内容如下：

[oracle@oraclehost admin]$ vi inittest.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 = test
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libgsodbc8.so
#HS_FDS_SHAREABLE_NAME= /usr/lib64/libodbc.so.1
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
set LD_LIBRARY_PATH=/usr/lib64
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR=UCS2
---------------------分割线----------------------
HS_FDS_TRACE_LEVEL可以修改为debug，打开透明网关的debug日志，日志路径.../hs/log目录下
HS_LANGUAGE此处指定数据库的字符类型。

3.配置监听
[oracle@oraclehost admin]$ vi listener.ora 
# listener.ora Network Configuration File: /home/oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
 )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = test)
     (ORACLE_HOME = /home/oracle/u01/app/oracle/product/11.2.0/db_1)
     (PROGRAM = dg4odbc)
     (ENVS=LD_LIBRARY_PATH=/home/oracle/u01/app/oracle/product/11.2.0/db_1/lib:/usr/lib64)
   )
 )

ADR_BASE_LISTENER = /home/oracle/u01/app/oracle
（上述蓝色字体部分为增加内容）重启监听，显示成功。
注：如果listener.ora中已经存在SID_LIST_LISTENER =
只需要在SID_LIST中增加SID_DESC即可。样例:
[oracle@t3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /disk2/app/DB/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.7)(PORT = 1521))
   )
 )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = test)
     (ORACLE_HOME = /disk2/app/DB/oracle)
     (PROGRAM = dg4odbc)
     (ENVS=LD_LIBRARY_PATH=/disk2/app/DB/oracle/lib:/usr/lib64)
   )
   (SID_DESC =
     (SID_NAME = tgbk)
     (ORACLE_HOME = /disk2/app/DB/oracle)
     (PROGRAM = dg4odbc)
     (ENVS=LD_LIBRARY_PATH=/disk2/app/DB/oracle/lib:/usr/lib64)
   )
 )
ADR_BASE_LISTENER = /disk2/app/DB/
此处值得注意的是SID_LIST_LISTENER是ORACLE_HOME的路径注意改成oracle的安装路径。

4.配置TNS
[oracle@oraclehost admin]$ vi tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )

test =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = test)
   )
   (HS = OK)
 )
（上述蓝色字体部分为增加内容）
添加多个TNS直接复制蓝色部分，示例如下：
gbase =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = gbase)
   )
   (HS = OK)

[oracle@oraclehost admin]$ tnsping test

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-MAR-2013 01:20:44

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = test)) (HS = OK))
OK (30 msec)

5.创建link
[oracle@gbase666 admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 26 16:23:01 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba;
Connected.
SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> grant dba to test;

Grant succeeded.

SQL> connect test/test;
Connected.
SQL> create database link testx connect to "gbase" identified by "gbase20110531" using 'test';

Database link created.

SQL>select count(*) from demo@testx;
COUNT(*)
----------
        20

测试成功！
注意：如果查询结果出现乱码需要指定oracle库的字符格式NLS_LANG=’SIMPLIFIED Chinese_CHINA.ZHS16GBK’添加到当前用户的.bash_profile中。并且source .bash_profile环境变量生效。添加多个监听和TNS之后，使用oracle用户执行lsnrtcl stop;lsnrctl stat;重新启动监听。并且shutdown immediate；startup；重启oracle数据库。
三、透明网关的限制条件
1)在sql语句中外部表的列名需要使用双引号包围。
2)在sql语句的过滤条件中如果出现字符串、日期类型时在等号右面输入值需要增加函数处理。
where  xxx_date = to_date('2013-05-01','yyyy-mm-dd');
where  xxx_varchar = to_char('2aaacbc');
where  xxx_char=to_char('caqt');
3)在oracle中使用外部表做表关联时，oracle透明网关会把外部表数据抽取到oracle中后再做关联，如果外部表数据巨大的情况下性能会比较低，建议在使用过程中避免这种情况的出现。
4)sql中不能涉及blob和clob类型的字段。
5)执行update、delete语句当过滤条件中如果涉及date、char、varchar类型的字段时必须使用DBMS_HS_PASSTHROUGH来执行。
6)查看所有dblink
select * from dba_db_links
7)别名访问
SQL> select t.*, "T2"."c1" from t, test@gt1 T2 where t.c = '1' and "T2"."c1" = 'aaab';

     C c1
---------- --------------------
     1 aaab
     1 aaab
     1 aaab

SQL>  
通过互联网查询到Oracle官方提供的限制
1)BLOB and CLOB data cannot be read by pass-through queries
2)Updates or deletes that include unsupported functions within a WHERE clause are not allowed
3)Does not support stored procedures
4)Cannot participate in distributed transactions; they support single-site transactions only
5)Does not support multithreaded agents
6)Does not support updating LONG columns with bind variables
7)Does not support rowids
8)COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
9)UPDATE and DELETE statements with the WHERE CURRENT OF clause are notsupported by the gateway because they rely on the Oracleimplementation.
10)The gateway does not support the CONNECT BY clause in a SELECT statement.
11)The Oracle ROWID implementation is not supported.
12)EXPLAIN PLAN Statement
13)The gateway does not support theSTART WITH condition and NOWAIT in a SELECT statement.
参考文档：
Support.oracle.com
Database Gateway Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64
《Oracle Database Gateway for ODBC User’s Guide》.