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，暂没有找到解决办法
