oracle 通过 ODBC 透明网关访问 mssql

 参考文章： />  
 若干年前实施过 Oracle 到 sybase 的透明网关，一切顺利。若干年后再度实施，天雷滚滚，一个坑接着一个坑啊。现做笔记，以期备忘。
 
 一、环境
 
 Oracle RAC 11.2.0.4.0，已安装 gateway
 MS Sql Server 版本不详，应在 2008 以上
 
 背景知识：
 gateway 是 Oracle 数据库软件的一个可选套件，官方名称叫透明网关，是用来解决 Oracle 同异构数据库的互联问题的。
 简单说就是配置适当后，可以启动 oracle 的 listener 来监听 sybase 、sql server 、mysql 以及一切可提供 ODBC 接口的数据库，可以通过 tnsnames.ora 对这些数据库进行名字解析，当然也可以在 oracle 中创建 database link 直接访问这些数据库。透明网关同时也会进行数据类型的自动转换，这对于异构数据库同 oracle 之间的数据互联提供了极大方便。
 
 比如要从 sql server 导入一张表到 oracle 中，传统的方式是 sql server 导出成 csv 或 excel，然后在 oracle 里面创建相同表结构的空表，通过 sql loader 或第三方工具进行导入。这种方式，首先效率极为低下，人工环节多，异常数据也难以处理，数据类型也要人工转换。如果是批量大规模的数据迁移，几乎是不可能进行的。引入透明网关后，只需要在 oracle 简单的 create table a_table as select * from a_table@mssql_link，一切搞定。
 
 Oracle 透明网关支持两种类型的驱动，数据库专属驱动或 odbc 驱动，专属驱动数量有限且更新不会太快。一般首选 odbc ，因为通用性更好，一次配置即可支持多种数据库。
 
 odbc 是微软提出的一种数据库通用接口规范，虽然主要立足于 windows 平台，但 linux 上也有开源实现。odbc 首先需要一个 Driver Manager ，linux 就是 unixODBC。然后各类不同的数据库各自提供自身的 odbc 接口库，通过 odbc.ini 的配置统一纳入 Driver Manager 的管理中。其他调用 odbc 的程序只需要按照 odbc 的结构规范同 Driver Manager 交互即可。比如 perl 、ruby 语言的 dbi 接口可以通过 dbd-odbc 访问，oracle 的透明网关可以通过 dg4odbc 访问。大致流程如下：
 
 sql server 数据库-> libmsodbcsql-11.0.so.2270.0 -> unixODBC libodbc.so -> dg4odbc -> gateway -> oracle 数据库
 sql server 数据库-> libmsodbcsql-11.0.so.2270.0 -> unixODBC libodbc.so -> dbd-odbc -> dbi -> perl、ruby
 
 因此我们的配置主要集中于这几个方面：
 1、安装 sql server 的 odbc 驱动
 2、安装 unixODBC
 3、安装 oracle transparent gateway
 4、配置 oracle 的 listener.ora 、initdg4odbc.ora 、tnsnames.ora 等
 5、创建 database link
 对于 ruby 等程序
 6、安装 dbi dbd-odbc 等： gen install dbi dbd-odbc ruby-odbc
 
 二、安装软件
 至 MS 官网下载：msodbcsql-11.0.2270.0
