In a heterogeneous database environment, establishing a seamless connection between Oracle and GBase 8s is a critical task. DBLink provides an efficient way to connect and operate these two systems. This article will detail how to configure DBLink in an Oracle environment to connect to a GBase 8s database.

Software Version Information

GBase 8s: GBase8sV8.8_AEE_3.5.0_3NW1_6_86443b
Oracle: 11g

Steps to Configure Oracle to GBase 8s DBLink

1. Install unixODBC on Oracle

yum install unixODBC

2. Install gbasecsdk on Oracle

tar -xvf clientsdk_3.5.0_3NW1_6_86443b_RHEL6_x86_64.tar 
./installclientsdk -i silent -DLICENSE_ACCEPTED=TRUE -DUSER_INSTALL_DIR=/opt/gbase

3. Configure the ODBC configuration file (execute as root on Oracle)

cat <<! >/etc/odbc.ini
[ODBC]
UNICODE=UCS-2
[odbc_demo]
Driver=/opt/gbase/lib/cli/iclit09b.so
Description=GBase ODBC DRIVER
Database=gbasedb
LogonID=gbasedbt
pwd=GBase123
Servername=gbase1
CLIENT_LOCALE=zh_cn.utf8
DB_LOCALE=zh_cn.utf8
TRANSLATIONDLL=/opt/gbase/lib/esql/igo4a304.so
!

4. Configure environment variables

export ODBCINI=/etc/odbc.ini 
export GBASEDBTDIR=/opt/gbase

5. Configure the database connection sqlhosts file (execute as root on Oracle)

cat <<! >$GBASEDBTDIR/etc/sqlhosts
gbase01 onsoctcp 172.16.3.47 9088
!

6. Test ODBC

isql odbc_demo   # Displays "connect!" if successful

7. Configure Oracle HS configuration file (execute as oracle user on Oracle)

cd $ORACLE_HOME/hs/admin
cat <<! >initodbc_demo.ora   # init<listener_instance_name>.ora
HS_FDS_CONNECT_INFO=odbc_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=gbase01
set GBASEDBTDIR=/opt/gbase
set GBASEDBTSQLHOSTS=/opt/gbase/etc/sqlhosts
set PATH=/opt/GBASE/gbase/bin:$PATH
set LD_LIBRARY_PATH=$GBASEDBTDIR/lib/:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:include:$LD_LIBRARY_PATH
set DELIMIDENT=y
!

8. Configure Oracle listener (execute as oracle user on Oracle)

1. Modify `listener.ora` file

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

Add the following lines:

# add for gbase8s start
(SID_DESC =
     (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = odbc_demo)
     (PROGRAM=dg4odbc)
    )
# add for gbase8s end

2. Modify `tnsnames.ora` file

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

Add the following lines:

# add for dg4odbc used by gbase8s start
odbc_demo =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.47)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = odbc_demo)
    )
   (HS=OK)
  )
# add for dg4odbc used by gbase8s end

9. Restart the listener (execute as oracle user on Oracle)

lsnrctl reload
lsnrctl status  # Shows "odbc_demo" as normal, status unknown
tnsping odbc_demo  # Shows "OK" if successful

10. Create a test table (execute as gbasedbt user on GBase 8s)

export DELIMIDENT=y
dbaccess gbasedb -<<!
create table "TEST"(a int);
!

11. Create DBLink and test (execute as oracle user on Oracle)

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

12. Notes

The DELIMIDENT=y setting must be enabled on the GBase 8s side to distinguish case sensitivity in quoted identifiers.
When operating in Oracle, table names are converted to uppercase and enclosed in quotes, such as "SYSTABLES".
Column names are created as lowercase by default if not enclosed in double quotes. Oracle operations should reference them as lowercase or omit field names.
dg4odbc does not support DDL operations.

Following these steps, Oracle database administrators and developers can successfully configure DBLink to achieve efficient data interaction with GBase 8s. This provides robust support for cross-database queries and data synchronization.

Oracle to GBase 8s DBLink Configuration Guide