1, first install MySQL client side software on the Oracle computer, and the x86_64 and i386 versions need to be installed so that the MySQL database can be connected. To view the client installation:
Copy Code code as follows:
Rpm-qa |grep MySQL
Mysql-5.0.45-7.el5
Mysql-5.0.45-7.el5
Get two records, one is x86_64, one is i386.
If you see that the MySQL client software is not installed, you will need to install:
Copy Code code as follows:
Yum install MySQL
Yum Install mysql.i386
Verify that the computer on which Oracle resides is able to connect to the target host MySQL database:
Copy Code code as follows:
Mysql-h 192.168.1.1-u root-p MySQL
2, check to see if the Oracle computer has a MySQL ODBC client installed, and the x86_64 and i386 versions need to be installed.
Copy Code code as follows:
Rpm-qa |grep Mysql-connect
If MYSQL-CONNECTOR-ODBC is not installed, download and install with the following command:
Download MYSQL-CONNECTOR-ODBC:
Copy Code code as follows:
wget Ftp://mirror.switch.ch/pool/3/mirror/centos/5.2/os/i386/CentO s/mysql-connector-odbc-3.51.12-2.2.i386.rpm
Install MYSQL-CONNECTOR-ODBC:
Copy Code code as follows:
RPM-IVH mysql-connector-odbc-3.51.12-2.2.i386.rpm
Get tips
Copy Code code as follows:
Libltdl.so.3 is needed by mysql-connector-odbc-3.51.12-2.2.i386, which finds that a i386 version of Libtool is required to be installed, so install libtool-ltdl.i386 with the following command:
Yum List *.i386|grep Libtool
Yum Install libtool-ltdl.i386
3, edit/etc/odbc.ini files on Oracle's computer and test ODBC work
Copy Code code as follows:
#odbc. INI contents are as follows
Copy Code code as follows:
[Test]
Driver=/usr/lib64/libmyodbc3.so
Description=mysql
server=192.168.1.1 (MySQL Server IP)
port=3306
User= (MySQL Username)
Uid= (MySQL Username)
password= (MySQL PWD)
Database= (MySQL Database Name)
Option=3
socket=
You should be able to successfully log into the MySQL client window by executing the following commands on the command line, which proves that ODBC functions are normal:
Copy Code code as follows:
4, edit the HS profile Vi/ora10g/hs/admin/inittest.ora (note that the blue part of the file name is the blue name in Odbc.ini)
Copy Code code as follows:
Hs_fds_connect_info = Test
Hs_fds_trace_level = ON (when formally used, should be set to OFF when no error is required)
Hs_fds_trace_file_name = Test.trc
Hs_fds_shareable_name=/usr/lib/libmyodbc3.so
Set Odbcini=/etc/odbc.ini
5, edit the Oracle listener configuration file of Oracle's computer, establish a monitoring method to simulate Oracle listener, and prepare for the future establishment of Dblink:
Vi/ora10g/network/admin/listener.ora Add the following statement:
Copy Code code as follows:
(Sid_desc =
(sid_name = test)
(Oracle_home =/ora10g)
(program = HSODBC)
(Envs=ld_library_path=/ora10g/lib32:/usr/lib64:/ora10g/lib)
)
The contents of the Listener.ora file now become:
Copy Code code as follows:
Sid_list_listener = (
Sid_list =
(Sid_desc =
(Oracle_home =/ora10g)
(program = Extproc)
(Global_dbname=prod)
(Sid_name=prod)
)
(Sid_desc =
(sid_name = test)
(Oracle_home =/ora10g)
(program = HSODBC)
(Envs=ld_library_path=/ora10g/lib32:/usr/lib64:/ora10g/lib)
)
)
LISTENER = (
Description_list =
(DESCRIPTION = (address = (PROTOCOL = IPC) (KEY = EXTPROC1))
(address = (PROTOCOL = TCP) (HOST = 0.0.0.0) (PORT = 1521))
)
Perform lsnrctl reload to make listener effective:
Copy Code code as follows:
Su–oracle
Lsnrctl Reload
Lsnrctl for linux:version 10.2.0.4.0-production on 09-feb-2009 13:59:38 Copyright (c) 1991, 2007, Oracle.
All rights reserved. Connecting to (description= address= (PROTOCOL=IPC) (Key=extproc1))
The command completed successfully
Lsnrctl status
Lsnrctl for linux:version 10.2.0.4.0-production on 12-feb-2009 08:56:00
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (description= address= (PROTOCOL=IPC) (Key=extproc1))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 10.2.0.4.0-production
Start Date 03-jan-2009 03:47:39
Uptime 5 hr. 8 min sec
Trace level off
Security On:local OS Authentication
SNMP off
Listener Parameter File/ora10g/network/admin/listener.ora
Listener Log File/ora10g/network/log/listener.log
Listening Endpoints Summary ...
(Description= (address= (PROTOCOL=IPC) (Key=extproc1))
(Description= (address= (protocol=tcp) (host=0.0.0.0) (port=1521))
Services Summary ...
Service "Plsextproc" has 1 instance (s).
Instance "Plsextproc", Status UNKNOWN, has 1 handler (s) for the This service ...
Service "Test" has 1 instance (s).
Instance "Test", Status UNKNOWN, has 1 handler (s) for the This service ...
The command completed successfully
6, edit the Tnsnames.ora file on Oracle's computer to facilitate the establishment of dblink. Note that this tnsnames configuration can support tnsping, but cannot support sqlplus logins, only for Dblink:
Copy Code code as follows:
Vi/ora10g/network/admin/tnsnames.ora
Test =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
(Connect_data =
(SID = test)
)
(HS = OK)
)
7, establish Dblink in Oracle database:
Copy Code code as follows:
Create Public database link MYSQL
Connect to "MySQL username" identified by "MySQL pwd"
Using ' (DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521)
(Connect_data = (sid= test))
(Hs=ok)
)';
Note that the username and password need to be in double quotes, or Oracle will be transferring uppercase letters and may not be able to log into MySQL.
8, due to the case sensitivity of the table name in MySQL, it is necessary to expand the table name in double quotes when making SQL query
Copy Code code as follows:
SELECT * from "tablename" @test
