Oracle通過dblink連接GBase 8a MPP Cluster

Oracle 通過 dblink 連接 GBase 8a MPP Cluster

源端：

Oracle 版本： Oracle 11.2.0.3.0    OS 版本： Redhat 7.5  IP:192.168.38.20

目標端：

GBase 版本： GBase 8a Cluster    OS 版本： Redhat 7.5  IP:192.168.38.10

一 Oracle 端安裝 gateway

說明：Oracle 11.2.0.3.0 默認安裝了 gateway

[oracle@cjcos02 hs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/hs

查看gateway 信息

[oracle@cjcos02 hs]$ dg4odbc

Oracle Corporation --- SATURDAY  MAY 09 2020 15:07:10.503

Heterogeneous Agent Release 11.2.0.3.0 - 64bit Production  Built with

   Oracle Database Gateway for ODBC

二 Oracle 端安裝 GBase ODBC

GBase ODBC 下載地址：

http://www.gbase8a.com/forum.php?mod=viewthread&tdtype=document&tid=1319

安裝：

[root@cjcos02 odbc]# rpm -ivh GBaseODBC-8.3.81.53-build53.17-redhat7-x86_64.rpm

Preparing...                          ################################# [100%]

Updating / installing...

   1:gbaseodbc-8.3-53.17              ################################# [100%]

三 Oracle 端配置 odbcinst.in

[root@cjcos02 odbc]# vim /etc/odbcinst.ini

[GBase ODBC 8.3 Driver]

Driver=/usr/lib64/libgsodbc8.so

UsageCount=1

DontDLClose=1

Threading=0

四 Oracle 端配置 GBase ODBC 數據源

4.1 查看配置文件位置

[root@cjcos02 odbc]# odbcinst -j

unixODBC 2.3.1

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

4.2 配置 odbc.in

[root@cjcos02 odbc]# vim /etc/odbc.ini

[gbase]

Description     = ODBC for GBase

Driver          = GBase ODBC 8.3 Driver

Server          = 192.168.38.10

Port            = 5258

UID             = cjc

Password        = cjc

Database        = cjcdb

五 驗證ODBC 連接

[root@cjcos02 odbc]# isql gbase

SQL> select version();

SQL> select * from t1;

六 配置 tnsnames.ora

[oracle@cjcos02 ~]$ cd $ORACLE_HOME/network/admin

[oracle@cjcos02 admin]$ vim tnsnames.ora 

[oracle@cjcos02 admin]$ tnsping gbase

七   配置listener.ora

[oracle@cjcos02 admin]$ vim listener.ora

[oracle@cjcos02 admin]$ lsnrctl reload

[oracle@cjcos02 admin]$ lsnrctl status

八 配置 initgbase.ora

[oracle@cjcos02 admin]$ pwd

/u01/app/oracle/product/11.2.0/db_1/hs/admin

[oracle@cjcos02 admin]$ vim initgbase.ora

HS_FDS_CONNECT_INFO = gbase

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

set ODBCINI = /etc/odbc.ini

九   創建dblink

SQL> create public database link gbase connect to "cjc" identified by "cjc" using 'gbase';

Database link created.

十 驗證數據

Oracle 端：

[oracle@cjcos02 ~]$ sqlplus / as sysdba

SQL> select * from t1@gbase;

Gbase 端：

[gbase@cjcos01 ~]$ gccli -ucjc -p

gbase> use cjcdb;

gbase> select * from t1;

十一：常見錯誤

如下報錯一般都是 initgbase.ora 配置問題，添加 HS_FDS_SHAREABLE_NAME 參數即可。

SQL> select * from t1@gbase;

select * from t1@gbase

                 *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from GBASE

Oracle 停庫時報錯 ORA-01097 ，切換會話后之前的會話會自動提交或回滾，在執行停庫命令。

SQL> shutdown immediate  

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

切換會話

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！