読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

OracleからMySQLへODBC経由のデータベースリンクで接続したメモ

Oracle Standard Editionで可能なDatabase Gateway for ODBC (DG4ODBC)を使用してMySQLに接続したのでメモ。
Database Gateway for ODBC (DG4ODBC)を使用すれば
ODBC接続でOracleから他のDB(MySQL)などにデータベースリンク経由で接続可能となる。

環境

DB DBVersion OS IP DB文字コード
Oracle 11.2.0.4 SEone Oracle Linux 6.5 192.168.1.11 EUC_JP
MySQL 5.6.20 CentOS 6.5 192.168.1.10 EUC_JP

以下を元にインストールと設定

Oracle Database Gateway for ODBCの構成

1.OracleサーバからODBC経由でMySQLへ接続するための設定

unixODBCのインストールと設定

unixODBCとconnectorをyumでインストールする

#yum install unixODBC
#yum install mysql-connector-odbc

unixODBCの状態確認

# odbcinst -j
unixODBC 2.2.14
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

unixODBCのドライバーの確認

#vim /etc/odbcinst.ini
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

MySQLの接続設定を行う

# vim  /etc/odbc.ini
[TOKDB]・・・データソース名
Driver     = MySQL・・・/etc/odbcinst.iniのドライバ名
SERVER     = 192.168.1.10・・・MySQLのホスト名またはIP
DATABASE   = KDB・・・MySQLの接続するデータベース名
PORT       = 3306・・・MySQLのポート番号
PASSWORD   = oraora・・・MySQLのパスワード
USER       = fromora・・・MySQLユーザ名

SYSTEM DATA SOURCESで設定を行うこと。
USER DATA SOURCES(/home/oracle/.odbc.ini)では動作しなかったので注意。

MySQL側の設定

IP許可をする
GRANT USAGE ON *.* TO 'fromora'@'192.168.1.11' IDENTIFIED BY 'oraora';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE ON `kdb`.* TO 'fromora'@'192.168.1.11';

ODBCで接続できるかテスト

# isql -v TOKDB
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.20-log|
+-----------+

これでOracle側のサーバからODBC接続でMySQLへ接続できることは確認できた。

2.Oracleでデータベースリンクを使ってMySQLへ接続する設定

DG4ODBCのインストール方法

  • Oracleデータベースと同じ$ORACLE_HOMEへ追加インストールする方法
  • Oracleデータベースと異なる別マシンへのインストール

2種類のインストール方法があるみたいだがOracleデータベースにインストールする。

DESCRIBE SYS.HS_FDS_CLASSがあることを確認する、あればデフォルトでインストールはされている模様。

SQL> DESCRIBE SYS.HS_FDS_CLASS
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 FDS_CLASS_NAME                            NOT NULL VARCHAR2(30)
 FDS_CLASS_COMMENTS                                 VARCHAR2(255)
 FDS_CLASS_ID                              NOT NULL NUMBER

ちなみに、なければ$ORACLE_HOME/rdbms/admin にある caths.sql をas sysdbaで実行すればいいみたい。

初期化パラメータ(initファイル)を修正する

サンプル($ORACLE_HOME/hs/admin/initdg4odbc.ora)があるので
サンプルの初期化ファイルをコピーしてその名前をinitsid.oraに変更します。
sidは、ゲートウェイの接続先となるOracle以外のシステムのインスタンスで使用されるシステム識別子(SID)です)

mv initdg4odbc.ora inittomysql.ora
oracle$ vim inittomysql.ora
HS_FDS_CONNECT_INFO = TOKDB
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
set ODBCINI= /etc/odbc.ini
  • HS_FDS_CONNECT_INFO=dsn_value(odbc.iniファイルに構成されているデータソース名)
  • HS_FDS_SHAREABLE_NAME=full_path_of_odbc_driver(ODBCドライバ・マネージャのフルパス)

リスナーの追加

Oracleデータベース上で1521ポートのリスナーはすでに稼動していたのでLISTENER2(port:1522)を追加する

vim $ORACLE_HOME/network/admin/listener.ora
LISTENER2 = 
     (DESCRIPTION_LIST = 
       (DESCRIPTION = 
         (ADDRESS_LIST = 
           (ADDRESS =(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)) 
         )
       )
     ) 

SID_LIST_LISTENER2 = 
     (SID_LIST = 
       (SID_DESC =
         (SID_NAME=tomysql)
         (ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1)
         (PROGRAM=DG4ODBC)
       )
 )

起動する

$ lsnrctl start listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-7月 -2015 14:57:19

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

