从 Oracle 到 MySQL 的异构配置过程

本地机器：AIX, Oracle(10.2.0.4.0), DM，IP:192.168.1.11
远程机器：Linux, MySQL, DM，IP:192.168.1.10
本文中有两个异构实例dm10和dpo，本篇着重强调dm10这个实例。

1，在本地机器/etc/odbc.ini添加数据源：

[dp]
Description     = dump oracle
Driver          = /app/dm51/lib/libmyodbc3aix.so
#Driver          = /data/dt1/dm51/lib/libmyodbc3_r.so
Server          = localhost
Database        = dp
Port            = 3306
Socket          =
Option          = 7602432
Stmt            =

[dm10]
Description     = Connect to DM5.1 on 192.168.1.10 
Driver          = /app/dm51/lib/libmyodbc3_r.so   //驱动文件
#Driver          = /data/dt1/dm51/lib/libmyodbc3_r.so
Server          = 192.168.1.10      //远程服务器地址
Database        = dp             //远程服务器的数据实例
Port            = 3306            //远程服务器的数据库端口
Socket          =
Option          = 7602432
Stmt            =

[dpo]
Description     = dump oracle
Driver          = /app/dm51/lib/libmyodbc3_r.so
#Driver          = /data/dt1/dm51/lib/libmyodbc3_r.so
Server          = 192.168.1.10
Database        = dp
Port            = 3306
Socket          =
Option          = 7602432
Stmt

2，进入$ORACLE_HOME/hs/admin目录，创建initdm10.ora文件（dm10为/etc/odbc.ini中数据源的名字）：

# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = dm10
HS_FDS_TRACE_LEVEL = 99 
HS_LANGUAGE = AMERICAN_AMERICA.ZHS16GBK

HS_FDS_SHAREABLE_NAME = /app/dm51/dm32/lib/libmyodbc3_r-3.51.25.so
# 注意DM的安装目录
# 此前，你将此值错写成/app/dm51/lib/libmyodbc3_r-3.51.25.so
# 导致了Could not find symbol 'SQLAllocConnect' in dynamic library错误

HS_OPEN_CURSORS = 300
# ODBC specific environment variables
# Environment variables required for the non-Oracle system
set ORACLE_HOME = /home/oracle/ora10g
set ODBCINI = /etc/odbc.ini
set ODBCSYSINI = /etc

3，配置监听。进入$ORACLE_HOME/network/admin目录，编辑listener.ora文件，添加dm10的SID：

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dm10)           //异构实例名
      (ORACLE_HOME = /home/oracle/ora10g) 
      (PROGRAM = hsodbc)                  //固定值
    )
    (SID_DESC =
      (SID_NAME = dpo)
      (ORACLE_HOME = /home/oracle/ora10g)
      (PROGRAM = hsodbc)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/ora10g)
     # (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
  )

4，进入$ORACLE_HOME/network/admin目录，配置tnsname.ora文件，添加dm10的配置：

# Generated by Oracle configuration tools.
.....

DM10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))  //这里填本地的oracle地址及端口
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dm10)    //数据源的名字
    )
    (HS = OK)
  )

DPO = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dpo)
    )
    (HS = OK)
  )

......

5，测试网络互通性：

tnsping dm10

6，创建数据链接：

sqlplus / as sysdba
create database link db123 connect to "root" identified by "dbplus03" using 'dm10';

查询链接是否成功：

select * from zk.zk10@db123;
select count(1) from zk.zk10@db123;

可能出现的错误：

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][C077] Could not find symbol 'SQLAllocConnect'
in dynamic library
[C079] Failed to load dynamic library
'/app/dm51/dm32/lib/libmyodbc3_r-3.51.25.so'

错误原因：驱动文件问题。
解决方法：1，检查本文中第二步中指向的驱动文件是否正确。
2，检查环境变量里的 LIBPATH 和 LD_LIBRARY_PATH 是否已经指向了正确的目录，比如本文中的/app/dm51/目录。

来个正确的环境变量配置：

LIBPATH=/home/oracle/ora10g/lib32:/home/oracle/ora10g/lib:/home/oracle/ora10g/precomp/lib:/app/dm51/dm32/lib:/app/dm51/lib
LD_LIBRARY_PATH=/home/oracle/ora10g/lib:/usr/lib:/home/oracle/ora10g/rdbms/lib:/app/dm51/dm32/lib:/app/dm51/lib