ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10g中通过透明网关、DBLink访问MySQL数据

Oracle10g中通过透明网关、DBLink访问MySQL数据

原创 Linux操作系统 作者:luoyoumou 时间:2011-04-27 23:12:59 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 -- 参考文档:
-- 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 ~]$ 
-- *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 "emp"@testdb;
select * from "emp"@mysql;
 
-- 附:在 Oracle 里面,对 My SQL某表执行增、删、改等操作,其回滚无效,且不能利用 Oracle  MySQL 数据库创建对象 及修改其对象结构!
 
 
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
-- 第二部分:My SQL 访问 Oracle
 
-- 好像 MySQL 目前还不支持连接 Oracle 数据库,本人正在寻找解决方案!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8297086/viewspace-693945/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-10-15

  • 博文量
    1
  • 访问量
    6972