Oracle透明閘道器訪問Mysql—luckyfriends
|
密 級：機密 |
|
總頁數：3頁 |
![clip_image002[4]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618453PjTf.gif "clip_image002[4]")
Oracle透明閘道器訪問Mysql
(V 1.0)
文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。
2012年8月
版本資訊
|
日期 |
版本 |
描述 |
作者/修改人 |
備註 |
|
2012-8-31 |
1.0 |
建立 |
Jusin Hao |
|
目 錄
5.3.1. plsqldeveloper把unicode勾選去掉... 26
1. 介紹
1.1. 編寫目的
本文件用於記錄******安裝操作過程。
1.2. 文件說明
本文件包含****的安裝操作等內容。
1.3. 定義
1.4. 參考文件
2. 概述
隨著我們資料庫從oracle 向mysql 遷移，一個新問題出來， 一個應用的一部分在 oracle端，一個部分在mysql端，
而一個需求又恰恰是要關聯兩邊的表做查詢，怎麼做？
一個方法，把其中一部分搬到另外一個庫裡去做表關聯查詢。
另一個就異構資料庫的關聯查詢了， 這裡介紹下oracle 關聯mysql 的配置：
經過一天半的測試終於成功了。
首先 oracle 的異構資料庫是透過gateway 來實現的， 有兩種，一個是透明閘道器,專門針對不同的資料庫有不同的軟體包
一個綜合閘道器，通吃一切。
hsodbc 屬於後者。
hsodbc 是一個32位程式，即便是在64位的oracle 安裝裡也是一個32位的程式，估計是oracle 不準備更新了
hsodbc 是透過odbc 來實現與異構資料庫的關聯的。
所以要主機上安裝odbc的管理包
windows 上不用了，都有
unix / linux 下有uinxODBC 的包
因為要跟mysql關聯所以要在oracle 的主機上安裝mysql的客戶端驅動。 mysql-connect-odbc
注意：
因為hsodbc 為32位程式，所以這倆程式包要有對應的32位的程式。 否則報錯。
3. 配置Oracle透過dg4odbc訪問一個mysql資料庫
3.1. 準備
3.1.1. Grant priv to host[root@mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.6.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.12.2.64' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.04 sec)
mysql>FLUSH PRIVILEGES;
3.1.2. We shall start from determining if [Oracle] and [DG4ODBC] are 32/64-bits:[oracle@myps ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/product/11.2.0.3/db_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
3.2. 下載並安裝ODBC Driver Manager(unixODBC)
下載地址：
unixODBC is currently availible in a gzip, tar format. This means that you should;
1. copy the file somewhere you can create files and directories
2. gunzip unixODBC*.tar.gz
3. tar xvf unixODBC*.tar
Doing so will create a unixODBC directory with all source files inside.
安裝配置
[root@myps unixODBC-2.3.2]# cd ..
[root@myps app]# pwd
/home/oracle/app
[root@myps app]# tar -zxvf unixODBC-2.3.2\ .tar.gz
[root@myps unixODBC-2.3.2]# cd unixODBC-2.3.2
[root@myps unixODBC-2.3.2]# ./configure --prefix=/home/oracle/app/unixODBC-2.3.2
[root@myps unixODBC-2.3.2]# make
[root@myps unixODBC-2.3.2]# make install
[root@myps unixODBC-2.3.2]# mkdir /home/oracle/app/etc
[root@myps unixODBC-2.3.2]# ./configure --sysconfdir=/home/oracle/app/etc
3.2.1. 安裝配置參考Make the Libraries and Programs
The install uses the standard GNU autoconf process. So its simply a matter of running
./configure
make
make install
By default the files are installed into /usr/local. As is usual with configure, this location can be changed by altering the prefix option to configure. i.e.
./configure --prefix=/usr/local/unixODBC
This will install the lib, bin, include and etc directories in /usr/local/unixODBC/lib etc.
To conform with the GNU guidelines the odbcinst file is now installed by default in {prefix}/etc, this can be altered using the --sysconfdir option to configure. To install the files in the old default /etc you would run configure like this
./configure --sysconfdir=/etc
3.2.2. 例：如下是預設安裝[root@myps unixODBC-2.3.2]# ./configure --enable-gui=no
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
[root@myps unixODBC-2.3.2]# make
[root@myps unixODBC-2.3.2]# make install
3.3. 下載並安裝配置Getting and installing [ODBC Driver].
下載需要登入（使用Oracle賬號即可）
http://dev.mysql.com/downloads/connector/odbc/#downloads
mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit.tar
安裝配置：
[root@myps app]# cd /home/oracle/app
[root@myps app]# tar -zxvf mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit.tar.gz
[root@myps app]# ln -s mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit mysql-odbc-526
[root@myps app]# ls
mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit mysql-odbc-526 unixODBC-2.3.2 .tar.gz
mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit.tar.gz unixODBC-2.3.2
[root@myps app]# ls -alt
total 9504
drwxr-xr-x 4 root root 4096 Dec 13 11:19 .
lrwxrwxrwx 1 root root 48 Dec 13 11:19 mysql-odbc-526 -> mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit
drwxr-xr-x 5 root root 4096 Dec 13 11:18 mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit
drwx------ 6 oracle dba 4096 Dec 13 11:12 ..
-rw-r--r-- 1 root root 1849173 Dec 13 11:11 unixODBC-2.3.2 .tar.gz
-rw-r--r-- 1 root root 7845412 Dec 13 11:11 mysql-connector-odbc-5.2.6-linux-rhel5-x86-64bit.tar.gz
drwxrwxr-x 20 1000 1000 4096 Oct 8 17:10 unixODBC-2.3.2
[root@myps app]#
3.4. Configuring ODBC data source for MySQL Connector/ODBC driver
[root@myps app]# vi odbc.ini
DATABASE = test
SERVER = 10.12.2.215
[mysql215]
Driver = /home/oracle/app/mysql-odbc-526/lib/libmyodbc5.so
DESCRIPTION = Connector/ODBC 5.6 Driver DSN
SERVER = 10.12.2.215
PORT = 3306
USER = root
PASSWORD = root
DATABASE = test
OPTION =0
#TRACEFILE = /u01/app/oracle/myodbc-demodsn.trc
TRACE = ON
~
[oracle@myps bin]$ export ODBCINI=/home/oracle/app/etc/odbc.ini
[oracle@myps ~]$ export LD_LIBRARY_PATH=/home/oracle/unixODBC-2.3.2/lib:$LD_LIBRARY_PATH
[oracle@myps bin]$ cd /home/oracle/app/unixODBC-2.3.2/bin
[oracle@myps bin]$ ./isql -v mysql215
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database |
+-----------------------------------------------------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+-----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test |
+-----------------------------------------------------------------+
| emp |
+-----------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
export ODBCINI 要設定正確，否則報如下異常；
[oracle@myps bin]$ isql -v mysql215
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
3.5. Configuring tnsnames.ora.
[oracle@myps admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PS92TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PS92TEST)
)
)
mysql215 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysql215)
)
(HS = OK)
)
3.6. Configuring listener.ora
[oracle@myps admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = mysql215)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = dg4odbc)
(ENVS ="LD_LIBRARY_PATH=/home/oracle/app/unixODBC-2.3.2/lib:/u01/app/oracle/product/11.2.0.3/db_1/lib:/usr/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myps.testdomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
3.7. Configuring gateway init.ora file
[oracle@myps admin]$ more initmysql215.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql215
HS_FDS_TRACE_LEVEL = user
HS_FDS_SHAREABLE_NAME = /home/oracle/app/unixODBC-2.3.2/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#
# ODBC specific environment variables
#
set ODBCINI= /home/oracle/app/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
#set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/lib: /home/oracle/app/unixODBC-2.3.2/lib/libodbc.so:/u01/app/oracle/product/11.2.0.3/db_1/hs/lib
3.8. Applying the settings in the configuration files.
The listeners must be restarted in order to pick up the changes we just made into tnsnames.ora, listener.ora and initmyodbc5.ora:
[oracle@myps admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2013 13:21:40
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myps.testdomain.com)(PORT=1521)))
The command completed successfully
[oracle@myps admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2013 13:21:46
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.3/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myps.testdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myps.testdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-DEC-2013 13:21:46
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/11.2.0.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myps.testdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mysql215" has 1 instance(s).
Instance "mysql215", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@myps admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2013 13:21:49
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myps.testdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-DEC-2013 13:21:46
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myps.testdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mysql215" has 1 instance(s).
Instance "mysql215", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@myps admin]$ tnsping mysql215
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2013 13:21:59
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = mysql215)) (HS = OK))
OK (10 msec)
3.9. Creating the database link and getting the data.
[oracle@myps lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 13 13:22:11 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create public database link lk_mysql215 connect to "root" identified by "root" using 'mysql215';
Database link created.
SQL> select * from "emp"@lk_mysql215;
a
--------------------------------------------------------------------------------
1
3.10.參考：
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文件 ID 1320645.1)
http://blog.csdn.net/lwei_998/article/details/7383844
Master Note for Oracle Gateway Products (Doc ID 1083703.1)
- this note gives general information about the Gateways.
You don't say on which platform you are running but the following notes describe the DG4ODBC setup on different platfroms -
How to Setup DG4ODBC on Linux x86 32bit (Doc ID 466228.1)
How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) (Doc ID 561033.1)
How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)
How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems (Doc ID 1266572.1)
Gateway Configuration Utility for Database Gateway for ODBC - DG4ODBC - to Connect to Non-Oracle Databases For Example - DB2, SQL*Server, Sybase, Informix, MySQL (Doc ID 1274143.1)
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)
4. 配置Oracle透過dg4odbc訪問多個mysql資料庫
4.1. Configuring ODBC data source for MySQL Connector/ODBC driver
[root@myps etc]# more odbc.ini
[mysql215_test]
Driver = /home/oracle/app/mysql-odbc-526/lib/libmyodbc5w.so
DESCRIPTION = Connector/ODBC 5.6 Driver DSN
SERVER = 10.12.2.215
PORT = 3306
USER = root
PASSWORD = root
DATABASE = test
OPTION =0
#TRACEFILE = /u01/app/oracle/myodbc-demodsn.trc
TRACE = ON
[mysql215_mysql]
Driver = /home/oracle/app/mysql-odbc-526/lib/libmyodbc5w.so
DESCRIPTION = Connector/ODBC 5.6 Driver DSN
SERVER = 10.12.2.215
PORT = 3306
USER = root
PASSWORD = root
DATABASE = mysql
OPTION =0
#TRACEFILE = /u01/app/oracle/myodbc-demodsn.trc
TRACE = ON
[oracle@myps ~]$ export ODBCINI=/home/oracle/app/etc/odbc.ini
[oracle@myps ~]$ export LD_LIBRARY_PATH=/home/oracle/unixODBC-2.3.2/lib:$LD_LIBRARY_PATH
[oracle@myps ~]$ cd app/unixODBC-2.3.2/bin
[oracle@myps bin]$ ./isql -v mysql215_test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
[oracle@myps bin]$ ./isql -v mysql215_mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
4.2. Configuring tnsnames.ora.
[oracle@myps admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PS92TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PS92TEST)
)
)
mysql215_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysql215_test)
)
(HS = OK)
)
mysql215_mysql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysql215_mysql)
)
(HS = OK)
)
4.3. Configuring listener.ora
[oracle@myps admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PS92TEST)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = PS92TEST)
)
(SID_DESC=
(SID_NAME = mysql215_test)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = dg4odbc)
(ENVS ="LD_LIBRARY_PATH=/home/oracle/app/unixODBC-2.3.2/lib:/u01/app/oracle/product/11.2.0.3/db_1/lib:/usr/lib")
)
(SID_DESC=
(SID_NAME = mysql215_mysql)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = dg4odbc)
(ENVS ="LD_LIBRARY_PATH=/home/oracle/app/unixODBC-2.3.2/lib:/u01/app/oracle/product/11.2.0.3/db_1/lib:/usr/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
4.4. Configuring gateway init.ora file
[oracle@myps admin]$ cd /u01/app/oracle/product/11.2.0.3/db_1/hs/admin
[oracle@myps admin]$ more initmysql215_test.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql215_test
HS_FDS_TRACE_LEVEL = user
HS_FDS_SHAREABLE_NAME = /home/oracle/app/unixODBC-2.3.2/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#
# ODBC specific environment variables
#
set ODBCINI= /home/oracle/app/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
#set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/lib:/usr/lib64:/u01/app/oracle/product/11.2.0.3/db_1/hs/lib
[oracle@myps admin]$ more initmysql215_mysql.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql215_mysql
HS_FDS_TRACE_LEVEL = user
HS_FDS_SHAREABLE_NAME = /home/oracle/app/unixODBC-2.3.2/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#
# ODBC specific environment variables
#
set ODBCINI= /home/oracle/app/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
#set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/lib:/usr/lib64:/u01/app/oracle/product/11.2.0.3/db_1/hs/lib
4.5. Applying the settings in the configuration files.
[oracle@myps admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-DEC-2013 13:06:43
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.2.185)(PORT=1521)))
The command completed successfully
[oracle@myps admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-DEC-2013 13:06:49
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.3/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.12.2.185)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.2.185)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 17-DEC-2013 13:06: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/11.2.0.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.12.2.185)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PS92TEST" has 1 instance(s).
Instance "PS92TEST", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql215_mysql" has 1 instance(s).
Instance "mysql215_mysql", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql215_test" has 1 instance(s).
Instance "mysql215_test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@myps admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-DEC-2013 13:06:57
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.2.185)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 17-DEC-2013 13:06:49
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/myps/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.12.2.185)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PS92TEST" has 1 instance(s).
Instance "PS92TEST", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql215_mysql" has 1 instance(s).
Instance "mysql215_mysql", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql215_test" has 1 instance(s).
Instance "mysql215_test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@myps admin]$ tnsping mysql215_test
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-DEC-2013 13:07:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = mysql215_test)) (HS = OK))
OK (10 msec)
[oracle@myps admin]$ tnsping mysql215_mysql
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-DEC-2013 13:07:13
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = mysql215_mysql)) (HS = OK))
OK (0 msec)
4.6. Creating the database link and getting the data.
SQL> create public database link lk_mysql215_test connect to "root" identified by "root" using 'mysql215_test';
Database link created.
SQL> select * from "emp"@lk_mysql215_test;
a
--------------------------------------------------------------------------------
1
SQL> create public database link lk_mysql215_mysql connect to "root" identified by "root" using 'mysql215_mysql';
SQL> select count(1) from "proxies_priv"@lk_mysql215_mysql;
COUNT(1)
----------
2
5. 問題記錄
5.1. 查詢方式：表、欄位都需要加雙引號
SQL> select a
2 from "emp"@lk_mysql215_test
3 ;
select a
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
SQL> select count(1)
2 from "emp"@lk_mysql215_test t1
3 LEFT OUTER JOIN "emp"@lk_mysql215_test t2 on (t1.a = t2.a);
LEFT OUTER JOIN "emp"@lk_mysql215_test t2 on (t1.a = t2.a)
*
ERROR at line 3:
ORA-00904: "T2"."A": invalid identifier
SQL> select * from "emp"@lk_mysql215_test;
a
--------------------------------------------------------------------------------
1
SQL> select "a" from "emp"@lk_mysql215_test;
a
--------------------------------------------------------------------------------
1
SQL> select t1."a", t2."a"
2 from "emp"@lk_mysql215_test t1
3 LEFT OUTER JOIN "emp"@lk_mysql215_test t2 on (t1."a" = t2."a");
a a
--------------------------------------------------------------------------------
1 1
參考：
5.2. ORA-00972: identifier is too long
建的那個 dblink不要名字太長，遇上表名太長的話，會出下面的問題
SQL> select * from "VERYLONGTABLENAMEMOREThan30characters"@demo;
select * from "VERYLONGTABLENAMEMOREThan30characters"@demo
*
ERROR at line 1:
ORA-00972: identifier is too long
Resolution:
An Oracle database does not allow object names with more then 30 characters. A gateway let a foreign database behave like an Oracle database; thus long object names like table or column names are not allowed. A simple workaround is to create a view on the remote database with less then 30 characters and work with the view.
5.3. 在plsql裡查詢有亂碼
如下所示在plsqldeveloper裡查詢有亂碼；
![clip_image003[6]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_13926184542D14.jpg "clip_image003[6]")
但是sqlplus裡查詢沒有亂碼
![clip_image005[6]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_13926184540Om7.jpg "clip_image005[6]")
我想是因為mysql的字符集 和這邊不匹配；
5.3.1. plsqldeveloper把unicode勾選去掉
![clip_image007[6]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618455A22M.jpg "clip_image007[6]")
但是中文還是亂碼；
5.3.2. 中文亂碼解決查詢資料庫字符集設定，利用Navicat工具：
![clip_image009[6]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618455VUEs.jpg "clip_image009[6]")
latin1 -- cp1252 West European
利用工具做修改：
![clip_image011[6]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618455bVjH.jpg "clip_image011[6]")
登入Oracle資料庫檢視資料庫字符集
SQL> set linesize 1500
SQL> set pagesize 5000
SQL> col parameter format a30
SQL> select * from NLS_DATABASE_PARAMETERS ;
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 11.2.0.3.0
修改$ORACLE_HOME/hs/admin/initsid.ora裡的引數設定，如下修改：
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
但是sqlplus和plsql developer裡查詢報如下異常；
SQL> select t1."a", t2."a"
2 from "emp"@lk_mysql215_test t1
3 ;
from "emp"@lk_mysql215_test t1
*
ERROR at line 2:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
登入mysql資料庫test
mysql> connect test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 84
Current database: test
mysql> select * from epmp;
ERROR 1146 (42S02): Table 'test.epmp' doesn't exist
mysql> select * from emp;
+--------+
| a |
+--------+
| 1 |
| 浣犲ソ |
+--------+
2 rows in set (0.00 sec)
檢視mysql資料庫字符集：
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> SET character_set_client = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_results = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp;
+--------+
| a |
+--------+
| 1 |
| 浣犲ソ |
+--------+
2 rows in set (0.00 sec)
mysql> show create table emp;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`a` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set character_set_server='utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | gbk_chinese_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
檢視mysql伺服器作業系統字符集
[root@myps ~]# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
[root@mysql ~]# export LC_ALL=zh_CN.gbk
[root@mysql ~]# mysql -u root -p
Enter password:
mysql> connect test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 95
Current database: test
mysql> select * from emp;
+------+
| a |
+------+
| 1 |
| 你好 |
| asdf |
+------+
3 rows in set (0.00 sec)
mysql> insert into emp values ('');
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+------+
| a |
+------+
| 1 |
| 你好 |
| asdf |
| 好 |
+------+
4 rows in set (0.00 sec)
修改如下檔案，設定CHARSET = utf8
[oracle@myps etc]$ vi odbc.ini
CHARSET = utf8
修改init引數檔案增加紅色部分
[oracle@myps admin]$ vi initmysql215_test.ora
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 （原來的）
#HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
注意：HS_NLS_NCHAR = UCS2不加查詢的額時候會報ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
![clip_image013[4]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618456yPc8.jpg "clip_image013[4]")
參考：
ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
5.4. 透過odbc client連線mysql報Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
如下所示：
[oracle@myps bin]$ ./isql -v mysql215_test
[08S01][unixODBC][MySQL][ODBC 5.2(w) Driver]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[ISQL]ERROR: Could not SQLConnect
[oracle@myps bin]$
檢視資料庫狀態
[root@mysql init.d]# /etc/init.d/mysql status
MySQL running (11121)[ OK ]
[root@mysql ~]# /etc/init.d/mysql stop
Shutting down MySQL..[ OK ]
[root@mysql ~]# /etc/init.d/mysql start
Starting MySQL.......[ OK ]
[root@mysql ~]# /etc/init.d/mysql status
MySQL running (2868)[ OK ]
[root@mysql ~]#
資料庫伺服器上的sock檔案
[root@mysql mysql]# more mysql.sock
mysql.sock: No such device or address
[root@mysql mysql]# pwd
/var/lib/mysql
[root@mysql mysql]# ls -alt
total 110764
-rw-r----- 1 mysql root 8467 Dec 13 14:03 mysql.err
drwxr-xr-x 5 mysql mysql 4096 Dec 13 14:02 .
-rw-rw---- 1 mysql mysql 50331648 Dec 13 14:02 ib_logfile0
-rw-rw---- 1 mysql mysql 5 Dec 13 14:02 mysql.pid
srwxrwxrwx 1 mysql mysql 0 Dec 13 14:02 mysql.sock
-rw-rw---- 1 mysql mysql 12582912 Dec 13 14:02 ibdata1
drwxr-xr-x 2 mysql mysql 4096 Dec 12 16:16 test
-rw-rw---- 1 mysql mysql 56 Dec 12 13:49 auto.cnf
-rw-r--r-- 1 root root 113 Dec 12 13:24 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 113 Dec 12 13:24 RPM_UPGRADE_MARKER-LAST
drwx--x--x 2 mysql mysql 4096 Dec 12 13:24 mysql
drwx------ 2 mysql mysql 4096 Dec 12 13:24 performance_schema
-rw-rw---- 1 mysql mysql 50331648 Dec 12 13:24 ib_logfile1
drwxr-xr-x 32 root root 4096 Dec 12 13:24 ..
[root@myps ~]# lsof | grep mysql.sock |wc -l
0
發現root使用者下isql連線沒問題
[root@myps bin]# ./isql -v mysql215_test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
[root@myps bin]# ./isql -v mysql215_mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
檢查odbc.int檔案許可權：
[oracle@myps etc]$ ll
total 8
-rw-r--r-- 1 root root 308 Dec 17 10:10 odbc.ini
-rw-r--r-- 1 root root 615 Dec 17 10:10 odbc.ini.bak
修改檔案許可權為oracle
[root@myps etc]# chown -R oracle:dba *
[root@myps etc]# chmod 755 *
[root@myps etc]# ll
total 8
-rwxr-xr-x 1 oracle dba 628 Dec 17 11:42 odbc.ini
-rwxr-xr-x 1 oracle dba 615 Dec 17 10:10 odbc.ini.bak
這時候isql連線正常
[oracle@myps bin]$ ./isql -v mysql215_test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
[oracle@myps bin]$ ./isql -v mysql215_mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
參考：
這個mysql.sock應該是mysql的主機和客戶機在同一host上的時候，使用unix domain socket做為通訊協議的載體，它比tcp快。通常遇到這個問題的原因就是你的mysql server沒執行起來。
Mysql有兩種連線方式：
（1），TCP/IP
（2），socket
對mysql.sock來說，其作用是程式與mysqlserver處於同一臺機器，發起本地連線時可用。
例如你無須定義連線host的具體IP得，只要為空或localhost就可以。
在此種情況下，即使你改變mysql的外部port也是一樣可能正常連線。
因為你在my.ini中或my.cnf中改變埠後，mysql.sock是隨每一次 mysql server啟動生成的。已經根據你在更改完my.cnf後重啟mysql時重新生成了一次，資訊已跟著變更。
那麼對於外部連線，必須是要變更port才能連線的。
linux下安裝mysql連線的時候經常回提示說找不到mysql.sock檔案，解決辦法很簡單：
如果是新安裝的mysql，提示找不到檔案，就搜尋下，指定正確的位置。
如果mysql.sock檔案誤刪的話，就需要重啟mysql服務，如果重啟成功的話會在datadir目錄下面生成mysql.sock 到時候指定即可。
如果還不行就選擇用TCP連線方式連線就行了，其實windows下還支援管道連線方式
透過修改/etc/my.cnf檔案來修正它，開啟檔案
更改一下：
[mysqld]
socket=/var/lib/mysql.sock
假如更改後mysql程式連不上，可以使用下面的方法繼續修改：
[mysql]
socket=/tmp/mysql.sock
另外還可以用下面的方法:
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
http://keepalived.iteye.com/blog/1418638
http://www.cnitblog.com/jakiegu/archive/2009/09/22/40843.html
5.5. 解除安裝mysql-connector
[root@myps ~]# rpm -qa |grep mysql
mysql-connector-odbc-commercial-5.2.6-1.rhel5
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
mysql-connector-odbc-commercial-5.2.6-1.rhel5
[root@myps ~]# rpm -e mysql-connector-odbc-commercial-5.2.6-1.rhel5
error: "mysql-connector-odbc-commercial-5.2.6-1.rhel5" specifies multiple packages
[root@myps ~]# rpm -e mysql-connector-odbc-commercial-5.2.6-1.rhel5 --nodeps
error: "mysql-connector-odbc-commercial-5.2.6-1.rhel5" specifies multiple packages
[root@myps ~]# rpm -e mysql-connector-odbc-commercial-5.2.6-1.rhel5 --allmatches --nodeps
Success: Usage count is 1
Success: Usage count is 1
Success: Usage count is 0
Success: Usage count is 0
[root@myps ~]# rpm -qa |grep mysql
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
[root@myps ~]# rpm -e mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5 --allmatches --nodeps
[root@myps ~]# rpm -qa |grep mysql
[root@myps ~]#
Use of an ODBC Driver Manager to Support Concurrent Connections to Multiple Databases (文件 ID 753815.1)
5.6. ORA-600 [HO define: Long fetch]
![clip_image014[4]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618456CtSx.jpg "clip_image014[4]")
有一個bigint(19):
select "id" from "go_visit_log20131205"@link_logs_2_40 where "id"=10
嗯。可以了。我規避了bigint就可以了。
|
|
ORA-600 [HO define: Long fetch] Error Message When Selecting Data Via Oracle Database Gateways (文件 ID 1224783.1) |
![clip_image016[4]](http://img.blog.itpub.net/blog/attachment/201402/17/14710393_1392618459p1k9.gif "clip_image016[4]"){kind=small}
5.7. 配置過程中的一些問題
[root@myps mysql_connector32]# rpm -ivh *.rpm
Preparing... ########################################### [100%]
1:mysql-connector-odbc-co########################################### [ 50%]
2:mysql-connector-odbc-co########################################### [100%]
Success: Usage count is 1
Success: Usage count is 1
[root@myps mysql_connector64]# ls
mysql-connector-odbc-commercial-5.2.6-1.rhel5.x86_64.rpm README.txt
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5.x86_64.rpm V41042-01.zip
[root@myps mysql_connector64]# rpm -ivh *.rpm
Preparing... ########################################### [100%]
1:mysql-connector-odbc-co########################################### [ 50%]
2:mysql-connector-odbc-co########################################### [100%]
Success: Usage count is 2
Success: Usage count is 2
[root@myps mysql_connector64]# rpm -qa |grep mysql
mysql-connector-odbc-commercial-5.2.6-1.rhel5
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
mysql-connector-odbc-commercial-debuginfo-5.2.6-1.rhel5
mysql-connector-odbc-commercial-5.2.6-1.rhel5
[root@myps mysql_connector64]#
[root@myps mysql_database]# rpm -ivh MySQL-client-advanced-5.6.14-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-client-advanced ########################################### [100%]
[root@mysql setup]# mysql -u root -p
Enter password:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.12.2.55' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.12.2.185' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
[root@myps mysql_database]#
[root@myps mysql_database]# cd /etc/
[root@myps etc]# ls -alt odbc*
-rw-r--r-- 1 root root 299 Dec 12 13:31 odbcinst.ini
-rw-r--r-- 1 root root 0 Jul 13 2006 odbc.ini
[root@myps etc]# rpm -qa |grep ODBC
unixODBC-devel-2.2.11-7.1
unixODBC-kde-2.2.11-7.1
unixODBC-2.2.11-7.1
unixODBC-devel-2.2.11-7.1
unixODBC-2.2.11-7.1
unixODBC-kde-2.2.11-7.1
[root@myps lib]# pwd
/usr/lib
[root@myps lib]# ls -alt libmy*
-rwxr-xr-x 1 root root 5243700 Sep 26 10:40 libmyodbc5a.so
-rwxr-xr-x 1 root root 5241428 Sep 26 10:40 libmyodbc5w.so
[root@myps ~]# odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
[root@myps ~]#
[root@myps ~]# more /etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[MySQL ODBC 5.2 Unicode Driver]
Driver = /usr/lib64/libmyodbc5w.so
UsageCount = 2
[MySQL ODBC 5.2 ANSI Driver]
Driver = /usr/lib64/libmyodbc5a.so
UsageCount = 2
[root@myps etc]# more odbc.ini
[ODBC Data Sources]
demo = MySQL ODBC Driver 5.6
[mysql215]
Driver = /usr/lib/libmyodbc5a.so
DATABASE = test
DESCRIPTION = MySQL ODBC 5.6 Connector Sample
PORT = 3306
SERVER = 10.12.2.215
UID = root
PWD = root
CHARSET = gbk
TRACEFILE = /tmp/myodbc-demodsn.trc
TRACE = ON
說明 ：
[test] -------dsn 的名字
Driver = /usr/lib/libmyodbc3.so -------mysql-conn-odbc 的驅動
DATABASE = test --------mysql 資料庫的database
DESCRIPTION = MySQL ODBC 3.51 Driver -----------描述字元
PORT = 3306 ---------------mysql的埠號
SERVER = 127.0.0.1 ----------------mysql 主機的ip 或者主機名
UID = nagios ----------從oracle端登入mysql的使用者名稱
PWD = passpwd --------------- 從oracle端登入mysql的密碼
CHARSET = gbk -------------------字符集
TRACEFILE = /tmp/myodbc-demodsn.trc ------trace 檔案 mysql -odbc 3.51下不起作用
TRACE = ON -----開啟trac --------mysql -odbc 3.51下不起作用
[oracle@myps ~]$ isql test root root -v
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
[oracle@myps ~]$ isql mysql215 root root -v
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5a.so' : /usr/lib/libmyodbc5a.so: wrong ELF class: ELFCLASS32
[ISQL]ERROR: Could not SQLConnect
[oracle@myps ~]$
[root@myps ~]# su - oracle
[oracle@myps ~]$ isql mysql215 root root -v
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5w.so' : /usr/lib/libmyodbc5w.so: wrong ELF class: ELFCLASS32
[ISQL]ERROR: Could not SQLConnect
[oracle@myps ~]$
[oracle@myps admin]$ more /u01/app/oracle/product/11.2.0.3/db_1/hs/admin/initmysql215.ora
HS_FDS_CONNECT_INFO = mysql215
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291
set ODBCINI= /etc/odbc.ini
set ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/lib:/usr/lib:/u01/app/oracle/product/11.2.0.3/db_1/hs/lib
[oracle@myps lib64]$ cd /usr/lib64
[oracle@myps lib64]$ ls -alt libodbc.*
lrwxrwxrwx 1 root root 16 Nov 28 13:04 libodbc.so -> libodbc.so.1.0.0
lrwxrwxrwx 1 root root 16 Nov 28 13:04 libodbc.so.1 -> libodbc.so.1.0.0
-rwxr-xr-x 1 root root 411488 Jul 13 2006 libodbc.so.1.0.0
-rw-r--r-- 1 root root 1075984 Jul 13 2006 libodbc.a
[oracle@myps lib]$ cd /usr/lib
[oracle@myps lib]$ ls -alt libodbc.*
lrwxrwxrwx 1 root root 16 Nov 28 13:05 libodbc.so -> libodbc.so.1.0.0
lrwxrwxrwx 1 root root 16 Nov 28 13:05 libodbc.so.1 -> libodbc.so.1.0.0
-rwxr-xr-x 1 root root 447892 Jul 13 2006 libodbc.so.1.0.0
-rw-r--r-- 1 root root 844080 Jul 13 2006 libodbc.a
[root@myps lib64]# vi /etc/odbc.ini
[mysql215]
Driver = /usr/lib64/libmyodbc5w.so
DATABASE = test
DESCRIPTION = MySQL ODBC 5.6 Connector Sample
PORT = 3306
SERVER = 10.12.2.215
UID = root
PWD = root
CHARSET = gbk
TRACEFILE = /tmp/myodbc-demodsn.trc
TRACE = ON
[root@myps lib64]# isql mysql215
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
[root@myps lib64]# su - oracle
[oracle@myps ~]$ isql -v mysql215
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
錯誤：
SQL> select * from "emp"@ln_mysql;
select * from "emp"@ln_mysql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from LN_MYSQL
[oracle@myps admin]$ tnsping mysql215
[oracle@myps admin]$ more initmysql215.ora
HS_FDS_CONNECT_INFO = mysql215
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291
set ODBCINI= /etc/odbc.ini
set ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/lib:/usr/lib:/u01/app/oracle/product/11.2.0.3/db_1/hs/lib
[oracle@myps admin]$ dg4odbc
Oracle Corporation --- THURSDAY DEC 12 2013 16:40:50.236
Heterogeneous Agent Release 11.2.0.3.0 - 64bit Production Built with
Oracle Database Gateway for ODBC
[root@myps ~]# vi /etc/odbc.ini
[mysql215]
Driver = /usr/lib64/libmyodbc5w.so
DATABASE = test
DESCRIPTION = MySQL ODBC 5.6 Connector Sample
PORT = 3306
SERVER = 10.12.2.215
USER = root
PASSWORD = root
OPTION =0
TRACEFILE = /u01/app/oracle/myodbc-demodsn.trc
TRACE = ON
[oracle@myps ~]$ isql -v mysql215
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database |
+-----------------------------------------------------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+-----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test |
+-----------------------------------------------------------------+
| emp |
+-----------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
[oracle@myps admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PS92TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PS92TEST)
)
)
MYSQL215 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.2.185)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysql215)
)
(HS = OK)
)
[oracle@myps admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = mysql215)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = dg4odbc)
(ENVS ="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/11.2.0.3/db_1/lib:/usr/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myps.testdomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@myps admin]$ more initmysql215.ora
HS_FDS_CONNECT_INFO = mysql215
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
set ODBCINI= /etc/odbc.ini
SQL> create public database link lk_mysql215 connect to "root" identified by "root" using 'mysql215';
Database link created.
SQL> select * from "emp"@ln_mysql215;
select * from "emp"@ln_mysql215
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
[oracle@myps admin]$ /usr/sbin/lsof |grep "dg4odbc" |grep "libodbc"
[oracle@myps admin]$ /usr/sbin/lsof |grep "dg4odbc" |grep "libmyodbc"
[oracle@myps log]$ cd /u01/app/oracle/product/11.2.0.3/db_1/hs/log
[oracle@myps log]$ ls -alt
total 56
-rw-r--r-- 1 oracle dba 20255 Dec 12 20:18 mysql215_agt_13933.trc
drwxr-xr-x 2 oracle dba 4096 Dec 12 16:47 .
-rw-r--r-- 1 oracle dba 13633 Dec 12 16:46 mysql215_agt_13628.trc
6. 參考：
http://blog.itpub.net/133735/viewspace-731986
http://blog.chinaunix.net/uid-411974-id-3807113.html
http://blog.itpub.net/8297086/viewspace-693945
http://blog.itpub.net/21601207/viewspace-709366
http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/
http://www.cnblogs.com/wwwiori/archive/2009/06/22/1508068.html
http://www.cnblogs.com/wwwiori/archive/2009/06/22/1508079.html
oracle-dg4odbc
http://blog.csdn.net/wannshan/article/details/5602085
http://blog.itpub.net/21601207/viewspace-709366
http://blog.csdn.net/lwei_998/article/details/7383844
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文件 ID 1320645.1)
How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (文件 ID 561033.1)
Select From Non-Oracle Database Using Dg4odbc Returns Ora-28500 From Sqlplus (文件 ID 1254254.1)
Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (文件 ID 756186.1)
How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文件 ID 234517.1)
Character Data Returned With Spaces From MySQL Using DG4ODBC (文件 ID 1068854.1)
When resolving the ORA-28500 error it's important to note that the username and password must be in double quotes.
http://blog.csdn.net/liefdiy/article/details/5348583
oracle--hsodbc
http://blog.itpub.net/8297086/viewspace-693945
http://blog.itpub.net/133735/viewspace-731986
http://www.cnblogs.com/lightnear/archive/2013/02/03/2890858.html
http://blog.csdn.net/wannshan/article/details/5602085
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1082556/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 透明閘道器訪問 MYSQL 表OracleMySql
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- Oracle 通過透明閘道器訪問mysql配置步驟OracleMySql
- Oracle Gateways透明閘道器訪問SQL ServerOracleGatewaySQLServer
- Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16OracleMySql
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- oracle透過透明閘道器訪問sql serverOracleSQLServer
- Oracle 透明閘道器連線MySQLOracleMySql
- Oracle配置透明閘道器訪問sql2000OracleSQL
- 10g透明閘道器訪問sqlserverSQLServer
- oracle透明閘道器之異構資料庫的訪問Oracle資料庫
- Oracle10g中通過透明閘道器、DBLink訪問MySQL資料OracleMySql
- ORACLE透明閘道器的配置Oracle
- oracle通過透明閘道器連線mysql的配置OracleMySql
- ORACLE 9i 建立透明閘道器訪問SQL Server 2008OracleSQLServer
- oracle通過透明閘道器（Oracle Transparent Geteways），訪問ms sql server和其他資料庫OracleSQLServer資料庫
- 【磐維資料庫】Oracle（透明閘道器）訪問磐維資料庫（PanWeiDB）資料庫Oracle
- oracle連線teradata透明閘道器配置Oracle
- transparent gateway 透明閘道器配置Gateway
- 安全閘道器 透明加解密解密
- ORACLE通過透明閘道器連線DB2OracleDB2
- oracle9i透明閘道器到sqlserver的配置OracleSQLServer
- ORACLE9i 的透明閘道器的配置 (轉)Oracle
- 透明閘道器 oracle 10g for sqlserver 和sybase的配置Oracle 10gSQLServer
- ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題OracleSQL
- linux 作業系統下ORACLE資料庫使用透明閘道器連線MYSQLLinux作業系統Oracle資料庫MySql
- 配置Oracle Gateways透明閘道器連線多個MSSQL資料庫OracleGatewaySQL資料庫
- ADSL+RH8.0透明閘道器指南(轉)
- 時代億信檔案共享訪問控制閘道器概述
- 不設定預設閘道器，可訪問網路
- 透明閘道器一些特殊場景的配置
- 透明閘道器的安裝配置及一般使用
- Janusec應用安全閘道器（WAF閘道器）
- Ceph物件閘道器，多區域閘道器物件
- 透明的閘道器：在Linux上執行IPchains(轉)LinuxAI
- 閘道器GatewayGateway