http://blog.csdn.net/dbanote/article/details/10488581
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
There is a need to synchronize some Oracle data to MySQL, which is too cumbersome to synchronize with each time, so it takes a little time to study the way Oracle is connected directly to MySQL.
Reference Document:detailed overview of connecting Oracle to MySQL Using dg4odbc Database Link (Doc ID 1320645.1)
Version information:
oracle:11.2.0.1.0 Os:centos 5.9
mysql:5.5.27 Os:centos 5.8
Principle:
Oracle uses the DG4ODBC data gateway to connect to other non-Oracle databases with the following schematic diagram:
From the know, Oracle connection to MySQL involves the following components: DG4ODBC, ODBC Driver Manager, ODBC Driver, this article will explain their configuration.
1) Judging 32 or 64-bit
Because the 32-bit and 64-bit configurations are different, the 64-bit is more complex, so we first have to determine whether Oracle and DG4ODBC are 32-bit or 64-bit:
[Plain]View Plaincopy print?
- [[Email protected] ~]$ file $ORACLE _home/bin/dg4odbc
- /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc:elf 64-bit LSB executable, AMD x86-64, version 1 (SYSV), For Gnu/linux 2.6.9, dynamically linked (uses GKFX libs), not stripped
The output from the above indicates that it is 64 bits.
2) Download and install the ODBC Driver Manager
To this page (http://www.unixodbc.org/download.html) Download UNIXODBC based on your OS (note: Version cannot be less than 2.2.14)
[Plain]View Plaincopy print?
- $ wget Http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
Unzip:
[Plain]View Plaincopy print?
- $ TAR-ZXVF unixodbc-2.2.14-linux-x86-64.tar.gz
After decompression, the USR directory is automatically created in the current directory, we create a directory (~/app/unixodbc-2.2.14) to place the UNIXODBC, and then migrate usr to that directory:
[Plain]View Plaincopy print?
- $ mkdir ~/app/unixodbc-2.2.14
- $ mv usr ~/app/unixodbc-2.2.14
3) Download and follow ODBC Driver for MySQL
To this page (http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads) download ODBC-5.2.5 based on your OS, this example selects the 64-bit tar version:
[Plain]View Plaincopy print?
- $ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.2/ mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz/from/http://cdn.mysql.com/
- $ TAR-ZXVF mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz
After the decompression is a folder, migrate the folder to the ~/app directory and create a soft link for it:
[Plain]View Plaincopy print?
- $ mv Mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit ~/app
- $ CD ~/app
- $ ln-s mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit myodbc-5.2.5
4) Configure ODBC Driver
Under the ~/ETC directory, create the Odbc.ini as follows:
[Plain]View Plaincopy print?
- [Myodbc5]
- Driver =/home/oracle/app/myodbc-5.2.5/lib/libmyodbc5w.so
- Description = Connector/odbc 5.2 Driver DSN
- SERVER = 192.168.1.15
- PORT = 3306
- USER = Mysql_user
- PASSWORD = Mysql_pwd
- DATABASE = mysql_db
- OPTION = 0
- TRACE = OFF
Among them, Driver points to the 3rd step in accordance with the ODBC Driver, here to pay special attention: MySQL Datbase is case-sensitive.
5) Verifying the ODBC connection
[Plain]View Plaincopy print?
- $ Export Odbcini=/home/oracle/etc/odbc.ini
- $ export ld_library_path=/home/oracle/app/unixodbc-2.2.14/usr/local/lib: $LD _library_path
- $ CD ~/app/unixodbc-2.2.14/usr/local/bin
- $./isql Myodbc5-v
- +---------------------------------------+
- | connected! |
- | |
- | sql-statement |
- | Help [TableName] |
- | Quit |
- | |
- +---------------------------------------+
The above shows that the connection was successful.
6) Configuration Tnsnames.ora
[Plain]View Plaincopy print?
- Myodbc5 =
- (description=
- (address=
- (PROTOCOL=TCP) (Host=localhost) (port=1521)
- )
- (Connect_data=
- (SID=MYODBC5)
- )
- (Hs=ok)
- )
7) configuration Listener.ora
[Plain]View Plaincopy print?
- Sid_list_listener=
- (sid_list=
- (sid_desc=
- (SID_NAME=MYODBC5)
- (oracle_home=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
- (PROGRAM=DG4ODBC)
- (envs=ld_library_path=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:/home/oracle/app/oracle/product/11.2.0/ Dbhome_1/lib)
- )
- )
As shown above, in order to avoid conflicts with other existing ODBC Driver manager, it is strongly set ld_library_path in Listener.ora
8) Create a init<sid>.ora file
Create a file $oracle_home/hs/admin/initmyodbc5.ora with the following content:
[SQL]View Plaincopy print?
- HS_FDS_CONNECT_INFO=MYODBC5 # Data Source name in odbc.ini
- hs_fds_shareable_name=/home/oracle/app/unixodbc-2.2.14/usr/local/lib/libodbc.so
- hs_fds_support_statistics=FALSE
- Hs_language=american_america. We8iso8859p15
- # ODBC ENV variables
- Set Odbcini=/home/oracle/etc/odbc.ini
9) Make the above configuration file effective
[SQL]View Plaincopy print?
- $ lsnrctl Reload
- $ LSNRCTL Status
- Service "Myodbc5" has 1 instance (s).
- Instance "Myodbc5", status UNKNOWN, have 1 handler (s) for the This service ...
10) Verify that the configuration is correct
[SQL]View Plaincopy print?
- $ tnsping Myodbc5
- TNS Ping Utility for linux:version 11.2.0.1.0-production on 29-aug-2013 10:54:46
- Copyright (c) 1997, Oracle. All rights reserved.
- Used parameter files:
- /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
- Used TNSNames Adapter to resolve the alias
- Attempting to contact (description= (address= (protocol=tcp) (Host=localhost) (port=1521)) (Connect_data= (SID= MYODBC5)) (Hs=ok) )
- OK (0 msec)
11) Create Dblink
[SQL]View Plaincopy print?
- Sql> Create public database link mysqltest connect to "Mysql_user" identified by "mysq L_pwd "Using ' Myodbc5 ';
- sql> Select Count (*) from [email protected];
- COUNT (*)
- ----------
- 371
-
Top
-
4
-
Step
-
0
- Previous [Oracle] Data Guard Series (5)-Creating a logical standby Library
- Next [Django Combat] 1th-Overview
[Oracle, MySQL] Oracle connects MySQL via Dblink