/opt/oracle/product/11.2.0/dbhome_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
システム・パラメータ・ファイルは/opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraです。
ログ・メッセージを/opt/oracle/diag/tnslsnr/oracle-test-db01/listener2/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
TNS-01201: リスナーでは、実行可能な/opt/oracle/product/11.2.0/dbhome_1/bin/DG4ODBC(SID tomysql)を検索できません。

リスナーが起動に失敗しました。前述のエラー・メッセージを参照してください...

む、失敗した。。
/opt/oracle/product/11.2.0/dbhome_1/bin/DG4ODBC(SID tomysql)を検索できません。
確認すると

$ ls  /opt/oracle/product/11.2.0/dbhome_1/bin/ | grep -i DG4ODBC
dg4odbc
dg4odbcO

小文字だったのね。。というわけで修正(PROGRAM=DG4ODBC)(PROGRAM=dg4odbc)

vim $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER2 = 
     (SID_LIST = 
       (SID_DESC =
         (SID_NAME=tomysql)
         (ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1)
         (PROGRAM=dg4odbc)
       )
 )

再度、起動する

$ lsnrctl start listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-7月 -2015 14:58:13

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

/opt/oracle/product/11.2.0/dbhome_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
システム・パラメータ・ファイルは/opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraです。
ログ・メッセージを/opt/oracle/diag/tnslsnr/oracle-test-db01/listener2/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))に接続中
リスナーのステータス
------------------------
別名                      listener2
バージョン                TNSLSNR for Linux: Version 11.2.0.4.0 - Production
開始日                    24-7月 -2015 14:58:13
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
ログ・ファイル            /opt/oracle/diag/tnslsnr/oracle-test-db01/listener2/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
サービスのサマリー...
サービス"tomysql"には、1件のインスタンスがあります。
  インスタンス"tomysql"、状態UNKNOWNには、このサービスに対する1件のハンドラがあります...
コマンドは正常に終了しました。

無事リスナーの起動ができた。

tnsnames.oraの編集

vim $ORACLE_HOME/network/admin/tnsnames.ora

dg4odbc=                   →データベース・リンクの作成時に使用する(任意の文字列)
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=localhost)  →ゲートウェイが稼働しているマシン
         (PORT=1522)       →listener.oraで設定したポート
      )
      (CONNECT_DATA=
         (SID=tomysql))    →listener.oraファイルで指定されたSID
      (HS=OK))             →これは必須らしい

データベースリンク作成

SQL> CREATE PUBLIC DATABASE LINK DBLINK_MYSQL CONNECT TO "fromora" IDENTIFIED BY "oraora" USING 'dg4odbc';

データベース・リンクが作成されました。

これで完成のはず。 では試してみた。

SQL> desc tt@DBLINK_MYSQL;
ERROR:
ORA-02085: データベース・リンクDBLINK_MYSQLはHO.WORLDに接続します

お、なんかできない。
ORA-02085でぐぐってると、 global_namesがあやしいので確認すると

trueに設定するとデータベース・リンクが、接続するデータベースと同じ名前を持つ必要がある

SQL> SELECT VALUE FROM v$parameter WHERE NAME LIKE 'global_names';

VALUE
--------------------------------------------------------------------------------
TRUE

だったのでfalseに変更

SQL> alter system set global_names= false;

システムが変更されました。

再度確認。

SQL> desc  tt@DBLINK_MYSQL;
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 id                                                 NUMBER(10)
 t                                                  NVARCHAR2(10)

よし。できた!とも思ったらNVARCHAR2(10)??

SQL> select * from   tt@DBLINK_MYSQL;

        id t
---------- ----------------------------------------
         1 {0R0{0R0

やはり文字化けしてる。 MySQL側を確認すると

mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| t     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> select * from tt;
+------+----------+
| id   | t        |
+------+----------+
|    1 | ほげほげ |
+------+----------+

MySQL側の文字コード(euc-jp)にあわせるのに MySQL :: MySQL Connector/ODBC Developer Guide :: 5.2 Connector/ODBC Connection Parameters を確認すると
/etc/odbc.iniにcharset = ujisを追加してあげる必要があるみたい。

SQL> select * from   tt@DBLINK_MYSQL;

        id t
---------- -------------------------------
         1 ほげほげ

これで無事できるようになった。

あとは、MySQL側のカラムにはダブルクォーテーションで囲んであげないとだめみたい。SQLPLUSだけなのかな?

SQL> select a.id,a.dbnm,b."dbnm" from ott a join tt@DBLINK_MYSQL b on a.id=b."id";

        ID DBNM       dbnm
---------- ---------- ------------------------------
         1 Oracle     MySQL

これでODBC経由でOracleMySQLのjoinとかできるから便利だー。