南大通用GBase 8s到Oracle DBLink配置详解

在构建企业级数据解决方案时，连接GBase 8s与Oracle数据库是常见的需求。在上一篇文章中我们安装了软件并介绍了Oracle到南大通用GBase 8s DBLink配置，本文将指导您如何配置GBase 8s到Oracle的DBLink，实现两个数据库系统间的互操作性。

1、GBase Gateway安装

1）配置/etc/services

vi /etc/services
gbase350_O2_3  9100/tcp

2）解压安装包

tar -xvf GBaseGateway_1.0.0_1.tar.gz

3）修改配置文件

cd GBaseGateway_1.0.0_1/conf/gbase8s
vi 实例名.properties
[gbasedbt]
gbase8s_IP=172.24.5.103
gbase8s_port=9100
gbase8s_user=gbasedbt
gbase8s_pwd=111111
gbase8s_encode=DB_LOCALE=zh_CN.GB18030-2000;CLIENT_LOCALE=zh_CN.GB18030-2000

透明网关参数配置文件：GBaseGateway_1.0.0_1/conf/conf.properties

用默认即可，默认端口 9898

4）启动透明网关

sh gbaseGatewayServer.sh start

5）增加透明网关连接信息加到数据库 sqlhosts

dblink_gateway  onsoctcp      172.24.5.103       9898

2、创建dblink并测试

set environment sqlmode 'oracle';
drop database link dblinktest2;
create public database link dblinktest2 connect to  'oracle' with system identified BY 'PBData#2014' 
USING '(
       DESCRIPTION = (
              ADDRESS=(PROTOCOL = TCP)
                            (HOST=172.24.5.99)
                            (PORT=1521)
                            )
              (CONNECT_DATA =
                            (SERVER=DEDICATED)
                            (SERVER=DEDICATED)
                            (SERVIC_NAME=db12c)
              )
              )';

select * from dblink_tab1@dblinktest2;

注意：如果报oracle

java.sql.SQLException: Non supported character set (add orai18n.jar in your classpath): ZHS16GBK

将oracle安装目录下的orai18n.jar拷贝到gateway的lib下，重启。

2、oracle到cm DBLink配置(原理与oracle到8s相同）

1）在odbc.ini中添加cm数据源(实例名更换为cm_update)

[cm_demo]
Driver=/opt/gbase/lib/cli/iclit09b.so
Description=GBase ODBC DRIVER
Database=gbasedb
LogonID=gbasedbt
pwd=GBase123
Servername=cm_update
CLIENT_LOCALE=zh_cn.utf8
DB_LOCALE=zh_cn.utf8

2）配置sqlhosts文件

db_group  group          -             -        i=1
gbase01   onsoctcp       172.16.3.45   9088    g=db_group
gbase02   onsoctcp       172.16.3.46   9088    g=db_group

cm_update group          -             -        i=3,c=0
oltp1     onsoctcp       172.16.3.45   18888   g=cm_update
oltp2     onsoctcp       172.16.3.46   18888   g=cm_update

cm_read   group          -             -        i=4,c=0
read1     onsoctcp       172.16.3.45   19999   g=cm_read
read2     onsoctcp       172.16.3.46   19999   g=cm_read

3）添加关于cm的oracle HS文件

cd $ORACLE_HOME/hs/admin （切到该目录下）
cat <<! >initcm_demo.ora   #init<监听实例名>.ora
HS_FDS_CONNECT_INFO=cm_demo
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_NLS_NCHAR = UCS2
HS_FDS_FETCH_ROWS=1000
HS_RPC_FETCH_REBLOCKING=OFF
set ODBCINI=/etc/odbc.ini
set GBASEDBTDIR=/opt/gbase
set GBASEDBTSERVER=cm_update
set GBASEDBTDIR=/opt/gbase
set GBASEDBTSQLHOSTS=/opt/gbase/etc/sqlhosts
set PATH=/opt/GBASE/gbase/bin:/u01/app/oracle/product/11.2.0/db_1/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin:/usr/lib64/qt-3.3/bin:/home/oracle/perl5/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/home/oracle/bin
set LD_LIBRARY_PATH=/opt/gbase/lib/:/opt/gbase/lib/cli:/opt/gbase/lib/esql:include:/u01/app/oracle/product/11.2.0/db_1/lib
set DELIMIDENT=y
!

4）配置oracle监听

1. 修改linstener.ora文件

cd $ORACLE_HOME/network/admin/
vi listener.ora

添加如下数行，如图所示

(SID_DESC =
     (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = cm_demo)
     (PROGRAM=dg4odbc)
    )

2. 修改tnsnames.ora文件

cd $ORACLE_HOME/network/admin/
vi tnsnames.ora

添加如下数行，如图所示

cm_demo =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.47)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = cm_demo)
    )
   (HS=OK)
  )

5）重启监听

lsnrctl reload
lsnrctl status #显示odbc_demo正常，状态unkonwn

tnsping cm_demo #显示ok即正常

6）创建dblink并测试

su - oracle
sqlplus / as sysdba
SQL> create database link gbasecmlink connect to "gbasedbt" identified by "GBase123" using 'cm_demo';
SQL> select * from test@gbasecmlink;
SQL> insert into test@gbasecmlink values(88);

本文提供了从GBase 8s到Oracle的DBLink配置的详细步骤，帮助数据库专业人员实现两个数据库系统间的数据交互和集成。掌握这些配置技巧，可以有效地支持复杂的数据处理任务和决策分析。