Oracle DatabaseからNon-OracleDB(MySQL)へのDatabase Gateway for ODBCを用いたDBLINK接続

#■この記事で記載すること

Oracle DatabaseからNon-OracleDBへのDatabase Gateway for ODBCを用いたDBLINK接続に関連する以下内容の自分用メモを記載します。

- Database Gateway for ODBC(DG4O)のセットアップ
- Database Cloud Service(DBCS)からNon-OracleDB（MySQL）へのDBLINK接続
- Autonomous Database(ADB)からNon-OracleDB（MySQL）へのDBLINK接続（DG4ODBCとNon-OracleDBを同一ノードに配置）
- ADBからNon-OracleDB（MySQL Database Service : MDS）へのDBLINK接続（DG4ODBCとNon-OracleDBを別ノードに配置）

DG4Oに関する設定を除くと、ADBのDBLINK設定や、TCPSを用いたDBLINK設定の基本的なフロー、以下の記事に記載した内容とほぼほぼ同じす。
- [DBLINK基本接続構成（TCPS接続）](https://qiita.com/TakuyaAma/items/c217a8445e99f3059214#-dblink%E5%9F%BA%E6%9C%AC%E6%8E%A5%E7%B6%9A%E6%A7%8B%E6%88%90tcps%E6%8E%A5%E7%B6%9A)
- [Autonomous DatabaseとDBCS間のDBLINK（Private IPを利用）の自分用メモ](https://qiita.com/TakuyaAma/items/e7a8aee81c90274edca6)

#■ Database Gateway for ODBC(DG4O)のセットアップ

各構成に共通で必要となるDG4Oをセットアップする

##● システム構成イメージ

Oracle（ローカル） -- [Oracle Net] --> DG4O --[ODBC]--> Non-Oracle（リモート）

##● システム構成説明

- DG4Oはローカル側、リモート側、独立したサーバのいずれにセットアップすることも可能
- DG4Oには、以下のツールをセットアップ、設定
 - Oracle Net
 - Database Gateway For ODBC
 - ODBC Driver Managers
 - Non-Oracle[ここではMySQL]のDBにSpecificなODBC設定

##● 設定例

- 前提
- ADBからDG4OにSSL/TCPS接続するためのWalletを作成/デプロイする
- MySQL ODBCドライバーのインストール
- Oracle Netの設定
- MySQL接続用ODBCの設定
- DG4Oのインストール

###- 前提

- Oracle Cloud Infrastructure（OCI）環境での動作を前提とし、ネットワーク環境（VCN/Subnet/Internet GW）がセットアップされている
- DG4Oサーバ（[Oracle Cloud Developer Image](https://qiita.com/shakiyam/items/6ba70516d2d78ed83664)）用のComputeがデプロイされている
- DG4OサーバのVNC設定が完了している

###- ADBからDG4OにSSL/TCPS接続するためのWalletを作成/デプロイする

[この記事](https://qiita.com/TakuyaAma/items/3b9f9a48b3b1ef9b2e8d#oracle%E3%82%A6%E3%82%A9%E3%83%AC%E3%83%83%E3%83%88%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E4%BD%9C%E6%88%90)を参考にウォレットファイルを作成のうえ、以下に配置する。

・クライアントウォレットファイル
DBLINKローカル側がDBCSの場合：DBCSの`/u01/client/wallet/`に配置
DBLINKローカル側がADBの場合：Object Storageに配置

・サーバウォレットファイル
DG4Oの`/u01/server/wallet/`に配置

###- MySQL ODBCドライバーのインストール

MySQLをインストール

MySQL Serverインストール

```bash
$ sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install mysql-community-server
$ sudo systemctl start mysqld
$ sudo systemctl enable mysqld
$ sudo cat /var/log/mysqld.log | grep password
$ mysql_secure_installation
```

MySQL Clientインストール
※ MySQL Serverインストールで合わせてClinetもインストールされる
※ MySQL Serverとは異なるサーバにDG4Oを入れる場合のみ必要

```bash
$ sudo yum install mysql-community-client
```

初期設定＆確認
※ 参考情報/MySQLを参照


###- MySQL接続用ODBCの設定


MySQL ODBC（mysql-connectors & unixODBC）のインストール

```bash
$ sudo yum install -y mysql-connector-odbc

（以下抜粋）
=======================================================================================================================================
 Package                            Arch                 Version                        Repository                                Size
=======================================================================================================================================
Installing:
 mysql-connector-odbc               x86_64               8.0.23-1.el7                   mysql-connectors-community               4.1 M
Installing for dependencies:
 unixODBC                           x86_64               2.3.1-14.0.1.el7               ol7_latest                               413 k

```

確認

```bash
$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
```

/etc/odbc.iniを以下のように編集

```bash
[ODBC Data Sources]
mds          = MyODBC 8.0 UNICODE Driver DSN
dg4odbc      = MySQL ODBC 8.0 UNICODE Driver DSN

[DG4ODBC]
Driver       = /usr/lib64/libmyodbc8w.so
Description  = Connector/ODBC 8.0 UNICODE Driver DSN
SERVER       = localhost
PORT         = 3306
USER         = root
Password     = WelCome123#123#
Database     = employees
OPTION       = 3
CHARSET      = utf8

```

ODBC経由でのMySQL接続確認

```sql
$ isql dg4odbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_employees                                             |
+-----------------------------------------------------------------+
| current_dept_emp                                                |
| departments                                                     |
| dept_emp                                                        |
| dept_emp_latest_date                                            |
| dept_manager                                                    |
| employees                                                       |
| salaries                                                        |
| titles                                                          |
+-----------------------------------------------------------------+
SQLRowCount returns 8
8 rows fetched
SQL> select count(*) from employees;
+---------------------+
| count(*)            |
+---------------------+
| 300024              |
+---------------------+
SQLRowCount returns 1
1 rows fetched
```

###- DG4Oのインストール

DG4OサーバにoracleでVNC接続のうえ、[Oracle Databaseダウンロードサイト](https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html)からDatabase Gateway for ODBCをダウンロード

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/8b83e32b-2274-1f8e-3311-6775eb9e5069.png)

- Oracle Universal Installerを用いてDG4Oを設定

インストール前のおまじない

```bash
$ sudo yum install oracle-database-preinstall-19c.x86_64
```

- ダウンロードしたDatabase Gateway for ODBCインストーラーを実行

```bash
$ cd Downloads/
$ unzip LINUX.X64_193000_gateways.zip
$ cd gateways/
$ ./runInstaller
```

インストーラのステップに沿って以下のとおり実行

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/9d367b86-27f7-3a30-4c14-db813b75b57d.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/b524c400-7572-6cf0-a292-2495711d2a4f.png)
Oracle Database Gateway for ODBCを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/0c6a70ed-b577-b848-2f06-040b162820d0.png)

あとはデフォルト構成でOKなので省略

- NETCAを用いてネットワークを設定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/a00c318b-8032-00f2-25d2-7c415c19dd97.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/36651051-66e0-2a23-5c0e-502015de201f.png)

