总页数:3页 |
Oracle透明网关访问Mysql
(V 1.0)
文章版权所有Jusin Hao(luckyfriends),支持原创,转载请注明。
2012年8月
版本信息
日期 | 版本 | 描述 | 作者/修改人 | 备注 |
2012-8-31 | 1.0 | 创建 | Jusin Hao | |
目 录
3. 配置Oracle通过dg4odbc访问一个mysql数据库... 6
3.1.2. We shall start from determining if [Oracle] and [DG4ODBC] are 32/64-bits: 6
3.2. 下载并安装ODBC Driver Manager(unixODBC) 7
3.3. 下载并安装配置Getting and installing [ODBC Driver]. 8
3.4. Configuring ODBC data source for MySQL Connector/ODBC driver 9
3.5. Configuring tnsnames.ora. 10
3.6. Configuring listener.ora. 11
3.7. Configuring gateway init.ora file. 12
3.8. Applying the settings in the configuration files. 12
3.9. Creating the database link and getting the data. 15
4. 配置Oracle通过dg4odbc访问多个mysql数据库... 16
4.1. Configuring ODBC data source for MySQL Connector/ODBC driver 16
4.2. Configuring tnsnames.ora. 17
4.3. Configuring listener.ora. 18
4.4. Configuring gateway init.ora file. 19
4.5. Applying the settings in the configuration files. 20
4.6. Creating the database link and getting the data. 22
5.2. ORA-00972: identifier is too long. 24
5.3.1. plsqldeveloper把unicode勾选去掉... 26
5.6. ORA-600 [HO define: Long fetch] 36
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 的包 www.unixodbc.org
因为要跟mysql关联所以要在oracle 的主机上安装mysql的客户端驱动。 mysql-connect-odbc www.mysql.com/downloads
注意:
因为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 unixODBC-2.3.2.tar.gz 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
http://www.mysql.com/products/
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_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://www.docin.com/p-113642416.html
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
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
参考:http://www.orafaq.com/forum/t/162496/0/
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里查询有乱码;
但是sqlplus里查询没有乱码
我想是因为mysql的字符集 和这边不匹配;
5.3.1. plsqldeveloper把unicode勾选去掉但是中文还是乱码;
5.3.2. 中文乱码解决查询数据库字符集设置,利用Navicat工具:
latin1 -- cp1252 West European
利用工具做修改:
登录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
修改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:
参考:
ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
http://www.2cto.com/database/201108/101151.html
http://www.jb51.net/article/18560.htm
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://www.linuxdiyf.com/viewarticle.php?id=84240
http://tech.ccidnet.com/art/1105/20070927/1227289_1.html
http://www.bitscn.com/plus/view.php?aid=20247
http://keepalived.iteye.com/blog/1418638
http://www.cnitblog.com/jakiegu/archive/2009/09/22/40843.html
http://www.360doc.com/content/08/0429/17/3500_1223586.shtml
http://www.360doc.com/content/11/0117/15/3626309_87131677.shtml
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]
有一个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) |
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
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
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. 参考:
https://forums.oracle.com/thread/2410525
http://www.oracle.com/technetwork/database/gateways/index.html
http://www.oracle.com/technetwork/middleware/id-mgmt/oeg-300773.html
http://it.kswchina.com/Oracle/zh/506285.html
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://hi.baidu.com/zhangsilly/item/f056a50ca6063ae9349902d4
http://www.yinxiulei.cn/oracle-mysql.html
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://www.yinxiulei.cn/oracle-mysql.html
http://blog.csdn.net/wannshan/article/details/5602085
http://blog.itpub.net/21601207/viewspace-709366
http://blog.csdn.net/lwei_998/article/details/7383844
http://www.docin.com/p-113642416.html
https://forums.oracle.com/message/9117369
https://forums.oracle.com/thread/929129
http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html
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://it.kswchina.com/Oracle/zh/506285.html
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://database.51cto.com/art/201108/284438.htm
http://database.51cto.com/art/201108/284450.htm
http://blog.csdn.net/wannshan/article/details/5602085
http://it.kswchina.com/Oracle/zh/506285.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14710393/viewspace-1082556/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14710393/viewspace-1082556/