PART I : Setting up ODBC driver
1. Install mysql-connector-odbc-3.51.12-linux-i686.rpm
bash> rpm –ivh mysql-connector-odbc-3.51.12-linux-i686.rpm
2. Start Mysql Daemon
bash> /etc/init.d/mysqld start Initializing MySQL database: [ OK ]
3. Configure ODBC
bash> ODBCConfig
4. Modify : /etc/odbc.ini
bash> vi /etc/odbc.ini
[trans] Driver = MySQL ODBC 3.51 Driver DATABASE = kampus DESCRIPTION = punya_kampus PWD = password PORT = 3306 SERVER = localhost UID = root
5. Test Connection
isql DSN [UID [PWD]] [options]
bash> isql -v trans root password
+---------------------------------------+ | Connected! | | sql-statement | help [tablename] | quit | +---------------------------------------+
PART II : How 2 Configure HSODBC
1. Listener
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = trans) (ORACLE_HOME = /u01/app/oracle/product/10.2.0) (PROGRAM = hsodbc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = OraHost)(PORT = 1521)) ) )
bash> lsnrctl reload
bash> lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-NOV-2006 22:50:28 Copyright (c) 1991, 2005, 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.1.0 - Production Start Date 12-NOV-2006 22:42:34 Uptime 0 days 0 hr. 7 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OraHost)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod_XPT" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "trans" has 1 instance(s). Instance "trans", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
2. Edit tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OraHost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) trans = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = OraHost)(PORT = 1521)) ) (CONNECT_DATA = (SID = trans)) (HS = OK) )
3. Configure $ORACLE_HOME/hs/admin/inithsodbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
# HS init parameters
#
HS_FDS_CONNECT_INFO = trans
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so
#
# ODBC specific environment variables
#set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
# set <envvar>=<value>
#
4. Configure Environment & Test tnsping
bash> vi .bash_profile
# Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi
# User specific environment and startup programs PATH=$PATH:$HOME/bin # Henri Configure export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0 export ORACLE_SID=prod export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export PATH=$PATH:$ORACLE_HOME/bin:/sbin:. export TNS_ADMIN=$ORACLE_HOME/network/admin export ODBCINI=/etc/odbc.ini
bash> tnsping trans TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 12-NOV-2006 23:21:43 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10.2.0/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = OraHost)(PORT = 1521))) (CONNECT_DATA = (SID = trans)) (HS = OK)) OK (10 msec)
5. Creating Database Link
SQL> conn kadal/kadal Connected.
SQL> CREATE DATABASE LINK trans CONNECT TO "root" IDENTIFIED BY "password" USING 'trans'; Database link created.
6. Test Koneksi
SQL> SELECT * FROM MHS@TRANS;
Note : Case Sensitive
Thank's. Very good article
You're welcome obninsk,
maybe you can add new articles for us here :d
klo setingan windows gmn pak? coz sya yg bngung di inithsodbc.ora utk mysql drivernya, dan di tnsping msih keluar eror tns no listener pdhl langkah 1-2 sudah ok,dan sudah buat odbc mysql via windows,mohn pencerahannya,terimakasih
@kunta
tns no listener biasanya menandakan tidak resolve antara hostname dan ipaddress.
tapi coba tambahkan parameter dibawah ini dilistener.ora anda, lalu coba restart listenernya (saya merefer pada tulisan mas henry diatas)
# add di listener.ora
TRANS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=OraHost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
)
semoga membantu
Hi,
I have completed all your step but am facing below error .
Error : ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TESTDB
Regards,
Muthu