Oracle10g中通過透明閘道器、DBLink訪問MySQL資料

luoyoumou發表於2011-04-27
OracleMySql
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章