Using Oracle Database Gateway to connect OCI Database system 19c and MS SQL Server 2016

## Background

Sometimes, we have heterogeneous database services in our system, we expect to transparently access data residing in a non-Oracle system from an Oracle environment. In this post, we will discuss how to use the Oracle Database Gateway to provide the ability to make this done.

Exactly speaking, we will install **Oracle Database Gateway 19c** on an **OCI Database System 19c** and make a connection to an existing **Microsoft SQL Server 2016**.

## Prerequisites

Assuming we already have the following service/server. 

- OCI Database System 19c
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/35fb0e3d-66ae-0032-e44d-67ccc366a11a.png)

- MS SQL Server 2016
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/4f9b8e83-a854-80c5-f359-78aafa3d2a2f.png)

## Setup VNC server in OCI Database System

### Pre-check

We are going to use Oracle Universal Installer to install the Oracle Database Gateway into the OCI Database System, so we need to setup VNC server on the OCI Database System first. 

Login to the OCI Database System with default `opc` user and check the Linux version. 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/612088f0-9846-480d-53fa-98f3c32c25ed.png)

Since the base image of OCI Database System 19c is Oracle Linux 7.5, and `Server with GUI` package is already installed. So we do not need to install it. 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/3052f9b3-9856-2e86-0ce0-dfbdd6c140b9.png)

### Configure VNC server

Switch to `oracle` user and setup password for the VNC server.

```bash
su - oracle
vncpasswd
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/29097d6b-0bbe-dccf-a0ed-0fccce63aab1.png)

Start the VNC server and check the port number which VNC server is using.

```bash
vncserver
vncserver -list
netstat -ltp4 | grep 590
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/19faa33c-9b34-ac3c-b2bb-8cd71f8efec6.png)

### Configure iptables and VCN security list

#### iptables

By default, VNC server port 590X is not allowed by the OCI Database System iptables, so we need to perform following commands to open the port `5901`. 

```bash
# check the iptables
iptables -L
# allow the inbound of port 5901
iptables -I INPUT 6 -p tcp -m state --state NEW -m tcp --dport 5901 -j ACCEPT
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/5a0ed9e5-12bc-10b5-3846-6a238e0370c7.png)

#### VCN Security List

We also need to ensure the port number `5901` is not blocked by the security list of the VCN(Virtual Cloud Network) which the OCI Database System resides in. 

For simplicity, I allow all the ingress traffic to this VCN. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/ab1cca12-905b-24f4-cb5d-44651bbb78ca.png)

### Connect from VNC Viewer

On your desktop, try to connect to the OCI Database System from the TigerVNC Viewer
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/960b7c9a-236b-8bea-e554-401bbcb87dc2.png)

Input the password and click OK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/1df509f3-02ed-42da-713d-f376567a559d.png)

You should be able to see the OCI Database System terminal in the VNC Viewer as below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b8c33cc9-e976-0543-4fb7-2b66b57be232.png)

## Install Oracle Database Gateway

### Download

You can download Oracle Database Gateway 19c from [here](https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html). 
https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/c9156856-806d-f4a5-e431-ee5bebba302e.png)

Then transfer the Oracle Database Gateway 19c installation package to the OCI Database System. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/9e0aea48-f366-034a-0759-df3a1d22a032.png)

### Install

Open VNC Viewer, move to where you placed the Oracle Database Gateway installation package and unzip it. 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/218083ae-4161-37d5-ba80-b8102ef871a5.png)

Go the unzipped directory `gateway` and execute the `./runInstaller` file

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/71758570-856d-dedb-b924-2dee8c7e3a3f.png)

Oracle Universal Installer interface will pop up and we can click `Next` to install gateway to the default Oracle home directory
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/36f3e0d5-9441-83c8-177f-b90a1032ffd6.png)

Select `Oralce Database Gateway for Microsoft SQL Server` and click Next.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b16dc6ab-22c2-591a-065f-5cfb01f55edc.png)

Now we need to input some critical information of the target SQL Server.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/3cac1959-1c26-1650-d6b2-51b0cc599365.png)

- Host Name: public IP address of the SQL Server or the hostname
- Port Number: 1433 is the default port number of SQL Server, if yours is using any other port, just change it
- Server Name: the instance name of the SQL Server, you can get it from SQL Server Configuration Manager as shown below
- Database Name: the database name you want to connect to

SQL Server instance name
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b181825c-4f54-3bdd-fe5b-47214310225f.png)

Review the summary and click Install 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/3a3da651-2df4-07a1-6928-c109c6e925fb.png)

Execute the script as `root` user
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/aecb12a0-6a3f-8d69-e069-be47b1ddd056.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/d0593e80-d336-a622-7b46-8870cf69c4c2.png)

Once you executed the script, back to VNC Viewer and click OK, you will see the successful message.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/6b064017-947c-432d-6acd-1a8226d93bad.png)

Until now, the Oracle Database Gateway 19c is installed.

## Configure the Oracle Database Gateway

### Pre-check

Login to the OCI Database System and switch to `oracle` user.
Go to following directory and start to configure the Oracle Database Gateway.

```bash
# go to the directory
cd $ORACLE_HOME/dg4msql/admin
# have a look at the gateway initialization file
cat initdg4msql.ora
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/22c81b2c-d1b4-6ece-40f5-b9fd24f34dc4.png)

