配置oracle11g通过dblink+透明网关访问GBase

一、在oracle的服务器上安装GBase的ODBC驱动；

二、执行odbcinst –j

 [oracle@t3 admin]$ odbcinst -j

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /home/oracle/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

[oracle@t3 admin]$ vim /etc/odbc.ini

[oracle@t3 admin]$ 

 [test]

Driver = /usr/lib64/libgsodbc8.so

DATABASE = test

DESCRIPTION = GBase 8a ODBC 8.3.1.5 Sample

PORT = 5258

SERVER = 192.168.137.5

UID = gbase

PWD = *********

CHARSET = utf8

TRACEFILE = /home/gbase/odbc.trc

TRACE = OFF

 

通过isql测试odbc配置情况

 

[oracle@t3 admin]$ isql test

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> select count(*) from test;

+---------------------+

| count(*)            |

+---------------------+

| 2097152             |

+---------------------+

SQLRowCount returns 1

1 rows fetched

SQL> quit

[oracle@t3 admin]$ 

三、配置oracle 透明网关

总共要配置三个文件

1、 $ORACLE_HOME/hs/admin/xxxx.ora

2、 $ORACLE_HOME/network/admin/listener.ora

2、 $ORACLE_HOME/network/admin/tnsnames.ora

这三个文件中的

$ORACLE_HOME/hs/admin/xxxx.ora中的HS_FDS_CONNECT_INFO = test

listener.ora 中的SID_NAME = test

tnsnames.ora中的SID = test

要一致。

其中$ORACLE_HOME/hs/admin/xxxx.ora的文件名命名规则：

init+HS_FDS_CONNECT_INFO.ora

例如我们在HS_FDS_CONNECT_INFO=test

inittest.ora

 

 

 [oracle@t3 admin]$ cd $ORACLE_HOME/hs/admin

[oracle@t3 admin]$ ls

extproc.ora  initdg4odbc.ora  listener.ora.sample  tnsnames.ora.sample

[oracle@t3 admin]$ vim 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

 ===========================

配置$ORACLE_HOME/network/admin/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 = t3)(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)

    )

  )

 

ADR_BASE_LISTENER = /disk2/app/DB/

 ================================================

配置$ORACLE_HOME/network/admin/tnsnames.ora

在原有文件中增加蓝色部分

# tnsnames.ora Network Configuration File: /disk2/app/DB/oracle/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = t3)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

testdblink =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SID = test)

    )

    (HS = OK)

  )

 

这里 testdblink创建dblink时使用。

 

四、创建oracle11g到GBase8a 的dblink

 

oracle@ynhdys2/oracle$sqlplus /nolog                                                                                            

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 7 16:04:23 2011

 

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

 

SQL> connect / as sysdba;

Connected.

SQL> create user gbase identified by gbase;

 

User created.

 

SQL> grant connect to gbase;

 

Grant succeeded.

 

SQL> grant dba to gbase;

 

Grant succeeded.

 

SQL> connect gbase/gbase;

Connected.

创建dblink

create database link gt1 connect to "gbase" identified by "*********" using 'testdblink'

 

SQL> select count(*) from test@gt1;

 

  COUNT(*)

----------

   2097152

 

SQL>