oracle11查看dblink,配置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>