透明网关

在oracle上安装dblink到gbase数据库

1、 在服务器上安装GbaseODBC驱动
2、 查看odbc安装后信息 odbcinst –j
3、 修改配置文件 vi /etc/odbc.ini  ，添加如下内容：
[ODBC Data Source]
test = GBase 8a ODBC Driver

[test]
Driver = /usr/lib64/libgsodbc8.so
DATABASE = test
DESCRIPTION = GBase 8a ODBC 8.3.1.5 Sample
PORT = 5258
SERVER = 192.168.105.44
UID = gbase
PWD = gbase20110531
CHARSET = utf8
TRACEFILE = /home/gbase/odbc.trc
TRACE = OFF
4、 测试isql是否成功  isql test
5、 Cd $ORACLE_HOME/hs/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

#
# 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
6、 修改监听文件 cd $ORACLE_HOME/network/admin  vi listener.ora，添加如下内容：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME= test)
      (ORACLE_HOME = /home/oracle/DB/oracle)
      (PROGRAM = dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/home/oracle/DB/oracle/lib:/usr/lib64)
    )
  )
7、 修改tns文件  cd $ORACLE_HOME/network/admin  vi tnsnames.ora ，添加如下内容：
testgbase =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.105.60)(PORT = 1521))
    (CONNECT_DATA =
      (SID = test)
    )
    (HS = OK)
  )
8、 测试服务名 tnsping testgbase
9、 登陆oracle数据库，创建用户名并赋予权限
Sqlplus /nolog
Conn / as sysdba
Create user ww identified by ww ;
Grant connect to ww;
Grant dba to ww;
Use ww/ww;
Create database link ww_test connect to "gbase" identified by "gbase20110531" using 'testgbase';

SQL> select * from demo@ww_test where "col1" < 5 and rownum<3;

      col1 col2      col3 col4   col5
---------- --------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 06-MAR-14 0123456789 Test Data No.:1 Items   0123456789
2 06-MAR-14 0123456789 Test Data No.:2 Items   0123456789

SQL>


在gbase上安装dblink到oracle数据库

1、上传透明网关软件到服务器，数据源配置文件：[透明网关软件安装目录]/conf/datedource/ oracle_link1 .properties，内容如下：
[ds1]
dataSource_IP=192.168.129.221
dataSource_port=1521
dataSource_dbname=ora11g
dataSource_dbtype=oracle
dataSource_user=gzsimis
dataSource_pwd=gzsimis

修改集群gcluster配置文件
gbase_dblink_gateway_ip = [透明网关IP;192.18.16.11]              -------集群ip
gbase_dblink_gateway_port = [透明网关配置的端口9898]
2、启动透明网关
sh gt.sh
3、查看是否启动
ps -ef | grep gateway
4、创建dblink
CREATE DATABASE LINK gbase_oracle connect to 'test' identified by 'test' using 'oracle_link1';    ----oracle_link1 名字为conf目录下文件的名字
5、查询已创建的dblink
select * from gbase.db_links;
6、删除dblink
drop database link link_name；
7、sql查询
select * from a@gbase_oracle ;       ------不支持limit语句


oracle 到 oracle安装dblink

1、查询当前用户是否具有创建全局dblink的权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); 
2、给用户赋予创建dblink的权限
grant create public database link to ww;
grant drop public database link to ww;
grant create database link to ww;
3、查看创建的dblink
select * from dba_db_links;  
select owner,object_name from dba_objects where object_type='DATABASE LINK';  
4、创建dblink
create database link gbase_oracle connect to ww identified by ww using 'ww_test_78';
5、进行查询测试
select count(*) from a@gbase_oracle;