Oracle Database Discussions

Dear All,

My Database is Oracle 11gR2 Rac of 2 nodes and on Linux 64-Bit operating system.
Version of my MySQL database is 5.5.17 it is on Linux 32-Bit operating system.

I am trying to connect MySQL database from Oracle database using a database link.

These are the step by step process that I am following to achieve my target. All my processes are on one node of Oracle RAC.

1)
Yum install mysql-connector-odbc
Yum install unixODBC

2)
Edit file /etc/odbc.ini
[test]
Driver = /usr/lib64/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 10.0.0.1
PORT = 3306
USER = test
Password = test
Database = test
OPTION = 3
SOCKET =

3)
Edit file /etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc3.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1

4) test connection
isql –v test test test
Returns:
---------------------------------------
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
---------------------------------------
SQL>

5) Now I edit listener.ora file and add the entry. After entry my listener.ora file is like this:

LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST=testnode1)(PORT = 1521))
)
)

# line added by Agent
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
)

# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib)
)
)

6) After these entries the listener status after RESTART is as following:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 27-DEC-2011 01:19:32
Uptime 0 days 9 hr. 31 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/racnode1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
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...
Service "testdb" has 1 instance(s).
Instance "testdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "testdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

7) Then in the tnsnames.ora file I added the following entry:

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = EXTPROC1521)
)
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
(HS = OK)
)

When i tnsping test, it works.

8) I edit the .bash_profile file for Oracle
ORACLE_HOME, ORACLE_BASE were already pointing to the correct destination, I added:

LD_LIBRARY_PATH=/usr/local/lib:/u01/app/oracle/product/11.1.0/db_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib:/usr/lib64:/usr/lib
export LD_LIBRARY_PATH

ODBCINI=/etc/odbc.ini
export ODBCINI

ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI

ODBCSYSINI=/etc
export ODBCSYSINI

9) Then i configured $ORACLE_HOME/hs/admin/inithsodbc.ora
And Modified these lines:
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = ON
HS_FDS_TRACE_FILE_NAME = odbc_test.log
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISCTICS = FALSE
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI = /etc/odbc.ini

10) Then i created a database link with the following command:
Create public database link test connect to test identified by test using ‘test’;

I tried username and password in database link with double quotes also.

11) Now Finally when I try to select records from mysql table:
select count(*) from "table"@"test"; -- I have tried with and without double quotes

I get the following error:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from TEST

How to get through this error, and how to check logs for HS?

Your help is required.

Regards, Imran

Edited by: misterimran on Dec 27, 2011 1:21 PM