Detailed Guide to Configuring DBLink from GBase 8s to Oracle
Tags: database
Published: 2024-07-11 03:51:14

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`

```sql
vi /etc/services
gbase350_O2_3  9100/tcp
```

### 2) Extract Installation Package

```sql
tar -xvf GBaseGateway_1.0.0_1.tar.gz
```

### 3) Modify Configuration Files

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

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
sh gbaseGatewayServer.sh start
```

### 5) Add Transparent Gateway Connection Information to SQLHOSTS

```sh
dblink_gateway  onsoctcp      172.24.5.103       9898
```

## 2. Create DBLink and Test

```sql
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;
```

**Note:** If you encounter the following Oracle error:
```sql
java.sql.SQLException: Non supported character set (add orai18n.jar in your classpath): ZHS16GBK
```

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

```ini
[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
```

### 2) Configure `sqlhosts` File

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

### 3) Add Oracle HS File for CM

```sh
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
!
```

### 4) Configure Oracle Listener

- Modify the `listener.ora` file:

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

Add the following lines:
```sh
(SID_DESC =
    (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME = cm_demo)
    (PROGRAM=dg4odbc)
)
```

- Modify the `tnsnames.ora` file:

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

Add the following lines:
```sh
cm_demo =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.47)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SID = cm_demo)
        )
        (HS=OK)
    )
```

### 5) Restart the Listener

```sh
lsnrctl reload
lsnrctl status # Should display odbc_demo as normal, state unknown
tnsping cm_demo # Should display OK if normal
```

### 6) Create DBLink and Test

```sql
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);
```

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.