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
SSL Certificates for FREE—nginx AWS Route 53

ssl, aws, nginx, letsencrypt
Image
From Heritage to Tech: DEVFEST Prayagraj 2023's Inaugural

devfest, googledevelopergroup, gdgprayagraj, prayagraj
Image
10 Disadvantages of Using ChatGPT Essay Writer

essaywritingservice, academicsupport, courseworkhelp
Image
The Fastest Llama: Uncovering the Speed of LLMs
Image
Understanding call, apply, and bind in JavaScript with Simple Examples

javascript, beginners, career, learning
Image
Day 3:From Strings to Numbers: Demystifying JavaScript Type Conversions

webdev, javascript, beginners, programming
Image
And the Oscar Goes to … Coding a Chronology Component

css, webdev, showdev, tutorial
Image
Creating safe and fast multiplayer in games on Unity and NodeJS with examples

unity3d, tutorial, node, javascript
Image
How can you Enable Containers in Windows 11?

windows11, tips, beginners, tutorials
Image
Best open-source tools for DevOps and microservices

devops
Image
Deploy a lightweight BI solution with your first dashboard in 5 steps

dashboard, reporting, sql, businessintelligence
Image
Ohuhu Independent Site Traffic Analysis Report
Image
"I want the selection to be random but weighted in favor of higher scores, but that's not happening."
Image
Day 2: Creating a Signup Page with Express.js – Building the Foundation for User Registration

backend, authentication, express
Image
The lucky sort

perl, python, theweeklychallenge
Image
Spring Boot Common Sense: Part 2 Crafting Effective JPA Entities for Robust Data Models

springboot, jpa, bestpractices
Image
Spring Boot Common Sense: Part 1 Laying the Foundation with SQL Databases

database, liquibase, bestpratices, springboot
Image
LLP Registration: Key Advantages and Legal Requirements

llp, trademark, company, javascript
Image
Why you should attend Google Developer’s Group DevFests?

devfest, developers, prayagraj, googledevelopergroup
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player