linux64位系统Oracle11g异构mysql透明网关搭建过程（dg4odbc）

linux64位系统Oracle11g异构mysql透明网关搭建过程（dg4odbc）

第一部分ODBC配置

一、所需的包

mysql-connector-odbc-5.1.8-1.rhel5.x86_64.rpm

mysql-connector-odbc-5.1.8-1.rhel5.i386.rpm

unixODBC-2.2.11-7.1.x86_64.rpm

unixODBC-2.2.11-7.1.i386.rpm

.

二、  配置Oracle服务器的/etc/odbc.ini文件,测试ODBC工作

（1）vi /etc/odbc.ini，填入如下内容：

[myodbc]
Descriptio      = mysql
Driver          = mysql
Server          = 123.25.12.5
USER            = root
PASSWORD        = mysql123
Database        = test
Port            = 3306
Socket          =/var/lib/mysql/mysql.sock
Option          = 3
Charset          = gbk
Stmt            =

（2）vi etc/odbcinst.ini，为如下内容：

[mysql]
Description     = mysql
Driver          = /usr/lib/libmyodbc5.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Setup64         = /usr/lib64/libodbcmyS.so
UsageCoun       = 1
CPTimeout       =
CPReuse         =

（3）测试 isql -v myodbc

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

能够连接上表示ODBC配置成功

如果报如下错误：
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
通过设置环境变量来解决

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

第二部分ORACLE配置

一、在/opt/oracle/product/OraHome/hs/admin/目录下，配置init+myodbc.ora文件，  init myodbc.ora命名方式为init+服务名+.ora。添加如下内容

# HS init parameters
#
HS_FDS_CONNECT_INFO = myodbc
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK

HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291

# ODBC specific environment variables
#
#set DBCINI=<full path name of the odbc initilization file>
set DBCINI=/etc/odbc.ini

HS_FDS_CONNECT_INFO为服务名，与前面保持一致

HS_FDS_TRACE_LEVEL为日志跟踪级别，使用时可以设为debug

HS_FDS_SHAREABLE_NAME为ODBC驱动

二、修改tnsnames.ora文件，添加如下内容

MYODBC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYODBC)
    )
    (HS = OK)
  )

其中SID为前面定义的服务名

HOST为本机的机器名或者IP

PORT为本机的数据库监听器端口

 

三、修改listener.ora文件，如下内容

# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
          (SID_NAME = myodbc)
          (ORACLE_HOME=/opt/oracle/product/OraHome)
          (PROGRAM = dg4odbc)
    )
  )


SZNDCRM =
     (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
      (ADDRESS=  (PROTOCOL=ipc)(KEY=PNPKEY))
  )



ADR_BASE_SZNDCRM = /opt/oracle

SID_NAME为服务名

PROGRAM为使用dg4odbc驱动

 

四、重新启动listener，基本内容如下：

lsnrctl stop
lsnrctl start

lsnrctl status

Service "mysql" has 1 instance(s).

  Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...

如果看到mysql这个服务，则表示listener.ora配置成功

 

五、创建oracle中创建数据库链接

sqlplus scott/tiger

create database link ln_mysql connect to "root" identified by "mysql123" using 'myodbc';

 

测试查看mysql中的表

select * from "mytest"@ln_mysql;

 c1 c2
---------- ----------
1    a
2    b

 

注：

（1）在搭建好之后导数据时候发现

1 2 3 4 5 6 

中间总是有一个空格

解决办法是在odbc.ini中加入

Charset  = gbk

（2）如果搭建过程中，遇到

ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYODBC3

这样的类似错误，请检查你的listener.ora，以确认无误，之后在进行尝试。

（3）如果搭建过程中，遇到

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2112): ;

[unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc3.so' :

/usr/lib64/libmyodbc3.so: cannot open shared object file: No such file or

directory (SQL State: 01000; SQL Code: 0)

ORA-02063: preceding 2 lines from MYSQLORA

将/etc/odbcinst.ini中的Driver = /usr/lib64/libmyodbc3.so改为Driver = /usr/lib/libmyodbc3.so或者是重装32位的mysql-connector-odbc-3.51.27-0.i386.rpm进行解决。

(4) 如果搭建过程中，遇到

ERROR 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=s1.db.sns.mdc.139.com)(PORT=1521))

(CONNECT_DATA=(SID=my23_3322)))

ORA-02063: preceding line from LN_MY23_3322

Process ID: 18585

Session ID: 96 Serial number: 207

在initmy23_3322.ora文件中添加如下参数

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

 

(5) 从异种数据库拷贝大数据量时，报错如下

insert into auth_user select * from auth_user@ln_my23_3322

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 19180

Session ID: 96 Serial number: 227

 

有如下报错：

ORA-07445: exception encountered: core dump [npixfc()+243] [SIGSEGV] [ADDR:0x7FFF086B7C08] [PC:0x59BAB0D] [Address not mapped to object] []

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=s1.db.sns.mdc.139.com)(PORT=1521))(CONNECT_DATA=(SID=my23_3322)))

ORA-02063: preceding line from LN_MY23_3322

机器内存有限无法再提供更多的内存，这可能是个BUG，暂没有找到解决办法

 

转自 http://yinxiulei.cn

转载于:https://www.cnblogs.com/xl-yin/articles/dg4odbc.html