linux 作業系統下ORACLE資料庫使用透明閘道器連線MYSQL

在mysql上建立使用者
create database dbname;
grant all on dbname.* to username@'%'  identified by 'dbname123';

在oracle服務端安裝
 yum install -y mysql-connector-odbc
chown oracle.oinstall /etc/odbc.ini

在oracle服務端配置odbc
vim /etc/odbc.ini
[mysql207]
Description     = MySQL test database
Trace       = debug
TraceFile   = mysql.trace.log
Driver      = /usr/lib64/libmyodbc5.so
SERVER      = 10.100.105.207
USER        = username
PASSWORD    = dbname123
PORT        = 3306
DATABASE    = dbname
-----------
其中，mysql207為服務名，後面將會用到
/usr/lib64/libmyodbc5.so 為odbc 驅動
10.100.105.207 為要訪問mysql 所在IP
username登陸mysql 伺服器使用者名稱（isql用到，後面建立的dblink不使用）
3306 為mysql 資料庫埠（isql用到，後面建立的dblink不使用）


在oracle服務端測試odbc
su - oracle
isql mysql207


在$ORACLE_HOME/hs/admin/目錄下，配置initmysql207.ora檔案，initmysql207.ora命名方式為init+服務名+.ora。新增如下內容
more $ORACLE_HOME/hs/admin/initmysql207.ora 
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql207
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_FDS_SQLLEN_INTERPRETATION=64
HS_LONG_PIECE_TRANSFER_SIZE=1258291


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

-------------
HS_FDS_CONNECT_INFO為服務名，與前面保持一致
HS_FDS_TRACE_LEVEL 為日誌跟蹤級別，不使用時可以設為OFF
HS_FDS_SHAREABLE_NAME 為ODBC驅動
HS_LANGUAGE 為異構伺服器的字符集，語言
HS_FDS_SQLLEN_INTERPRETATION 允許64bit 驅動可以使用32bit 標準


修改listener.ora 檔案，新增一個監聽器：
LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = mysql207)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
  )

或者在oracle增加靜態監聽：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = mysql207)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
  )
-----------
SID_NAME為服務名
PROGRAM 為使用dg4odbc 驅動

啟動listener1
lsnrctl start listener1

修改tnsnames.ora 檔案，新增如下內容
mysql207 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.105.207)(PORT = 1522))
    (CONNECT_DATA =
     (SERVICE_NAME = mysql207)
    )
    (hs=ok)
  )

其中SID為前面定義的服務名
HOST為本機的機器名或者IP
PORT為本機的資料庫監聽器埠

建oracle 中建立資料庫連結
sqlplus / as sysdba
create  public database link lnmysql207 connect to "username" identified by "dbname123" using 'mysql207';

測試：
select * from t1@lnmysql207;

RROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.100.105.208)(PORT=1521))
(CONNECT_DATA=(SID=mysql207)))
ORA-02063: preceding line from LN_MY23_3322
Process ID: 18585
Session ID: 96 Serial number: 207

在init'mysql207.ora檔案中新增如下引數
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_FDS_SQLLEN_INTERPRETATION=64