-- Oracle 远程访问 My SQL --

luoyoumou 2010-07-28 03:01:05

加精

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////



-- 第一部分：Oracle 访问 My SQL



-- 参考文档：

-- http://www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml

-- 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 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 ~]$