Oracle to GBase 8s DBLink Configuration Guide
Tags: database
Published: 2024-07-10 09:15:36

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

```bash
yum install unixODBC
```

### 2. Install gbasecsdk on Oracle

```bash
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)

```bash
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

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

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

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

### 6. Test ODBC

```bash
isql odbc_demo   # Displays "connect!" if successful
```

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

```bash
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

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

Add the following lines:

```plaintext
# 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

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

Add the following lines:

```plaintext
# 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)

```bash
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)

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

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

```bash
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.