Oracle透明閘道器訪問Mysql—luckyfriends

/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