-- 第一部分：Oracle 访问 My SQL
-- 参考文档：
-- http://www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml]http://www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml
-- http://it.kswchina.com/Oracle/zh/506285.html]http://it.kswchina.com/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 mailto:oracle@192.168.1.112]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
DRIVERS............: /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/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# 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: please wait...
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
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/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sztyora_b)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@sztyora_b ~]$
-- *3) 配置 Oracle TNS Listener (添加如下内容)
[oracle@sztyora_b ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# HSODBC.IDEVELOPMENT.INFO =
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 127.0.0.1)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
-- 最后 tnsnames.ora 的内容如下：
# HSODBC.IDEVELOPMENT.INFO =
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 127.0.0.1)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
SZTYORA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sztyora_b)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sztyora)
)
)
LISTENER_SZTYORA =
(ADDRESS = (PROTOCOL = TCP)(HOST = sztyora_b)(PORT = null))
-- 确认 tnsnames.ora 的配置是否正确：
[oracle@sztyora_b ~]$ tnsping test
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 28-JUL-2010 10:30:55
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (10 msec)
-- *4) 配置初始化参数文件：
-- init.ora of the gateway
-- The SID name used in this article to configure Oracle HS functionality is called myodbc3.
-- This was defined in the ODBC driver manager configuration file: /usr/local/etc/odbc.ini.
-- There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail).
-- A short note about the name of the SID — don't use dots in the SID and keep it short!
-- The SID is also relevant for the init.ora file of the gateway. The name of the file is init.ora.
-- In this article it is called initmyodbc3.ora. The file should be located in $ORACLE_HOME/hs/admin.
-- It should contain the following entries:
[oracle@sztyora_b ~]$ mkdir -p $ORACLE_HOME/hs/admin
[oracle@sztyora_b admin]$ vi $ORACLE_HOME/hs/admin/inittest.ora
# this is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini
#
-- *5) 测试 HSODBC 配置
SQL> DROP PUBLIC DATABASE LINK testdb;
SQL> DROP PUBLIC DATABASE LINK mysql;
SQL> CREATE PUBLIC DATABASE LINK testdb
CONNECT TO "oracle" IDENTIFIED BY "manager" USING 'TEST';
SQL> CREATE PUBLIC DATABASE LINK MYSQL
connect to "oracle" identified by "manager"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
(CONNECT_DATA = (SID= test) )
(HS=OK)
)';
select * from mailto:]"emp"@testdb;
select * from mailto:]"emp"@mysql;
-- 附：在 Oracle 里面，对 My SQL某表执行增、删、改等操作，其回滚无效，且不能利用 Oracle 对 MySQL 数据库创建对象 及修改其对象结构！

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- 第二部分：My SQL 访问 Oracle
-- 好像 MySQL 目前还不支持连接 Oracle 数据库，本人正在寻找解决方案！