Detailed Guide to Configuring DBLink from GBase 8s to Oracle

Cong Li - Jul 11 - - Dev Community

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.

Image
Design & Implementation: Project 2

searchlist, buckets, depaul, github
Image
HTTP & content_length

http, django, webdev
Image
STB60NF06L
Image
STB36NF06LT4
Image
How to Fix Type Compatibility Issues Between Next.js Link and Material-UI MenuItem

nextjs, typescript, react, frontend
Image
Debouncing and Throttling

webdev, javascript, beginners, programming
Image
STB20NK50ZT4
Image
Use Cases of the CORE Framework

scrum, devops, softwaredevelopment, softwareengineering
Image
5 Lessons You Can Learn From ADHD Assessment
Image
20ETS08S
Image
FDU8880
Image
HUF76437S3S
Image
Cat Flap Installation Cost Near Me
Image
FQB6N80
Image
FDB3632
Image
2SK2071-01S
Image
Is Tech Making Lightweight Self Propelled Folding Wheelchair Better Or Worse?
Image
Understanding SSL, Encryption, and Their Importance in Web Applications
Image
NTD6600N
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player