从 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 [email protected];
select count(1) from [email protected];

可能出现的错误:

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

发表评论

您的电子邮箱地址不会被公开。