Oracle 通过透明网关访问mysql配置步骤
来源:互联网 发布:战舰世界弗林特数据 编辑:程序博客网 时间:2024/05/21 10:44
http://blog.itpub.net/21601207/viewspace-709366
Oracle 通过透明网关访问mysql配置步骤
一、当前环境
1. OS
more /etc/redhat-release
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
2. Oracle
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3. Mysql
5.1.46-community-log MySQL Community Server
4. IP
机器名
IP
数据库
A
s1.db.sns.mdc.139.com
192.168.2.235
Oracle
B
backup.139.com
192.168.2.23
mysql
二、所需的包
mysql-connector-odbc-5.1.8-1.rhel5.x86_64.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
unixODBC-2.2.11-7.1.i386.rpm
三、配置过程
1. 在机器A上安装相关包
rpm –ivh mysql-connector-odbc-5.1.8-1.rhel5.x86_64.rpm
yum install unixODBC
2. 在机器B上创建mysql用户并授权
grant select on user_global.* to my23_3322@192.168.2.235 identified by 'my23_3322';
3. 配置机器A上的 /etc/odbc.ini文件,添加如下内容
[my23_3322]
Driver = /usr/lib64/libmyodbc5.so
Server = 192.168.2.23
User = my23_3322
Password = my23_3322
Port = 3322
database = user_global
其中,my23_3322 为服务名,后面将会用到
/usr/lib64/libmyodbc5.so 为odbc 驱动
192.168.2.23 为要访问mysql 所在IP
my23_3322 登陆mysql 服务器用户名
3322 为mysql 数据库端口
4. 在机器A使用isql 命令进行测试
isql my23_3322
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
能够连接上表示ODBC配置成功
下面进行oracle 方面的配置,如无特殊说明均在机器A上配置
5. 在/opt/oracle/11.2/db_1/hs/admin/目录下,配置init my23_3322.ora文件, init my23_3322.ora命名方式为init+服务名+.ora。添加如下内容
# HS init parameters
#
HS_FDS_CONNECT_INFO = my23_3322
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
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 为日志跟踪级别,不使用时可以设为OFF
HS_FDS_SHAREABLE_NAME 为ODBC驱动
HS_LANGUAGE 为 Provides Heterogeneous Services with character set, language, and territory information of the non-Oracle data source. The value must use the following format:language[_territory.character_set] 。为异构服务器的字符集,语言
HS_FDS_SQLLEN_INTERPRETATION 允许64bit 驱动可以使用32bit 标准,具体解释可以参考oracle 文档ID 554409.1
6. 修改tnsnames.ora 文件,添加如下内容
my23_3322 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s1.db.sns.mdc.139.com)(PORT = 1521))
(CONNECT_DATA =
(SID = my23_3322)
)
(hs=ok)
)
其中SID为前面定义的服务名
HOST为本机的机器名或者IP
PORT为本机的数据库监听器端口
7. 修改listener.ora 文件,添加如下内容
(SID_DESC =
(SID_NAME = my23_3322)
(ORACLE_HOME = /opt/oracle/11.2/db_1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/opt/oracle/11.2/db_1/lib:/opt/oracle/11.2/db_1/odbc/lib)
)
SID_NAME为服务名
PROGRAM 为使用dg4odbc 驱动
8. 重新启动listener ,基本内容如下:
lsnrctl status
Service "my23_3322" has 1 instance(s).
Instance "my23_3322", status UNKNOWN, has 1 handler(s) for this service...
如果看到my23_3322 这个服务,则表示listener.ora 配置成功
9. 创建oracle 中创建数据库链接
sqlplus scott/tiger
create database link ln_my23_3322 connect to "my23_3322" identified by "my23_3322" using 'my23_3322';
10. 测试
SQL> select count(1) from tmp_reg_ip@ln_my23_3322;
COUNT(1)
----------
3812
Elapsed: 00:00:00.07
SQL> select * from dual@ln_my23_3322;
D
-
X
四、安装过程中遇到的问题
1. 配置完成后,执行SQL报如下错误
SQL> select * from dual@ln_my23_3322;
select * from dual@ln_my23_3322
*
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
参考Oracle ID 555355.1
2. 从异种数据库拷贝大数据量时,报错如下
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
Alert 文件有如下报错:
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
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_13360/ora11g_ora_17512_i13360.trc
现象为服务器内存(4G)被吃光,数据库分配2G内存。机器内存有限无法再提供更多的内存,这可能是个BUG,暂没有找到解决办法,郁闷。。。。。。
五、透明网关有如下限制
1. BLOB and CLOB data cannot be read by pass-through queries
2. Updates or deletes that include unsupported functions within a WHERE clause are not allowed
3. Does not support stored procedures
4. Cannot participate in distributed transactions; they support single-site transactions only
5. Does not support multithreaded agents
6. Does not support updating LONG columns with bind variables
7. Does not support rowids
8. COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
9. UPDATE and DELETE statements with the WHERE CURRENT OF clause are not supported by the gateway because they rely on the Oracle implementation.
10. The gateway does not support the CONNECT BY clause in a SELECT statement.
11. The Oracle ROWID implementation is not supported.
12. EXPLAIN PLAN Statement
13. The gateway does not support the START WITH condition and NOWAIT in a SELECT statement.
六、参考文档
Support.oracle.com
Database Gateway Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64
Oracle Database Gateway for ODBC User’s Guide
- Oracle 通过透明网关访问mysql配置步骤
- oracle通过透明网关访问SQLSERVER数据库
- 配置 Oracle 透明网关访问 SQL Server
- 配置Oracle到MySQL透明网关
- oracle透明网关配置
- oracle透明网关通过dblink连接slq server和mysql
- oracle 11g透明网关配置(访问ms sqlserver2008)
- Oracle 通过网关连接MySql的配置
- oracle透明网关(sqlserver、mysql)
- ORACLE的透明网关配置
- ORACLE11G通过透明网关访问SQLServer2008数据
- oracle 10g 透明网关设置步骤
- oracle通过透明网关(Oracle Transparent Geteways),访问ms sql server和其他数据库
- oracle通过透明网关(Oracle Transparent Geteways),访问ms sql server和其他数据库
- Oracle 11g R2通过透明网关连接DB2 详细步骤
- oracle 10g透明网关访问sqlserver2000
- oracle 10g透明网关访问sqlserver2000
- oracle 10g透明网关访问sqlserver
- 第一部分(5)
- 转:在Eclipse中使用JUnit4进行单元测试(高级篇)
- 解决android上java.net.UnknownHostException: Unable to resolve host "xxxx": No address associated wit
- awd
- Android 导入项目时出现的各类错误(红色感叹号)
- Oracle 通过透明网关访问mysql配置步骤
- 【练习】 例9-13 没结果
- 给宝宝起名要遵循的几点建议
- 河南省第三届省赛 AMAZING AUCTION
- SQL语句的添加、删除、修改多种方法
- 2014年你需要掌握的20条社会化媒体技巧
- XCode 同一Project创建多个target
- 查看linux下oracle链接配置
- C++纯手工打造COM:COM之来龙去脉——组件如何被创建