ADBとのTCPS接続用にTCPSを追加

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/a6ca9650-3526-12ae-4e91-c53f38cf87ce.png)

TCPはデフォルトを利用

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/065a105b-79fc-5c39-d1a5-223d43331216.png)

TCPSは1522を利用
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7c6da5b4-1c99-b511-3a9f-b7b5188756c1.png)

完了
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3dcadf4c-94a3-0b21-62fb-8b46af98ddcc.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/8ab20d8a-431a-a9dc-6524-fc767b759994.png)
rootで以下を実行

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c40a5289-42cb-13d3-d21e-52d64c2436fa.png)


```bash
# /home/oracle/app/oraInventory/orainstRoot.sh
Changing permissions of /home/oracle/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /home/oracle/app/oraInventory to oinstall.
The execution of the script is complete.

# /home/oracle/app/oracle/product/19.0.0/tghome_1/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /home/oracle/app/oracle/product/19.0.0/tghome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
```

###- Oracle Netの設定

- listener.oraの設定

```bash
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/19.0.0/tghome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
       (SID_NAME=dg4odbc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/19.0.0/tghome_1)
      (ENV="LD_LIBRARY_PATH=/usr/lib64:/home/oracle/app/oracle/product/19.0.0/tghome_1/lib")
      (PROGRAM=DG4ODBC)
      )
   )
```

- tnsnames.oraの設定

