-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////// -- 第一部分：Oracle 访问 My SQL -- 参考文档： -- www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml -- it.kswchina/Oracle/zh/506285.html ---------------------------------------------------------------------------------------------------------------- -- 操作系统信息： -- Oracle 服务器 -- 操作系统：Red Hat Enterprise Linux 5.3 -- 数据库： Oracle RDBMS 10.2.0.4.0 -- 主机名： sztyora_b -- DB_Name： sztyora -- MySQL 服务器 -- 操作系统：Red Hat Enterprise Linux 5.3 -- 数据库： My SQL RDBMS 5.1.28 -- 主机名： sztyora_a -- DB_Name： test ---------------------------------------------------------------------------------------------------------------- -- 所需要软件包： -- Oracle：( 第一个是 Oracle 安装包，第二个是 Oracle 升级包，第二个不是必须的 ) 10201_database_linux32.zip p6810189_10204_Linux-x86.zip -- MySQL： MySQL-server-community-5.1.28-0.rhel5.i386.rpm MySQL-devel-community-5.1.28-0.rhel5.i386.rpm MySQL-client-community-5.1.28-0.rhel5.i386.rpm MySQL-shared-community-5.1.28-0.rhel5.i386.rpm MySQL-shared-compat-5.1.28-0.rhel5.i386.rpm MySQL-test-community-5.1.28-0.rhel5.i386.rpm -- MySQL connect 及 unixODBC 及相关包 unixODBC-2.2.11-7.1.i386.rpm -- RHEL 5.3 安装盘自带 mysql-connector-odbc-3.51.27-0.i386.rpm mysql-connector-odbc-3.51.27-0.src.rpm mysql-connector-odbc-debuginfo-3.51.27-0.i386.rpm mysql-connector-odbc-setup-3.51.27-0.i386.rpm libtool-ltdl-1.5.22-6.1.i386.rpm -- RHEL 5.3 安装盘自带 -- Oracle 及 MySQL 等包的安装略 rpm -ivh xxxx.rpm ...... ---------------------------------------------------------------------------------------------------------------- -- Oracle 访问 My SQL ，其主要操作均在 Oracle 服务器上进行 --------- -- Step 1： 在 My SQL 服务器上 创建相应的用户，并对其授权： # mysql -u root mysql mysql> GRANT ALL PRIVILEGES ON test.* TO oracle@192.168.1.112 IDENTIFIED BY 'manager' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; --------- -- Step 2：更改 odbc.ini 文件( 添加如下内容 )： # vi /usr/local/etc/odbc.ini [ODBC Data Sources] myodbc3 = MySQL ODBC 3.51 Driver DSN [test] Driver = /usr/lib/libmyodbc3.so Description = MySQL ODBC 3.51 Driver DSN SERVER = 192.168.1.111 PORT = 3306 USER = oracle Password = manager Database = test OPTION = 3 SOCKET = [Default] Driver = /usr/lib/libmyodbc3.so Description = MySQL ODBC 3.51 Driver DSN SERVER = localhost PORT = USER = oracle Password = Database = test OPTION = 3 SOCKET = --------- -- Step 3：给 /home/oracle/.bash_profile 文件追加如下内容(主要是最后两行及 LD_LIBRARY_PATH ) # Oracle Settings ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME ORACLE_SID=sztyora; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH # NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"; export NLS_LANG # ORA_NLS33=$ORACLE_HOME/nls/data; export ORA_NLS33 ODBCINI=/usr/local/etc/odbc.ini; export ODBCINI ODBCSYSINI=/usr/local/etc; export ODBCSYSINI -- 附：查看 odbc 版本 及 其参数文件路径 [root@sztyora_b ~]# odbcinst -j unixODBC 2.2.11 : /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini USER DATA SOURCES..: /usr/local/etc/odbc.ini --------- -- Step 4：测试 My SQL ODBC 驱动： [root@sztyora_b ~]# su - oracle [oracle@sztyora_b ~]$ isql test oracle manager -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> create table emp (id int, name varchar(100)); 0 rows affected SQL> insert into emp values (100, 'Jeff'); 1 rows affected SQL> insert into emp values (100, 'Melody'); 1 rows affected SQL> commit; 0 rows affected SQL> quit $ --------- -- Step 5：配置 HSODBC 程序 -- *1) 修改监听文件： [oracle@sztyora_b ~]$ vi $ORACLE_HOME/network/admin/a # a Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/a # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sztyora_b)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = test) (ENVS=LD_LIBRARY_PATH = /usr/lib:/u01/app/oracle/product/10.2.0/db_1/lib) ) ) -- 确认 hsodbc 的配置路径是否正确 (如果环境变量包含其正确的资源库路径，则能看到 HSODBC代理的版本号)： [oracle@sztyora_b ~]$ hsodbc Oracle Corporation --- WEDNESDAY JUL 28 2010 10:21:10.425 Heterogeneous Agent Release 10.2.0.4.0 - Production Built with Driver for ODBC -- *2) 重新启动监听： [oracle@sztyora_b ~]$ lsnrctl stop LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-JUL-2010 10:24:38 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sztyora_b)(PORT=1521))) The command completed successfully [oracle@sztyora_b ~]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-JUL-2010 10:24:49 Copyright (c) 1991, 2007, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: TNSLSNR for Linux: Version 10.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/mysql下载appdb_1/network/admin/a Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sztyora_b)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sztyora_b)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 28-JUL-2010 10:24:49 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/a Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sztyora_b)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for Service "test" has 1 instance(s). Instance "test", status UNKNOWN, has 1 handler(s) for The command completed successfully [oracle@sztyora_b ~]$ | 
发表评论