Oracle to connect MySQL
Dear All,
from couple of days I am trying to connect mysql from my oracle database.
I have studied so many documentations and have created one of my own. I am just struck at one point.
My oracle database is 11gR1 and CentOS 5.1 32-bit is the operating system.
MySQL is on a windows based machine and version is 5.1.
What I can do:
I can connect MySQL from Linux using the command: isql –v bssdata <user> <password> [Connected]
I can tnsping the listener using command: tnsping bssdata [OK]
But after setting up what i did in the documentation below when I try to connect mysql using a DB link. It gives the following error:
select * from bssdata.sta_mis_std_marks@bssdata
Error at Command Line:1 Column:40
Error report:
SQL Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from BSSDATA
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Please help me get out of this. Thank you very much
Here are the details what i did:
------------------------------------------------------------------------------------------
Install required packages
Yum install mysql-connector-odbc
Yum install unixODBC
vi /etc/odbc.ini
[bssdata]
# myodbc3 = MySQL ODBC 3.51 Driver DSN
# [myodbc3]
Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 192.168.0.68
PORT = 3306
USER = ******
Password = ******
Database = bssdata
OPTION = 3
SOCKET =
vi /etc/odbcinst.ini
# Example driver definitinions
# Included in the unixODBC package
#[PostgreSQL]
#Description = ODBC for PostgreSQL
#Driver = /usr/lib/libodbcpsql.so
#Setup = /usr/lib/libodbcpsqlS.so
#FileUsage = 1
# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1
Test Connection
isql –v bssdata ****** ******
Editing listener.ora file to add entry
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = blissglb.abc.edu.pk)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = bssdata)
(ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/11.1.0/db_1/odbc)
)
)
Restart listener
Lsnrctl> stop
Lsnrctl> start
Test listener
tnsping bssdata
Editing tnsnames.ora file in network directory of oracle
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BLISSGLB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = blissglb.abc.pk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = blissglb.abc.edu.pk)
)
)
BSSGLB, BSSGLB.abc.EDU.PK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bssglb.abc.edu.pk)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = EXTPROC1521)
)
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
bssdata =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.66)(PORT = 1521))
)
(CONNECT_DATA =
(SID = bssdata)
)
(HS = OK)
)
Edit Oracle’s bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_HOME
ORACLE_SID=blissglb
export ORACLE_SID
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
LD_LIBRARY_PATH=/usr/local/lib:/u01/app/oracle/product/11.1.0/db_1/lib
export LD_LIBRARY_PATH
ODBCINI=/etc/odbc.ini
export ODBCINI
ODBCSYSINI=/etc
export ODBCSYSINI
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
unset USERNAME
Configure file db_1/hs/admin/inithsodbc.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 = <odbc data_source_name>
#HS_FDS_TRACE_LEVEL = <trace_level>
#HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>
HS_FDS_CONNECT_INFO = bssdata
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so
# ODBC specific environment variables
#set ODBCINI=<full path name of the odbc initilization file>
set ODBCINI=/etc/odbc.ini
# Environment variables required for the non-Oracle system
set <envvar>=<value>
Create Database Link
Create database link bssdata connect to ****** identified by ******** using ‘bssdata’
Using selection query
Select * from bssdata.sta_mis_std_marks@bssdata {Here comes the error described above}
Regards,
Imran