```bash:tnsnames.ora
DG4ODBC =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=DG4ODBC))
      (HS=OK)
   )
```

- Oracle-ODBCファイルの設定

```bash:$ORACLE_HOME/hs/admin/initdg4odbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DG4ODBC
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME = /lib64/libodbc.so.2

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
```

※ `HS_FDS_TRACE_FILE_NAME`のパスは、以下で確認する

```bash
$ ldd `which isql` | grep odbc
        libodbc.so.2 => /lib64/libodbc.so.2 (0x00007fa6bd06b000)
```

#■ Database Cloud Service(DBCS)からNon-OracleDB（MySQL）へのDBLINK接続

##● システム構成イメージ

DBCS（ローカル） -- [Oracle Net:TCPS] --> DG4O --[ODBC]--> MySQL on Compute（リモート）

※ DG4OとNon-Oracleは同一サーバ上に配置

##● 設定例

- Oracle Net設定 on DG4O
- Oracle Net設定 on DBCS
- DBLINK作成

### - Oracle Net設定 on DG4O

- ウォレットファイルの確認

```bash
$ ls /u01/server/wallet
cwallet.sso  ewallet.p12
```

- sqlnet.oraの編集

```bash
# sqlnet.ora Network Configuration File: /u01/app/19.0.0.0/grid/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))
 
SSL_SERVER_DN_MATCH=(ON)
```

- lisener.oraの編集

wallet_locationを追記

```bash
wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))
```

- TCPSエンドポイントの再構成

```bash
$ lsnrctl reload
$ lsnrctl status
```

### - Oracle Net設定 on DBCS

- ウォレットファイルの確認

```bash
$ ls /u01/client/wallet
cwallet.sso  ewallet.p12
```

- sqlnet.oraの編集

```bash
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/client/wallet)
     )
   )
 
SSL_SERVER_DN_MATCH=(ON)
```

- tnsnames.oraの編集

```bash
DG4ODBCS =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcps)(HOST=dg4o)(PORT=1522))
      (CONNECT_DATA=(SID=DG4ODBC))
      (HS=OK)
      (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
   )
```

### - DBLINK作成

DBLINK作成

```sql
CREATE PUBLIC DATABASE LINK DBLINK_DG4ODBCS CONNECT TO "root" IDENTIFIED BY "WelCome123#123#" USING 'DG4ODBCS';
```

DBLINK経由でMySQLにSQLを実行

```sql
SQL> select count(*) from "employees"@DBLINK_DG4ODBCS;

  COUNT(*)
----------
    300024
```

#■ Autonomous Database(ADB)からNon-OracleDB（MySQL）へのDBLINK接続（DG4ODBCとNon-OracleDBを同一ノードに配置）

##● システム構成イメージ

ADB（ローカル） -- [Oracle Net:Public/TCPS] --> DG4O --[ODBC]--> MySQL on Compute（リモート）

※ DG4OとNon-Oracleは同一サーバ上に配置
※ ADBとDG4OはPublic IPアドレスで接続（Oracle Cloud内に閉じた通信で、Internetにはトラフィックが流れない）

##● 設定例

- ADBからの接続設定：TCPS接続先（DG4O）
- ADBへのSQL接続設定
- ADBにDG4O接続用のウォレットファイルの登録
- ADBのDBLINK作成

###- Autonomous DBからの接続設定：TCPS接続先（DG4O）

- OCI-Security List設定

Autonomous DBのOutbound IPの確認

```sql
SELECT cloud_identity FROM v$pdbs;
```

OCI Consoleを用いて以下通信経路を確保

`Autonomous DB -(TCPS)-> DG4O`

- DG4OのFirewall設定

Firewall設定の確認

```bash
$ sudo firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens3
  sources:
  services: dhcpv6-client ssh
  ports: 5900-5902/tcp 1521-1523/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
```

- Security Listの登録

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/67c8d36f-48b6-410e-b016-20d87df457c2.png)

###- ADBへのSQL接続設定

- Autonomous databaseのIDを確認

```bash
$ oci db autonomous-database list
```

- ウォレットファイルのダウンロード

```bash
$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.xxxxxx --file /home/oracle/Wallet/Wallet_ADBS-Private.zip --password <your password> 
```

