Create dblink from oracle to memsql

December 1, 2015 · by · in MemSQL, MySQL, Oracle DB

 

 

 

 

  1. Install required RPM packages
  2. Configure ODBC gateway
  3. Setup environment variable
  4. Configure listener.ora and tnsnames.ora
  5. Configure ODBC listener
  6. Create database link and test2

 

Environment:

 

Database DB Name DB Version Host Name OS Version
Oracle PCRADMN 11.2.0.4 oracle-myvm RHEL 6.7
memsql gen_ap_s1 5.5.8 memsql-myvm N/A

 

  1. Required package check

 

  1. [Dev root @ oracle-myvm ~]  # rpm -qa | grep mysql
  2. mysql-community-client-5.7.9-1.el6.i686
  3. mysql-community-libs-5.7.9-1.el6.i686
  4. *mysql-connector-odbc-5.3.4-1.el6.i686
  5. mysql-community-common-5.7.9-1.el6.i686
  6. *mysql-connector-odbc-5.3.4-1.el6.x86_64
  9. [Dev root @ oracle-myvm ~]  # rpm -qa | grep unixODBC
  10. unixODBC-devel-2.2.14-14.el6.x86_64
  11. unixODBC-2.2.14-14.el6.i686
  12. unixODBC-devel-2.2.14-14.el6.i686
  13. unixODBC-2.2.14-14.el6.x86_64

 

 

 

  1. Configure ODBC gateway and test connection

 

  1. [Dev root @ oracle-myvm ~]  # vi /etc/odbc.ini
  2. [myodbc3]
  3. Driver = /usr/lib64/libmyodbc5w.so
  4. Description = MySQL ODBC 5.1 Driver DSN
  5. SERVER = memsql-myvm.dbversity.com
  6. PORT = 3383
  7. USER= gen_fad_etl_rw_s1
  8. Password= <clear-text password>
  9. Database= gen_ap_s1
  10. OPTION= 3
  11. SOCKET =
  12. charset = utf8
  14. [Dev root @ oracle-myvm ~]  # vi /etc/odbcinst.ini
  15. =>
  16. [MySQL]
  17. Description             = ODBC forMySQL
  18. Driver          = /usr/lib/libmyodbc5w.so
  19. Setup           = /usr/lib/libodbcmyS.so
  20. Driver64                = /usr/lib64/libmyodbc5w.so
  21. Setup64         = /usr/lib64/libodbcmyS.so
  22. FileUsage               = 1
  25. [Dev root @ oracle-myvm ~] # isql myodbc3 -v
  26. +—————————————+
  27. | Connected!                            |
  28. |                                       |
  29. | sql-statement                         |
  30. | help [tablename]                      |
  31. | quit                                  |
  32. |                                       |
  33. +—————————————+
  34. SQL> quit

 

  1. set environment variable as oracle

 

  1. export LD_LIBRARY_PATH=$ORACLE_HOME:lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64
  2. export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
  3. export ODBCINI=/etc/odbc.ini
  4. export ODBCSYSINI=/etc
  5. export ODBCINSTINI=/etc/odbc.ini

 

 

  1. configure listener.ora and tnsnames.ora under $ORACLE_HOME/network/admin

 

  1. SID_LIST_LISTENER_PCRADMN =
  2. (SID_LIST =
  3. (SID_DESC =
  4. (GLOBAL_DBNAME = PCRADMN)
  5. (ORACLE_HOME = /optware/oracle/11.2.0.4/db_1)
  6. (SID_NAME = PCRADMN)
  7. )
  8. (SID_DESC =
  9. (PROGRAM = dg4odbc)
  10. (SID_NAME = myodbc3)
  11. (ORACLE_HOME = /optware/oracle/11.2.0.4/db_1)
  12. (ENVS=LD_LIBRARY_PATH=/optware/oracle/11.2.0.4/db_1/lib:/lib:/usr/lib:/usr/local/lib:/optware/oracle/11.2.0.4/db_1/hs/lib:/usr/lib64)
  13. )
  14. )
  17. LISTENER_PCRADMN =
  18. (DESCRIPTION =
  19. (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-myvm.dbversity.com )(PORT = 7777))
  20. )
  21. )
  23. –restart LISTENER_PCRADMN
  25. myodbc3 =
  26. (DESCRIPTION =
  27. (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-myvm.dbversity.com)(PORT = 7777))
  28. (CONNECT_DATA =
  29. (SID = myodbc3))
  30. (HS = OK)
  31. )
  33. bash-4.1$ tnsping myodbc3
  35. TNS Ping Utility forLinux: Version 11.2.0.4.0 – Production on27-OCT-2015 11:00:16
  37. Copyright (c) 1997, 2013, Oracle.  Allrights reserved.
  39. Used TNSNAMES adapter toresolve the alias
  40. Attempting tocontact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-myvm.dbversity.com)(PORT = 7777)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))
  41. OK (20 msec)

 

  1. Configure ODBC listener

 

  1. bash-4.1$ cd $ORACLE_HOME/hs/admin
  2. bash-4.1$ vi initmyodbc3.ora
  3. =>
  4. HS_FDS_CONNECT_INFO = myodbc3
  5. HS_FDS_TRACE_FILE_NAME=odbc_test.log
  6. HS_FDS_TRACE_LEVEL=4
  7. HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
  8. HS_FDS_SUPPORT_STATISCTICS = FALSE
  9. HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
  10. setODBCINI = /etc/odbc.ini

 

 

  1. Test dblink

 

# dblink using tns entry

  1. SQL>  createpublic database link myodbc connect to “gen_fad_etl_rw_s1” identified by “<password>” using ‘myodbc3’;
  3. Databaselink created.
  5. SQL> selectcount(1) from M_SUBGL_TRF_PRC@myodbc;
  7. COUNT(1)
  8. ———-
  9. 0

 

# dblink using connection string

  1. SQL> createdatabase link myodbc2 connect to “gen_fad_etl_rw_s1” identified by “<password>”
  2. using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-myvm.dbversity.com)(PORT = 7777)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))’;
  4. Databaselink created.
  6. SQL> selectcount(1) from dual@myodbc2;
  8. COUNT(1)
  9. ———-
  10. 1

 

  1. Known Issue

7.1  Error: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=

 

  1. SQL> selectcount(1) from “M_SUBGL_TRF_PRC”@myodbc;
  2. selectcount(1) from “M_SUBGL_TRF_PRC”@myodbc
  3. *
  4. ERROR atline 1:
  5. ORA-28511: lost RPC connectionto heterogeneous remote agent usingSID=ORA-28511: lost RPC connection to heterogeneous remote agent using
  6. SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-myvm.dbversity.com)(PORT=
  7. 7777))(CONNECT_DATA=(SID=myodbc3)))
  8. ORA-02063: preceding line fromMYODBC
  9. Process ID: 1848
  10. Session ID: 15 Serial number: 511

 

Solution: Remove HS_FDS_SQLLEN_INTERPRETATION=32 from $ORACLE_HOME/hs/admin/initmyodbc3.ora.

 

  • Ask Question
    • Tags: , , ,