下载地址： /> 下载 unixODBC-2.3.0.tar.gz 备用。msodbcsql 需要依赖这个版本，想用其他版本，可以修改 msodbcsql 下的 build_dm.sh 跟 install.sh 脚本，一般不需要。

 1、解开 msodbcsql
 tar vfxz msodbcsql-11.0.2270.0.tar.gz
 cd msodbcsql-11.0.2270.0
 
 2、将 unixODBC 拷入 msodbcsql 目录，免得在线下载了
 cp ../unixODBC-2.3.0.tar.gz msodbcsql-11.0.2270.0
 
 3、./install.sh verify ；校验 unixODBC 是否就绪 。输出如下，我这个已经是安装好了的。
 
 # ./install.sh verify
 Microsoft ODBC Driver 11 for SQL Server Installation Script
 Copyright Microsoft Corp.
 Starting install for Microsoft ODBC Driver 11 for SQL Server
 Checking for 64 bit Linux compatible OS ..................................... OK
 Checking required libs are installed ........................................ OK
 unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
 unixODBC Driver Manager version 2.3.0 installed ............................. OK
 unixODBC Driver Manager configuration correct .............................. OK*
 Microsoft ODBC Driver 11 for SQL Server already installed ............ INSTALLED
 See /tmp/msodbcsql.11319.28100.25842/install.log for more information about installation failures.
 
 4、安装 unixODBC 2.3.0
 
 msodbcsql-11.0.2270.0 目录下已经包含了 build_dm.sh 脚本，直接运行可以在线下载 unixODBC。
 
 我们已经下载好了，就可以通过 如下命令直接安装
 
 # ./build_dm.sh --download-url=file://unixODBC-2.3.0.tar.gz
 
 Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.
 .... 此处略去200字
 Enter 'YES' to have this script continue: YES
 Verifying processor and operating system ................................... OK
 Verifying wget is installed ................................................ OK
 Verifying tar is installed ................................................. OK
 Verifying make is installed ................................................ OK
 Downloading unixODBC 2.3.0 DriverManager ................................... OK
 Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
 Configuring unixODBC 2.3.0 DriverManager ................................... OK
 Building unixODBC 2.3.0 DriverManager ...................................... OK
 Build of the unixODBC 2.3.0 DriverManager complete.
 Run the command 'cd /tmp/unixODBC.8000.24204.29056/unixODBC-2.3.0; make install' to install the driver manager.
 PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
 EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
 THIS MAY POTENTIALLY OVERWRITE THAT COPY.
 
 注意上面输出信息提示，还需运行如下脚本进行安装：.
 cd /tmp/unixODBC.12406.19933.26727/unixODBC-2.3.0; make install
 
 5、返回原有目录，运行如下指令，安装 msodbcsql 驱动：
 
 不放心的话，可以再运行一次  ./install.sh verify
 
 ./install.sh install

 我这里已经安装过了，因此 NOT ATTEMPTED 提示不会重新安装
 Enter YES to accept the license or anything else to terminate the installation: YES
 Checking for 64 bit Linux compatible OS ..................................... OK
 Checking required libs are installed ........................................ OK
 unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
 unixODBC Driver Manager version 2.3.0 installed ............................. OK
 unixODBC Driver Manager configuration correct .............................. OK*
 Microsoft ODBC Driver 11 for SQL Server already installed ............ INSTALLED
 Microsoft ODBC Driver 11 for SQL Server files copied ............. NOT ATTEMPTED
 Symbolic links for bcp and sqlcmd created ........................ NOT ATTEMPTED
 Microsoft ODBC Driver 11 for SQL Server registered ............... NOT ATTEMPTED
 See /tmp/msodbcsql.570.21050.3671/install.log for more information about installation failures.
 
 至此所需软件已经安装完毕，最终安装目录： /opt/microsoft/msodbcsql
 
 我们看看都安装了些什么：
 bcp、sqlcmd 是 msodbcsql 提供的命令行交互工具；bcp 用于导数据，sqlcmd 类似于 sqlplus。
 
 # ls -la /usr/bin/bcp /usr/bin/sqlcmd
 lrwxrwxrwx 1 root root 44 Jun 26 09:46 /usr/bin/bcp -> /opt/microsoft/msodbcsql/bin/bcp-11.0.2270.0
 lrwxrwxrwx 1 root root 47 Jun 26 09:46 /usr/bin/sqlcmd -> /opt/microsoft/msodbcsql/bin/sqlcmd-11.0.2270.0
 
 # tree /opt/microsoft/msodbcsql -d
 /opt/microsoft/msodbcsql
 ├── 11.0.2270.0
 │   ├── docs
 │   │   └── en_US
 │   │   ├── html
 │   │   ├── html_chm
 │   │   ├── icons
 │   │   ├── local
 │   │   ├── scripts
 │   │   └── styles
 │   ├── en_US
 │   └── include
 ├── bin
 └── lib64