- ウォレットファイルを用いてSQL接続

```bash
$ sql /nolog
SQL> set cloudconfig /home/oracle/Wallet/Wallet_ADBS-Private.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config6589439447521943899

SQL> connect admin/WelCome123#123#@adbs_high
```

###- ADBにDG4O接続用のウォレットファイルの登録

- クレデンシャルの登録

credential登録 : ADB -> Object Storage
・username: OCI User（IAM User）
・Password: 認証トークン

```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OS_CRED',
    username => 'foo',
    password => 'baa'
  );
END;
/
```

credential登録 : DBLINK（ADB -> DG4O/MySQL）
・username: MySQL User
・Password: MySQL Password

```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MySQL_CRED',
    username => 'root',
    password => 'WelCome123#123#'
  );
END;
/
```

- 登録されたクレデンシャルの確認

```bash
SELECT credential_name, username, comments FROM all_credentials;
```

- ウォレットをbucketにアップロード

```bash
$ oci os object put -bn ADB_DBLIN_DG4O --file '/home/oracle/Wallet/client_wallet/cwallet.sso' --name cwallet.sso
```

- 確認

```bash
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OS_CRED', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/ADB_DBLIN_DG4O/o/');
   OBJECT_NAME    BYTES                            CHECKSUM    CREATED                          LAST_MODIFIED
______________ ________ ___________________________________ __________ ______________________________________
cwallet.sso        6269 0b7425beae28120fab237db73e9cfbfd               20-APR-21 04.56.13.733000000 AM GMT
```

###- DBLINK作成

- ディレクトリの作成

```
CREATE DIRECTORY ADB_DBLINK_DG4O_DIR AS 'ADB_DBLINK_DG4O_DIR';
```

- Walletファイルをディレクトリに取り込み

```
BEGIN 
     DBMS_CLOUD.GET_OBJECT(
	credential_name => 'OS_CRED',	
        object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/ADB_DBLIN_DG4O/o/cwallet.sso',
        directory_name => 'ADB_DBLINK_DG4O_DIR'); 
END;
/
```

- 確認

```sql
SELECT * FROM DBMS_CLOUD.LIST_FILES('ADB_DBLINK_DG4O_DIR');

   OBJECT_NAME    BYTES    CHECKSUM                                CREATED                          LAST_MODIFIED
______________ ________ ___________ ______________________________________ ______________________________________
cwallet.sso        6269             20-APR-21 05.18.15.000000000 AM GMT    20-APR-21 05.18.15.000000000 AM GMT
```

- GLOBAL_NAMESをFALSEに設定

```sql
SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
 
System altered.

SQL> SHOW PARAMETER GLOBAL_NAMES
NAME                     TYPE        VALUE
----------------------   ----------- -----------
global_names             boolean     FALSE
```

- DBLINKの作成(Database Gateway for ODBC経由)
※ `gateway_link => TRUE`パラメータを追加する必要あり

```sql
BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'ADB_DBLINK_DG4O', 
          hostname => 'W.X.Y.Z', 
          port => '1522',
          service_name => 'DG4ODBC',
          ssl_server_cert_dn => 'CN=dbcs',
          credential_name => 'MySQL_CRED',
          directory_name => 'ADB_DBLINK_DG4O_DIR',
          gateway_link => TRUE);
END;
/
```

- 確認

```SQL
SQL> select * from all_db_links;
```

#■ ADBからNon-OracleDB（MySQL Database Service : MDS）へのDBLINK接続（DG4ODBCとNon-OracleDBを別ノードに配置）

##● システム構成イメージ

ADB（ローカル） -- [Oracle Net] --> DG4O --[ODBC]--> MDS（リモート）

##● 設定例

「Autonomous Database(ADB)からNon-OracleDB（MySQL）へのDBLINK接続」の構成を前提に以下を設定

- MDSのセットアップ
- Oracle Net設定の変更
- ODBC設定の変更
- Firewall設定の変更
- DBLINKの作成

###- MDSのセットアップ

[この記事](https://qiita.com/sugimount/items/cbe9a15c169fe597894e)などを参考にMDSをセットアップ

OCIコンソールからMySQL → DBシステムを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c54bf4fe-2e72-db71-7c36-117f460aee53.png)

