Oracle Database Gatewayを用いてOCI Database System 19cとMS SQL Server 2016を接続する方法

## 2021/12/15 更新

- 本記事を作成した時にDBCSのデータベースバージョンは19.3でしたから、Oracle Database Gateway for SQL Serverは無事でインストールできましたが、現時点（2021年12月）ではDBCSのデータベースのデフォルトバージョンは19.11になっておりますので、Oracle Database Gateway for SQL Serverをインストールする時にRU 19.11とのパッチコンフリクトの問題でインストールできなくなります。

- Oracle Database Gateway for SQL Server はライセンスとして有償製品かつ(1コンピュータ ¥2,100,000 )、Processorライセンスではなく、Computerライセンスであることを注意してください。詳細は下記2つリンクの内容を参照してください。

    - [Oracle Technology Global Price List](https://www.oracle.com/jp/a/ocom/docs/jpy-tech-localizablepl.pdf)
    - [Oracle Processor Core Factor Table 補足資料](https://www.oracle.com/assets/procr-core-factor-table-suplmnt-jp-3304645-ja.pdf)

## はじめに

Oracle Cloud Infrastructure(以下OCI) では、Oracle DB のマネージドサービス(以下OCI Database System)を提供しています。
この記事は、OCI Database System に Oracle Database Gateway を追加でインストールして、Microsoft の SQL Server 2016 と DBlink を設定して、Oracle DB から透過的に SQL 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)

## OCI Database SystemのVNCサーバーのセットアップ

### 事前確認

Oracle Universal Installerを使用してOracle Database GatewayをOCI Database Systemにインストールするため、最初にOCI Database SystemにVNCサーバーを設定する必要があります。

デフォルトの`opc`ユーザーでOCI Database Systemにログインし、Linuxバージョンを確認します。

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

OCI Database System 19cのベースイメージはOracle Linux 7.5であり、GUI環境はベースイメージに含まれているので、インストールする必要はありません。

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

### VNCサーバーの構成

`oracle`ユーザーに切り替えて、VNCサーバーのパスワードを設定します。

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

VNCサーバーを起動し、VNCサーバーが使用しているポートの状態を確認します。

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

### iptablesとVCN Security Listの構成

#### iptables

デフォルトでは、VNCサーバーのポート590XはOCI Database Systemのiptablesで許可されていないため、次のコマンドを実行してポート`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

また、ポート`5901`が、OCI Database Systemが存在するVCN（Virtual Cloud Network）のセキュリティリストによってブロックされないようにする必要があります。

簡単にするために、このVCNへのすべての入力トラフィックを許可します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/ab1cca12-905b-24f4-cb5d-44651bbb78ca.png)

### VNC Viewerから接続

デスクトップで、TigerVNC ViewerからOCI Database Systemに接続して見ましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/960b7c9a-236b-8bea-e554-401bbcb87dc2.png)

パスワードを入力して[OK]をクリックします

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

以下のように、VNC ViewerでOCI Database Systemのターミナルを表示できるはずです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b8c33cc9-e976-0543-4fb7-2b66b57be232.png)

## Oracle Database Gatewayのインストール

### ダウンロード

Oracle Database Gateway 19cは[こちら](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)

次に、Oracle Database Gateway 19cのインストールパッケージをOCI Database Systemに転送します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/9e0aea48-f366-034a-0759-df3a1d22a032.png)

### インストール

VNC Viewerを開き、Oracle Database Gatewayインストールパッケージを配置した場所に移動して解凍します。

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

解凍したディレクトリ`gateway`に移動して、`./runInstaller`ファイルを実行します。

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

Oracle Universal Installerインターフェイスがポップアップされ、[Next]をクリックしてゲートウェイをOracleホームディレクトリにインストールできます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/36f3e0d5-9441-83c8-177f-b90a1032ffd6.png)

`Oralce Database Gateway for Microsoft SQL Server`を選択し、[Next]をクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/b16dc6ab-22c2-591a-065f-5cfb01f55edc.png)

次に、ターゲットSQL Serverの重要な情報を入力する必要があります。

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

- Host Name: SQL ServerのパブリックIPアドレスまたはホスト名
- Port Number: 1433はSQL Serverのデフォルトのポート番号です。他のポートを使用している場合は、変更してください
- Server Name: SQL Serverのインスタンス名。以下に示すように、SQL Server Configuration Managerから取得できます
- Database Name: 接続したいデータベース名

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

概要を確認し、[Install]をクリックします
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/3a3da651-2df4-07a1-6928-c109c6e925fb.png)

`root`ユーザーとしてスクリプトを実行します
![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)

スクリプトを実行し、VNC Viewerに戻って[OK]をクリックすると、成功のメッセージが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/6b064017-947c-432d-6acd-1a8226d93bad.png)

これまで、Oracle Database Gateway 19cがインストールされています。

## Oracle Database Gatewayの構成

### 事前確認

OCI Database System にログインし、`oracle`ユーザーに切り替えます。
次のディレクトリに移動し、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)

すべてが適切に見え、必要なすべてのSQL Server情報がここに反映されます。
ファイル `tnsnames.ora.sample`と` listener.ora.sample`を開き、その内容を使用して設定を行います。

### `tnsnames.ora`の更新

ユーザー`oracle`として作業していることを確認してください。 ディレクトリ`$ORACLE_HOME/network/admin`に移動し、`tnsnames.ora`ファイルを更新します。 `tnsnames.ora.sample`からコンテンツをコピーし、`tnsnames.ora`に貼り付けます。**HOST**をhostanmeに変更することを忘れないでください。

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

### `listener.ora`の更新

OCI Database SystemはシングルインスタンスRACとして構築されており、ユーザー`grid`はリスナーの所有者なので、ユーザー`grid`に切り替える必要があります。 次に、`listener.ora.sample`からコンテンツ(SID_LIST_LISTENERの部分)をコピーして、`listener.ora`に貼り付けます。
以下のスクリーンショットを確認してください。

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

リスナーをリロードして、ステータスを確認します。 リスナー `dg4msql`が登録され、正常に機能していることがわかります。

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

ここまで実行することで、SQL Serverに接続するためのOracle Database Gatewayの構成は完了です。
次に、動作確認を行います。

## Oracle Database Gatewayの検証

ユーザー`oracle`としてOCI Database Systemにログインし、`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;
```

データベースリンクはデータベース内のオブジェクトとして作成されるため、明示的に削除しない限り使用することが出来ます。再起動しても同様です。
上記のサンプルクエリでは、`trex`はSQL Serverデータベース`xucheng`のテーブル名を表しています。また、サンプルクエリの`xucheng`は、作成したデータベースリンクです。 このデータベースリンクを使用して、SQL Serverのデータベースに接続します。 したがって、基本的な使用法は`select * from <TARGET_TABLE_NAME>@<DB_LINK>`なります

OCI Database Systemでは、デフォルトで`global_names`パラメータが`TURE`に設定されているため、`ORA-02085`エラーが発生します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/dde9c6b4-77df-031f-7413-101f876fdd64.png)

global_namesをFALSEに設定して、セッションレベルで`ORA-02085`の問題を解決するか、システムレベルでこのパラメーターを変更することもできます。

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

以下のスクリーンショットでわかるように、SQL Serverと同じクエリ結果を取得できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100411/bf63f7b3-e89e-311b-937d-527418bc82bd.png)

これで、OCI Database System 19cインスタンスからSQL Serverデータベース連携することが出来ました。

## 参照資料

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