Everything looks good, all the required SQL Server information are reflected here.
Open file `tnsnames.ora.sample` and `listener.ora.sample`, we will use the contents to make the configuration. 

### Configure the `tnsnames.ora`

Make sure you are working as user `oracle`. Go to the directory `$ORACLE_HOME/network/admin` and update the `tnsnames.ora` file. Copy the content from `tnsnames.ora.sample` and paste it into `tnsnames.ora`, remember to change the **HOST** to be your hostanme.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/549aa391-c3d8-8b7a-bced-5a4f19bdc33a.png)

### Configure the `listener.ora`

OCI Database System is built as a single instance RAC, user `grid` is the owner of the listener, so we need to switch to user `grid`. Then copy the content(only the SID_LIST_LISTENER part) from `listener.ora.sample` and paste it into `listener.ora`. 
Please check below screenshot. 

```bash
# switch to user grid
sudo su - grid
# move to the listener directory
cd $ORACLE_HOME/network/admin
vim listener.ora
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/90a5bfe5-65bc-d647-0929-593dc57d84f7.png)

Reload the listeners and check the status. We can see that the listener `dg4msql` is registered and working well.

```bash
lsnrctl reload
lsnrctl status
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/40beca41-272c-2e67-3f60-bee9e6c4153c.png)

Until now, the configuration of the Oracle Database Gateway to connect to a specific SQL Server is completed. Next, let's verify how it works.

## Verify the Oracle Database Gateway

Login to the OCI Database System as user `oracle` and connect to the database as `sys`.

```bash
sudo su - oracle
sqlplus / as sysdba
```

```sql
-- change to pdb
alter session set container=pdb1;
-- create database link to the SQL Server
create public database link xucheng connect to "SQL_SERVER_USER" identified by "SQL_SERVER_PASSWORD" using 'dg4msql';
-- query table [trex] through the database link [xucheng]
select * from trex@xucheng;
```
The database link is created as an object in the database, so you can use it any time unless you drop it. 
In the sample query above, `trex` is a table in SQL Server database `xucheng`. And the `xucheng` in the sample query is the database link we just created. We use this database link to connect to the database in SQL Server. So the basic usage is `select * from <TARGET_TABLE_NAME>@<DB_LINK>`

In OCI Database System the `global_names` parameter is set to `TURE` by default, so you will get the `ORA-02085` error. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/dde9c6b4-77df-031f-7413-101f876fdd64.png)

You can set the global_names to FALSE to overcome the `ORA-02085` issue at session level, or you can also alter this parameter at the system level. 

```sql
ALTER SESSION SET global_names = FALSE;
select * from trex@xucheng;
```

As you can see in below screenshot, we can get the same query result as in SQL Server. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/bf63f7b3-e89e-311b-937d-527418bc82bd.png)

Now, we can query the SQL Server database from OCI Database System 19c instance. 

## References

- [Installing Oracle Database Gateway for SQL Server](https://docs.oracle.com/en/database/oracle/oracle-database/19/otgis/install-sqlserver-gateway.html#GUID-19E6DECC-F415-4458-BA80-36867B1D1F85)
- [Introduction to the Oracle Database Gateway for SQL Server](https://docs.oracle.com/en/database/oracle/oracle-database/19/gmswn/database-gateway-sqlserver-introduction.html#GUID-21C2779B-C002-45E3-AD40-8A8FF47B25AC)
- [SQL Server Gateway Features and Restriction](https://docs.oracle.com/en/database/oracle/oracle-database/19/gmswn/database-gateway-sqlserver-features.html#GUID-7715EE2E-35B9-4B04-90F3-28A85476E638)
- [OCI Security Lists](https://docs.cloud.oracle.com/iaas/Content/Network/Concepts/securitylists.htm)
- [How to Resolve ORA-02085: database link SOURCE_LINK connects to SOURCE_DATABASE](http://logic.edchen.org/how-to-resolve-ora-02085-database-link-source_link-connects-to-source_database/)
- [CREATE DATABASE LINK](https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205)