DEV Community

Cong Li
Cong Li

Posted on

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
Enter fullscreen mode Exit fullscreen mode

2) Extract Installation Package

tar -xvf GBaseGateway_1.0.0_1.tar.gz
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

5) Add Transparent Gateway Connection Information to SQLHOSTS

dblink_gateway  onsoctcp      172.24.5.103       9898
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Note: If you encounter the following Oracle error:

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

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
!
Enter fullscreen mode Exit fullscreen mode

4) Configure Oracle Listener

  • Modify the listener.ora file:
cd $ORACLE_HOME/network/admin/
vi listener.ora
Enter fullscreen mode Exit fullscreen mode

Add the following lines:

(SID_DESC =
    (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME = cm_demo)
    (PROGRAM=dg4odbc)
)
Enter fullscreen mode Exit fullscreen mode
  • Modify the tnsnames.ora file:
cd $ORACLE_HOME/network/admin/
vi tnsnames.ora
Enter fullscreen mode Exit fullscreen mode

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)
    )
Enter fullscreen mode Exit fullscreen mode

5) Restart the Listener

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

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);
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)