MySQL DBシステムの作成を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c2a83060-088e-3445-c3a2-b28ecf810c28.png)

以下、ノード基本情報、ネットワーク、シェイプ選択、バックアップ構成、ホスト名などを登録
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/a635a085-6b2d-4215-8c2d-8f2411ca9215.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/9e4f7323-3a55-5d36-f0d5-a9e30e2f8b44.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/ce547d33-ebf2-3acb-5b0e-510fa77a4f36.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/cb07419f-8f23-1979-e8f6-b5ccbea45bdb.png)


作成後、しばらく（10数分）すると以下のとおりサービスがActiveになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/ead6ffa2-85a0-7626-63d6-3b90a7325218.png)

エンドポイントを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/70b21703-fc49-b5f1-9b0e-9dab7a2bfdc0.png)

###- ODBC設定の変更

- /etc/odbc.iniファイルにMDS接続用設定を追記

```bash
[mds]
Driver       = /usr/lib64/libmyodbc8w.so
Description  = Connector/ODBC 8.0 UNICODE Driver DSN
SERVER       = mds01.sub04121421101.vcn05.oraclevcn.com
PORT         = 3306
USER         = remotemds
Password     = WelCome123#123#
Database     = employees
OPTION       = 3
CHARSET      = utf8
```

###- Oracle Net設定の変更

- initファイル作成

```bash:$ORACLE_HOME:hs/admin/initMDS.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MDS
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME = /lib64/libodbc.so.2

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

HS_NLS_NCHAR=UCS2
HS_LANGUAGE=JAPANESE_JAPAN.AL32UTF8
```

- tnsnames.oraの設定変更

MDSを追加

```bash
MDS =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=MDS))
      (HS=OK)
      (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
   )
```

- listener.oraの設定変更

SID_LISTに、MDSを追加

```bash
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
       (SID_NAME=DG4ODBC)
       (ORACLE_HOME=/home/oracle/app/oracle/product/19.0.0/tghome_1)
       (ENV="LD_LIBRARY_PATH=/lib64:/usr/lib:/usr/lib64:/home/oracle/app/oracle/product/19.0.0/tghome_1/lib")
       (PROGRAM=dg4odbc)
      )
      (SID_DESC =
       (SID_NAME=MDS)
       (ORACLE_HOME=/home/oracle/app/oracle/product/19.0.0/tghome_1)
       (ENV="LD_LIBRARY_PATH=/lib64:/usr/lib:/usr/lib64:/home/oracle/app/oracle/product/19.0.0/tghome_1/lib")
       (PROGRAM=dg4odbc)
      )
   )
```

- エンドポイントの更新

listenerのreload

```sql
$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 03:27:42

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT=1521)))
The command completed successfully
```

確認
Service "MDS"が登録されていることを確認

```sql
$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 03:27:52

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-APR-2021 16:16:33
Uptime                    3 days 11 hr. 11 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/19.0.0/tghome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/instance-20210418-1233/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=instance-20210418-1233.sub04121421100.vcn05.oraclevcn.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DG4ODBC" has 1 instance(s).
  Instance "DG4ODBC", status UNKNOWN, has 1 handler(s) for this service...
Service "MDS" has 1 instance(s).
  Instance "MDS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
```

###- Firewall設定の変更

- VCNのセキュリティリストにDG4OからMDSにアクセスするための設定を追加

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7e2a70d1-0664-b173-b724-2c1206e69d7c.png)


- DG4OからMDSエンドポイントに向けて接続確認

```sql
$ mysql -h mds01.sub04121421101.vcn05.oraclevcn.com -u remotemds -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.23-u2-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
```

- テスト用のDB/Table作成

```sql
$ mysql -h mds01.sub04121421101.vcn05.oraclevcn.com -u remotemds -p
mysql> create database employees;
mysql> use employees;
Database changed
mysql> show database;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);
```

insert test data

```sql
INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24')
```

###- DBLINKの作成

- ウォレットファイルの確認

[Database Cloud Service(DBCS)からNon-OracleDB（MySQL）へのDBLINK接続]()で登録したクレデンシャルやウォレットファイルを確認