lib64 目录下就是 msodbcsql 驱动，其实我们要的就是这一个：
 
 # ls -la /opt/microsoft/msodbcsql/lib64/
 total 1728
 drwxr-xr-x 2 root root 4096 Jun 29 17:49 .
 drwxr-xr-x 5 root root 4096 Jun 26 09:46 ..
 -rwxr-xr-x 1 root root 1757680 Jun 29 17:49 libmsodbcsql-11.0.so.2270.0
 
unixODBC 的东西安装在 /usr/lib64 目录下：

 # ls -la /usr/lib64/libodbc*
 -rwxr-xr-x 1 root root 965 Jul 1 09:16 /usr/lib64/libodbccr.la
 lrwxrwxrwx 1 root root 18 Jul 1 09:16 /usr/lib64/libodbccr.so -> libodbccr.so.1.0.0
 lrwxrwxrwx 1 root root 18 Jul 1 09:16 /usr/lib64/libodbccr.so.1 -> libodbccr.so.1.0.0
 -rwxr-xr-x 1 root root 516766 Jul 1 09:16 /usr/lib64/libodbccr.so.1.0.0
 -rwxr-xr-x 1 root root 977 Jul 1 09:16 /usr/lib64/libodbcinst.la
 lrwxrwxrwx 1 root root 20 Jul 1 09:16 /usr/lib64/libodbcinst.so -> libodbcinst.so.1.0.0
 lrwxrwxrwx 1 root root 20 Jul 1 09:16 /usr/lib64/libodbcinst.so.1 -> libodbcinst.so.1.0.0
 -rwxr-xr-x 1 root root 502107 Jul 1 09:16 /usr/lib64/libodbcinst.so.1.0.0
 -rwxr-xr-x 1 root root 953 Jul 1 09:16 /usr/lib64/libodbc.la
 lrwxrwxrwx 1 root root 16 Jul 1 09:16 /usr/lib64/libodbc.so -> libodbc.so.1.0.0
 lrwxrwxrwx 1 root root 16 Jul 1 09:16 /usr/lib64/libodbc.so.1 -> libodbc.so.1.0.0
 -rwxr-xr-x 1 root root 1860769 Jul 1 09:16 /usr/lib64/libodbc.so.1.0.0

查看下 unixODBC 的配置
 
# cat /etc/odbcinst.ini

[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
 
三、配置环境

1、配置 odbc.ini （前面通过 ./build_dm.sh 安装的 unixODBC ，配置目录放在 /etc/下 ）

# vi /etc/odbc.ini

[mssql]
# Driver 可以直接指定 libmsodbcsql-11.0.so.2270.0
# 也可以使用 /etc/odbcinst.ini 中配置好的名称
Driver = ODBC Driver 11 for SQL Server

Server = ip_address,1433
Database = db_name

UNICODE = UCS-2

# 字符集 ，这个很关键 mssql 默认使用的是很坑爹的 UCS-2 ，这个坑把我埋了三天
# 这里如果不指定，也可以放到配置 initmssql.ora 时指定 HS_NLS_NCHAR=UCS2
# 这里我没有设置，我是在 initmssql.ora 中设置的 HS_NLS_NCHAR
# 注意绝对、绝对、绝对不要忘了设置 HS_NLS_NCHAR，官方文档根本就没提这事
# 我一直在绝望孤独中跟伪娘奋斗，最终发现伪娘终归还是娘，有事还是要找哥
# IT人士必备技能之如何爬墙找哥，这里就不讲了，不然你就看不到这篇文章了。

2、编写个 ruby 程序测试一下：

require 'dbi'

dbh = DBI.connect("DBI:ODBC:mssql", 'username', 'password')
dbh.select_all("select top 10 * from some_table ") do |row|
  puts row.to_h
end

测试正常的话就可以进行下一步了。

3、配置 $ORACLE_HOME/hs/admin/initmssql.ora 文件

同一目录下有个 initdg4odbc.ora 例子文件。

这个文件就是用来描述 mssql 数据库的，内容主要有下面几行。

HS_FDS_CONNECT_INFO = mssql
# 名称要同 /etc/odbc.ini 里面保持一致，不然上哪里找数据库

HS_FDS_TRACE_LEVEL = 255
# 设置日志信息等级，数字越大越详细，一定要打开，不然出错是茫然无助，想死的心都有了。
# 日志就在 $ORACLE_HOME/hs/log 目录下，一堆 *.trc 文件

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
# 设置 odbc 的lib ，注意是设置 unixODBC ，也就是 Driver Manager 的 lib
# 重要的事要强调，注意是 Driver Manager 的 libodbc，不要搞错了。linux 下可以用 locate libodbc.so 搜索一下。

HS_NLS_NCHAR=UCS2
# mssql 坑爹的字符集
# 这就是那个埋了我三天的坑，全部都设好了，就差这个，死活不正常

set ODBCINI=/etc/odbc.ini
# 设定 odbc.ini 的目录

#set <envvar>=<value>
# envvar 基本可以忽略

4、配置 listener.ora

对于 oracle 11.2.0.4 以前版本，配置文件就是 $ORACLE_HOME/network/admin/listener.ora。
对于 11.2.0.4 以后版本，引入了 endpoints_listener.ora 文件，同 listener.ora 协同工作。但 endpoints_listener.ora 我们不需要改动。

a、先讲简单的，单实例且版本低于 11.2.0.4 的 oracle 。

su - oracle

cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mssql)
         (ORACLE_HOME=/var/oracle/product/11.2.0.3)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/usr/lib64:/var/oracle/product/11.2.0.3/lib)
      )
  )

