oracle11g新建dblink,Oracle11g异构dblink访问GBase 8a数据库

1:安装unixODBC驱动(有无RPM包的驱动无关紧要，最好是没有)

./configure --enable-gui=no

make && make install

若重复安装请先清除之前的安装痕迹

make clean

2:安装GBase8a的ODBC驱动

可以从gbase官网下载

3:配置ODBC访问gbase 8a数据源

vi /usr/local/etc/odbc.ini

[ODBC Data Sources]

test = GBase 8a ODBC Driver

[test]

Driver=/usr/lib64/libgsodbc8.so

Description=gbase

Server=127.0.0.1

Port=5258

UID= root

Password=1

Database= test

Socket=/tmp/gbase_8a_5258.sock

CHARSET     = utf8

TRACEFILE   = /tmp/myodbc-demodsn.trc

TRACE       = off

------------------------------------------

参数说明：

Port=5258   #gbase数据库访问端口

UID= root  #gbase数据库用户

Password=1  #gbase数据库密码

Database= test #选择gbase数据库中任意一个库名

------------------------------------------------

验证

[root@localhost etc]# isql -v test

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

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

SQL> show tables;

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

| Tables_in_test                                                  |

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

| t1                                                              |

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

SQLRowCount returns 1

1 rows fetched

SQL>

能看到表说明通过odbc访问gbase成功！

4:配置ORACLE透明网关与ODBC数据源的连接

vi /home/oracle/app/oracle/product/11.2.0/db_1/hs/admin/inittest.ora

# HS init parameters

#

HS_FDS_CONNECT_INFO=test

HS_FDS_TRACE_LEVEL=0

HS_FDS_SHAREABLE_NAME=/usr/lib64/libgsodbc8.so

set ODBCINI=/usr/local/etc/odbc.ini

set LD_LIBRARY_PATH=/usr/lib64

5:配置ORACLE监听

vi /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /home/oracle/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.localdomain)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /home/oracle/app

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/db_1)

(SID_NAME = test )

(PROGRAM = dg4odbc)

(ENVS = "LD_LIBRARY_PATH=/usr/lib64:/usr/lib:$ORACLE_HOME/lib")

)

)

配置完成后重新生效监听

lsnrctl reload

6:配置ORACLE的TNS

vi /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.localdomain)

)

)

TEST  =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = test )

)

(HS = OK)

)

配置完成后，测试tns效果

tnsping test

7:建立DBlink并做查询测试

#建立oracle到gbase连接通道

SQL> create database link gbase connect to "root" identified by "1" using 'test';

Database link created.

SQL> select * from t1@gbase;

id

----------

1

2

3

1

2

3

1

2

3

9 rows selected.