```sql
sql /nolog

!-- ADBのウォレットファイルを使ってADBにSQL接続
SQL> set cloudconfig /home/oracle/Wallet/Wallet_ADBS-Private.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config6589439447521943899

SQL> connect admin/WelCome123#123#@adbs_high

!-- ADBに登録されているクレデンシャルファイルを確認
>SQL SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OS_CRED', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/hogehoge/b/ADB_DBLIN_DG4O/o/');

SQL> SELECT *  FROM all_credentials);
   OWNER    CREDENTIAL_NAME                                                USERNAME    WINDOWS_DOMAIN                                                   COMMENTS    ENABLED
________ __________________ _______________________________________________________ _________________ __________________________________________________________ __________
ADMIN    MYSQL_CRED         root                                                                      {"comments":"Created via DBMS_CLOUD.create_credential"}    TRUE
ADMIN    OS_CRED            oracleidentitycloudservice/hoge@oracle.com                      {"comments":"Created via DBMS_CLOUD.create_credential"}    TRUE

!-- DG4OへのTCPS接続用のウォレットファイルの確認
SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OS_CRED', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/ADB_DBLIN_DG4O/o/');

   OBJECT_NAME    BYTES                            CHECKSUM    CREATED                          LAST_MODIFIED
______________ ________ ___________________________________ __________ ______________________________________
cwallet.sso        6269 0b7425beae28120fab237db73e9cfbfd               20-APR-21 04.56.13.733000000 AM GMT
```
###- DBLINKの作成

- MDS接続用のディレクトリの作成

```sql
SQL> CREATE DIRECTORY ADB_DBLINK_MDS_DIR AS 'ADB_DBLINK_MDS_DIR';

SQL> select * from dba_directories where directory_name = 'ADB_DBLINK_MDS_DIR';
   OWNER        DIRECTORY_NAME                                                        DIRECTORY_PATH    ORIGIN_CON_ID
________ _____________________ _____________________________________________________________________ ________________
SYS      ADB_DBLINK_MDS_DIR    /u03/dbfs/BFC7FF6CFC9043AEE0530714000AA508/data/ADB_DBLINK_MDS_DIR                  20
```

- Walletファイルをディレクトリに取り込み

```sql
SQL> BEGIN 
     DBMS_CLOUD.GET_OBJECT(
    credential_name => 'OS_CRED',   
        object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/ADB_DBLIN_DG4O/o/cwallet.sso',
        directory_name => 'ADB_DBLINK_MDS_DIR'); 
END;
/
```

- DBLINK用のクレデンシャル作成

username: MySQL User
Password: MySQL Password

```sql
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MDS_CRED',
    username => 'remotemds',
    password => 'WelCome123#123#'
  );
END;
/
```

- DBLINKの作成

```sql
SQL> BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'ADB_DBLINK_MDS', 
          hostname => 'W.X.Y.Z', 
          port => '1522',
          service_name => 'DG4ODBC',
          ssl_server_cert_dn => 'CN=dbcs',
          credential_name => 'MDS_CRED',
          directory_name => 'ADB_DBLINK_MDS_DIR',
          gateway_link => TRUE);
END;
/
```

- 確認

```
SQL>  select * from "employees"@ADB_DBLINK_MDS;

   emp_no    birth_date    first_name    last_name    gender    hire_date
_________ _____________ _____________ ____________ _________ ____________
    10001 02-SEP-53     Georgi        Facello      M         26-JUN-86
    10002 02-JUN-64     Bezalel       Simmel       F         21-NOV-85
    10003 03-DEC-59     Parto         Bamford      M         28-AUG-86
    10004 01-MAY-54     Chirstian     Koblick      M         01-DEC-86
    10005 21-JAN-55     Kyoichi       Maliniak     M         12-SEP-89
    10006 20-APR-53     Anneke        Preusig      F         02-JUN-89
    10007 23-MAY-57     Tzvetan       Zielinski    F         10-FEB-89
    10008 19-FEB-58     Saniya        Kalloufi     M         15-SEP-94
    10009 19-APR-52     Sumant        Peac         F         18-FEB-85
    10010 01-JUN-63     Duangkaew     Piveteau     F         24-AUG-89

10 rows selected.

```



#■ 参考情報

##● 資料

Database Gateway for ODBC