只需要添加如上内容即可。
因为 listener 是默认的监听，我们只需要往里面添加一个 SID 即可。
也就是说同一个数据库的 1521 监听端口可以监听多个 sid 。

注意这里的 SID_NAME=mssql 同 initmssql.ora 名称要保持一致，数据库就是通过这个 sid 来寻找 initSID.ora 文件的。
PROGRAM=dg4odbc 不能乱改，dg4odbc 是 oracle 提供的程序用来同 Driver Manager（就是 unixODBC）通讯的。
ENVS 中 /usr/lib64 是 unixODBC 的 lib 目录，/var/oracle/product/11.2.0.3 是 ORACLE_HOME 目录。

配置完毕后即可重启 listener：

lsnrctl stop
lsnrctl start
lsnrctl status

b、对于 oracle RAC 11.2.0.4 版本，不能使用 lsnrctl 管理监听，可以使用 srvctl 。

su - oracle

同样是修改 $ORACLE_HOME/network/admin/listener.ora 文件，添加：

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mssql)
         (ORACLE_HOME=/var/oracle/product/11.2.0.3)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/usr/lib64:/var/oracle/product/11.2.0.3/lib)
      )
  )

srvctl stop   listener -l mssql
srvctl start  listener -l mssql
srvctl status listener -l mssql

以上命令会将多个节点的 listener 全部启动。

另外如果不想使用 1521 端口监听，可以用 netca 新建一个 listener ，然后添加

SID_LIST_yournewLISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mssql)
         (ORACLE_HOME=/var/oracle/product/11.2.0.3)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/usr/lib64:/var/oracle/product/11.2.0.3/lib)
      )
  )

5、万事俱备，该配置 tnsnames.ora 了

initmssql.ora 、listener.ora  都配置好了，接下来就是 tnsnames.ora

TNS_ADMIN 一般都设置成 $ORACLE_HOME/network/admin，除非你有个变态的 dba。

# cat $TNS_ADMIN/tnsnames.ora

mssql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=mssql))
    (HS=OK)
  )

这里需要注意的就两点：

SID = mssql 要同listener.ora 中保持一致
HS=OK 告诉 oracle 这里是个异构数据库。

测试一下：
# tnsping mssql

注意不要尝试用 sqlplus 连接 sql server ，永远也不会成功的。
# sqlplus username/password@mssql

会报如下错误
ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

只能创建 database link 后进行测试。

6、创建 database link

能看到这篇文章的人应该都会，还是记录一下吧：

create public database link mssql connect to "user" identified by "pass" using 'mssql';

注意单双引号，不能乱用，oracle 里面单双引号含义是不同的。

终于可以休息一下了，有了 dblink 世界清静了。
最后肺腑之言，不要做 dba ，不要做运维，你辛辛苦苦的埋了三天，仅仅是为了实现一个不起眼的功能，或许就只用一次，值吗？