Oracle から SQL Server に接続する

Oracle からデータベースリンク経由で SQL Server に接続してデータ操作を行う方法をご紹介します。
OS は Windows10 、関連製品のバージョンは Oracle 19c と SQL Server 2019 です。
環境の都合上、同一PCに同居させていますが、もちろん別PCであっても問題ありません。

Oracle Database Gateway for ODBC

Oracle 19c を利用しています。SQL Server に接続するための機能として「Oracle Database Gateway for SQLServer」というものがありますが、価格表を見たところ"Integration Products"のひとつとして、Computer License で別途購入が必要そうです。
じゃあ無償でやることはできないのかなと思って調べていると「Databaseライセンス情報ユーザー・マニュアル」に以下の記載がありました。

Oracle Database Gateway for ODBCは、Oracle Databaseがインストールおよび使用されているマシンと異なるマシン上にインストールし、使用できます。Oracle Gateway for ODBCを実行しているマシン用に別途ライセンスを取得する必要はありません。

1.7 特別なライセンス権限

確かに先ほどの価格表には「for ODBC」については記載がなかったです。つまり、ODBC 経由であれば標準で利用できるということですね。
尚、設定方法等の詳細は「インストレーションおよび構成ガイドfor Microsoft Windows」の「11 Oracle Database Gateway for ODBCの構成」 に記載がありますが、。今回もこちらを読みながらやってます。

SQL Server 側にユーザを作成する

今回は Oracle から接続するにあたって専用ユーザを作ることにします。本来はこのようなアプリケーション固定のユーザ登録は好ましくない（パスワードを埋め込みが発生して変更しづらくなるため）ので OS 認証を使ってみたいところですが、まだ未検証なので別途ということで。
尚、SQL Server 上にはあらかじめ「TESTDB」というデータベースに sample というテーブルを作成しています。中身は以下の通りです。

SQL Server テストデータ

次に、接続用のユーザ（ログイン）を作成します。

ログインの作成（１）

テスト用のデータベースに対して読み書きができるように権限設定しておきます。

ログインの作成（２）
ログインの作成（３）

念のため、作成したユーザを使ってログインし、TESTDB の sample テーブルが参照できるかを確認しておいてください。

ODBC データソースの作成

次に ODBC 経由ということなので、ODBC データソースを作成したいと思います。
スタートメニューより「ODBC データソース (64 ビット)」を起動し、「システム DSN」タブを選択します。

ODBC データソース アドミニストレーター

ここにデータソースを追加していきます。ドライバは、SQL Server に接続できれば良いのですが、とりあえず「SQL Server」を選択します。

データソースの新規作成（１）

名前とサーバーを入力します。今回は同居なのでサーバーは localhost です。データソースの名前は「MSSQL2019DSN」とします。

データソースの新規作成（２）

IDとパスワードは、Oracle側から設定しますので「ユーザーが入力する～」を選択し、「SQL Server に接続して～」のチェックボックスは外します。

データソースの新規作成（３）

あとはデフォルトで良いです。必要に応じて変更してください。
既定のデータベースも、SQL Server のログイン作成したときに設定しているので不要です。

データソースの新規作成（４）
データソースの新規作成（５）

「完了」を選択すると、設定内容が出てくるので「OK」を選択します。尚、今回データソースにはユーザID等を設定していませんので、こちらの画面からのテストは失敗します。

Database Gateway for ODBC の構成

それでは、Oracle Database Gateway の設定を行います。尚、今回の環境の ORACLE_HOME ですが、
　C:\oracle\product\19.3.0\dbhome_1
としています。以降、このフォルダは「%ORACLE_HOME%」と記載します。

初期化パラメータファイル（init｛Gateway SID}.ora）の作成

初めに Gateway SID の初期化パラメータを作成します。"init{Gateway SID}.ora"というファイル名で、通常のデータベースインスタンスの初期化パラメータファイルと同じですね。Oracle からみると、Gatewayもインスタンスのひとつということなのでしょう。今回は Gateway SID を「MSSQL2019GW」とします。

%ORACLE_HOME%\hs\admin\initMSSQL2019GW.ora

HS_FDS_CONNECT_INFO = MSSQL2019DSN
HS_FDS_TRACE_LEVEL = 0

HS_FDS_CONNECT_INFO には先ほど登録したODBCデータソースの「名前」を設定してください。

listener.ora の設定

先ほどの Gateway SID を Oracle リスナーに認識させるための設定を追加します。

%ORACLE_HOME%\network\admin\listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME = MSSQL2019GW)
      (ORACLE_HOME = C:\oracle\product\19.3.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

SID_NAME には、Gateway SID を指定します。プログラムの dg4odbc というのが Database Gateway for ODBC 本体（%ORACLE_HOME%\bin\dg4odbc.exe）を指しているものと思われます。
尚、上記には追加すべき設定のみを掲載していますが、デフォルトの listener.ora に追加したイメージはこんな感じです。

listener.ora 設定イメージ

リスナーを再起動します。"lsnrctl stop"からの"lsnrctl start" ですね。

リスナーの再起動

tnsnames.ora の設定

次に Gateway SID にアクセスするための TNS 名の定義を行います。

%ORACLE_HOME%\network\admin\tnsnames.ora 

MSSQL2019TNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SID = MSSQL2019GW)
    )
    (HS = OK)
  )

これで Database Gateway for ODBC としての設定は終了です

Oracle からの接続

Oracle から Database Gateway 経由でアクセスするためには、データベースリンクを作成する必要があります。ログインするためのユーザとパスワードはここに記載します。

CREATE PUBLIC DATABASE LINK MSSQL2019LINK
  CONNECT TO "orauser"
  IDENTIFIED BY "******"
  USING 'MSSQL2019TNS';

これで準備完了です。実際に Oracle から SQL Server に接続して sample テーブルを参照してみます。

SELECT * FROM SAMPLE@MSSQL2019LINK;
SQL Server データの参照

見事に中身が参照できました。ついでに1行追加してみます。

SQL Server データの追加

これを SQL Server 側で参照してみます。

SQL Server テストデータの参照

うまく登録できました。
Gateway SID は任意の値に設定できるので、複数のデータベースも定義できそうです。また、ODBC 接続ができれば良いので、SQL Server 以外のデータベースでもいけそうですね。

データベースODBC,Oracle,Oracle Database Gateway,Setup,SQLServer

Posted by takmaru