- Documentation
 - [Create Database Links to an Oracle Database Gateway to Access Non-Oracle Databases](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/database-links-other-databases.html#GUID-E4F35EEE-3147-4822-A9EB-A341D87E4ECF)
 - [CREATE_DATABASE_LINKプロシージャ](https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/dbms-cloud-admin.html#GUID-8F1DEFF0-08ED-4BB7-825A-01FEE214A079)
 - [Oracle Database Gateway for ODBC](https://docs.oracle.com/en/database/oracle/oracle-database/19/odbcu/index.html)
- MOS
 - [Master Note for Oracle Gateway Products (Doc ID 1083703.1)](https://support.oracle.com/epmos/faces/DocumentDisplay_afrLoop=381241568779311&id=561033.1&_adf.ctrl-state=4fh7cta8f_53)
 - [How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)](https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=68274352939517&id=1083703.1&displayIndex=7&_afrWindowMode=0&_adf.ctrl-state=17bgh7e5ak_127)
- Blogs
 - [How to Access Non-Oracle Databases from Autonomous Database using Oracle Database Gateway](https://blogs.oracle.com/datawarehousing/how-to-access-non-oracle-databases-from-autonomous-database-using-oracle-database-gateway)
 - [https://cloudness.net/configure-dg4odbc-in-oci-database-cloud-service/](https://cloudness.net/configure-dg4odbc-in-oci-database-cloud-service/)
 - [Oracle Database Gatewayを用いてOCI Database System 19cとMS SQL Server 2016を接続する方法](https://qiita.com/RexZheng/items/585f468d0f2108abb2ff)

MySQL

- [github : sample database(test_db)](https://github.com/datacharmer/test_db)
- [OCI Compute上のLinux仮想マシンにMySQLをインストールしてみた](https://qiita.com/orakurara/items/a8dcef622a59d73b8df9)
- [Oracle MySQL Database Serviceのインスタンスを作ってMySQL Workbenchから接続してみた](https://qiita.com/orakurara/items/e8e158eebad1295a9448)
- [https://qiita.com/ucan-lab/items/c80fb8a880679fa20162](https://qiita.com/ucan-lab/items/c80fb8a880679fa20162)

ODBC

- [The unixODBC Project home page](http://www.unixodbc.org/)
- [Linux/UNIX ODBC](https://www.easysoft.com/developer/interfaces/odbc/linux.html)
- [MySQL Connector/ODBC Developer Guide](https://dev.mysql.com/doc/connector-odbc/en/)
 - [Configuring a Connector/ODBC DSN on Unix](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-unix.html)
 - [ODBC Connection Parameters](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html)

##● 関連する設定・コマンド

###- VNCの設定

oracleユーザ接続用のVNCを設定

```bash
$ sudo su - oracle
$ vncpasswd
$ vncserver :1
$ vncserver -list

TigerVNC server sessions:

X DISPLAY #     PROCESS ID
:1              11754

```

firewllの設定

```bash
$ sudo firewall-cmd --permanent --add-port=5900-5902/tcp
$ sudo firewall-cmd --permanent --add-port=1521-1523/tcp
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-all
```

###- MySQL設定

確認

```bash
$ mysql --version
mysql  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
```

MySQL初期セットアップ

```bash
$ sudo systemctl start mysqld
$ sudo grep password /var/log/mysqld.log
2021-04-18T06:57:39.457413Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: J7gfAaMuX)wm

$ mysql -u root -p 
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'WelCome123#123#';
> exit
$ mysql -u root -p
```

※ 本番環境ならば[mysql_secure_installation](https://qiita.com/rutko/items/27c0f76cee37260a1cda)を実施

sample dbのセットアップ

```bash
$ git clone https://github.com/datacharmer/test_db.git
$ cd test_db
$ mysql -u root -p < employees.sql
$ mysql -u root -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use employees
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.03 sec)
```

- MySQL サービスコントロール

```bash
# MySQL サービスの起動
$ sudo systemctl start mysql
# MySQL サービスの停止
$ sudo systemctl stop mysql
# MySQL サービスの再起動
$ sudo systemctl restart mysql
# MySQL サービスの自動起動の有効化
$ sudo systemctl enable mysql
# MySQL サービスの自動起動の無効化
$ sudo systemctl disable mysql
```