Detailed Guide to Configuring DBLink from GBase 8s to Oracle

Connecting GBase 8s with Oracle databases is a common requirement when building enterprise-level data solutions. In the previous article, we covered the configuration of DBLink from Oracle to GBase 8s. This article will guide you through configuring DBLink from GBase 8s to Oracle, enabling interoperability between the two database systems.

1. Installing GBase Gateway

1) Configure /etc/services

vi /etc/services
gbase350_O2_3  9100/tcp

2) Extract Installation Package

tar -xvf GBaseGateway_1.0.0_1.tar.gz

3) Modify Configuration Files

cd GBaseGateway_1.0.0_1/conf/gbase8s
vi instance_name.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

The transparent gateway configuration file (GBaseGateway_1.0.0_1/conf/conf.properties) can be left as default, with the default port being 9898.

4) Start the Transparent Gateway

sh gbaseGatewayServer.sh start

5) Add Transparent Gateway Connection Information to SQLHOSTS

dblink_gateway  onsoctcp      172.24.5.103       9898

2. Create DBLink and Test

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;

Note: If you encounter the following Oracle error:

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

Copy orai18n.jar from the Oracle installation directory to the gateway's lib directory and restart the gateway.

3. Configuring DBLink from Oracle to CM (Similar to Oracle to GBase 8s)

1) Add CM Data Source to odbc.ini (change instance name to 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) Configure sqlhosts File

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) Add Oracle HS File for CM

cd $ORACLE_HOME/hs/admin (Navigate to the HS admin directory)
cat <<! >initcm_demo.ora   #init<monitor instance name>.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) Configure Oracle Listener

• Modify the listener.ora file:

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

Add the following lines:

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

• Modify the tnsnames.ora file:

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

Add the following lines:

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

5) Restart the Listener

lsnrctl reload
lsnrctl status # Should display odbc_demo as normal, state unknown
tnsping cm_demo # Should display OK if normal

6) Create DBLink and Test

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);

This article provides detailed steps for configuring DBLink from GBase 8s to Oracle, enabling database professionals to achieve data interaction and integration between the two systems. Mastering these configuration techniques can effectively support complex data processing tasks and decision-making analysis.