Oracle透明网关访问Mysql—luckyfriends-CSDN博客

/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_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

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

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

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

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

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;

--------------------------------------------------------------------------------

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

[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

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

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

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

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

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;

--------------------------------------------------------------------------------

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)

----------

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;

--------------------------------------------------------------------------------

SQL> select "a" from "emp"@lk_mysql215_test;

--------------------------------------------------------------------------------

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

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:

参考：

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

发现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-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 0

[root@myps ~]# rpm -qa |grep mysql

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

[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

[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-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://www.oracle.com/technetwork/middleware/id-mgmt/documentation/mysql-integration-guide-428960.pdf

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://wenku.baidu.com/link?url=l_jX2FZBz5eaAoBxciDQ9Ww1fSjszBrdBNMwtdKejIHZrPBudZTOIO5HKkAOl3wP_DJjqRtjfOMgKTaGbuwp7S5awxHj5kYavwcriaWZsGC

http://it.kswchina.com/Oracle/zh/506285.html

http://blog.itpub.net/8297086/viewspace-693945

http://blog.itpub.net/133735/viewspace-731986

http://wenku.baidu.com/link?url=l_jX2FZBz5eaAoBxciDQ9Ww1fSjszBrdBNMwtdKejIHZrPBudZTOIO5HKkAOl3wP_DJjqRtjfOMgKTaGbuwp7S5awxHj5kYavwcriaWZsGC

透明网关连接Sqlserver 2000

http://www.cnblogs.com/lightnear/archive/2013/02/03/2890858.html

http://database.51cto.com/art/201108/284438.htm