Oracle10g Access the MySQL sample through DBLink
- 2021-10-16 05:22:49
- OfStack
1. First, install the Client software of MySQL on the computer where Oracle is located, and both x86_64 and i386 versions need to be installed in order to connect to MySQL database. To view the Client installation:
rpm -qa |grep mysql
mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
Two records are obtained, one for x86_64 and one for i386.
If you see that the mysql client software has not been installed, you need to install:
yum install mysql
yum install mysql.i386
Verify that the computer on which Oracle is located can connect to the target host MySQL database:
mysql -h 192.168.1.1 -u root -p mysql
2. Check whether the MySQL ODBC client is installed on the computer where Oracle is located, and both x86_64 and i386 versions need to be installed.
rpm -qa |grep mysql-connect
If mysql-connector-odbc is not installed, download and install it with the following command:
Download mysql-connector-odbc:
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:
rpm -ivh mysql-connector-odbc-3.51.12-2.2.i386.rpm
Get prompted
libltdl.so.3 is needed by mysql-connector-odbc-3.51.12-2.2.i386 And found that you need to install libtool Adj. i386 Version, so install it with the following command libtool-ltdl.i386 :
yum list *.i386|grep libtool
yum install libtool-ltdl.i386
3. Edit the file/etc/odbc. ini on the computer where Oracle is located, and test the work of ODBC
vi /etc/odbc.ini
# odbc. ini reads 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=
ODBC should be able to login to the MySQL Client window successfully by executing the following command from the command line, which proves that ODBC is functioning properly:
isql -v test
quit
4. Edit the hs configuration file vi/ora10g/hs/admin/inittest. ora (Note that the blue part of the file name is the blue name in odbc. ini)
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = on (After formal use, it should be set to when there is no need to troubleshoot off )
HS_FDS_TRACE_FILE_NAME = test.trc
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set ODBCINI=/etc/odbc.ini
5. Edit the configuration file of Oracle listener of the computer where Oracle is located, and establish a monitoring mode simulating Oracle Listener to prepare for the establishment of dblink in the future:
vi/ora10g/network/admin/listener. ora Add the following statement:
(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:
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)) )
)
Execute lsnrctl reload to make Listener effective:
su In fact, in fact, the 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 40 days 5 hr. 8 min. 20 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 this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. Edit the tnsnames. ora file in the computer where Oracle is located to facilitate the establishment of dblink. Note that this tnsnames configuration can support tnsping, but not sqlplus login, only for dblink:
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:
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 you need double quotation marks for your username and password, otherwise Oracle transmits capital letters and may not be able to log in to MySQL.
8. Because of the case sensitivity of table names in MySQL, it is necessary to enclose table names in double quotation marks when performing SQL queries
select * from "tablename"@test