4 Replies Latest reply: Mar 25, 2013 10:34 AM by EdStevens RSS

    Access MySQL from Oracle Tnsping problem.

    998365
      Dears
      I am trying to connect to MysqlDB from Oracle.
      I have installed Oracle Gateway and ODBC diver on Linux server.
      Oracle gateway was installed on same server with Oracle Database but in different home and it has a different listener.
      please look at my configuration files which I have created and maintained to established connections to MySqlDB.
      I think I did some mistake and seize up in it so I could not find a solution. I am trying to fix it as well but if somebody can help me it will be very nice.

      Gateway version is 11.2.0.3.0 and it is on ORAHOME /oracle/11203/GTW and its listener port is 1522.
      I am able to tnsping port 1522 (dg4odbc) from gateways home. Gateway configuration files are below.
      ####/etc/odbc.ini####
      [ODBC Data Sources]
      MySQL = MySQL ODBC Driver 5.2

      [MySQL]
      Driver = /usr/lib64/libmyodbc5a.so
      DATABASE = veritabani
      DESCRIPTION = MySQL ODBC 5.2 Driver
      PORT = nnnn
      SERVER = x.x.x.x
      UID = kullanici
      PWD = sifre
      CHARSET = latin1
      TRACEFILE = /tmp/myodbc-demodsn.trc
      TRACE = OFF

      ####/oracle/11203/GTW/hs/admin/initdg4odbc.ora####
      HS_FDS_CONNECT_INFO = MySQL
      HS_FDS_TRACE_LEVEL = 0
      HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5a.so
      set ODBCINI=/etc/odbc.ini
      set HOME=/home/oracle

      ####/oracle/11203/GTW/network/admin/listener.ora####
      LISTENER =
      (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1522))
      )

      SID_LIST_LISTENER=
      (SID_LIST=
      (SID_DESC=
      (SID_NAME=dg4odbc)
      (ORACLE_HOME=/oracle/11203/GTW)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/11203/GTW/lib)
      )
      )

      ####/oracle/11203/GTW/network/admin/tnsnames.ora####
      dg4odbc =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)
      (HOST = hostname)
      (PORT = 1522)
      )

      (CONNECT_DATA =
      (SID = dg4odbc)
      )
      (HS = OK)
      )

      Oracle Database version is 11.2.0.2.0 and Its on ORAHOME /oracle/11202/BDW and its listener port is 1521.
      I am not able to tnsping port 1522 (dg4odbc) from Database Home. Database configuration file is below.
      ####/oracle/11202/BDW/network/admin/tnsnames.ora####
      *
      *
      dg4odbc =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1522)))
      (CONNECT_DATA = (SID = dg4odbc))
      (HS=OK))

      Also I have created a DBLINK and tried to get info from MySqlDB but it did not work as usual.

      CREATE PUBLIC DATABASE LINK dg4odbc CONNECT TO "kullanici" IDENTIFIED BY "sifre" USING ’dg4odbc’;

      SQL> select * from kullanici@dg4odbc;
      select * from kullanici@dg4odbc
      *
      ERROR at line 1:
      ora-12154 tns could not resolve the connect identifier specified

      Thank you so much to everybody who reads and writes this...

      Best regards,
      Eren.

      Edited by: BeymenAdmin on Mar 21, 2013 8:52 AM
        • 1. Re: Access MySQL from Oracle Tnsping problem.
          EdStevens
          BeymenAdmin wrote:
          Dears
          I am trying to connect to MysqlDB from Oracle.
          I have installed Oracle Gateway and ODBC diver on Linux server.
          Oracle gateway was installed on same server with Oracle Database but in different home and it has a different listener.
          please look at my configuration files which I have created and maintained to established connections to MySqlDB.
          I think I did some mistake and seize up in it so I could not find a solution. I am trying to fix it as well but if somebody can help me it will be very nice.

          Gateway version is 11.2.0.3.0 and it is on ORAHOME /oracle/11203/GTW and its listener port is 1522.
          I am able to tnsping port 1522 (dg4odbc) from gateways home. Gateway configuration files are below.
          ####/etc/odbc.ini####
          [ODBC Data Sources]
          MySQL = MySQL ODBC Driver 5.2

          [MySQL]
          Driver = /usr/lib64/libmyodbc5a.so
          DATABASE = veritabani
          DESCRIPTION = MySQL ODBC 5.2 Driver
          PORT = nnnn
          SERVER = x.x.x.x
          UID = kullanici
          PWD = sifre
          CHARSET = latin1
          TRACEFILE = /tmp/myodbc-demodsn.trc
          TRACE = OFF

          ####/oracle/11203/GTW/hs/admin/initdg4odbc.ora####
          HS_FDS_CONNECT_INFO = MySQL
          HS_FDS_TRACE_LEVEL = 0
          HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5a.so
          set ODBCINI=/etc/odbc.ini
          set HOME=/home/oracle

          ####/oracle/11203/GTW/network/admin/listener.ora####
          LISTENER =
          (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1522))
          )

          SID_LIST_LISTENER=
          (SID_LIST=
          (SID_DESC=
          (SID_NAME=dg4odbc)
          (ORACLE_HOME=/oracle/11203/GTW)
          (PROGRAM=dg4odbc)
          (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/11203/GTW/lib)
          )
          )

          ####/oracle/11203/GTW/network/admin/tnsnames.ora####
          dg4odbc =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)
          (HOST = hostname)
          (PORT = 1522)
          )

          (CONNECT_DATA =
          (SID = dg4odbc)
          )
          (HS = OK)
          )

          Oracle Database version is 11.2.0.2.0 and Its on ORAHOME /oracle/11202/BDW and its listener port is 1521.
          I am not able to tnsping port 1522 (dg4odbc) from Database Home. Database configuration file is below.
          ####/oracle/11202/BDW/network/admin/tnsnames.ora####
          *
          *
          dg4odbc =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1522)))
          (CONNECT_DATA = (SID = dg4odbc))
          (HS=OK))

          Also I have created a DBLINK and tried to get info from MySqlDB but it did not work as usual.

          CREATE PUBLIC DATABASE LINK dg4odbc CONNECT TO "kullanici" IDENTIFIED BY "sifre" USING ’dg4odbc’;

          SQL> select * from kullanici@dg4odbc;
          select * from kullanici@dg4odbc
          *
          ERROR at line 1:
          ora-12154 tns could not resolve the connect identifier specified
          see: [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154 TNS:could not resolve the connect identifier specified


          Remember that the database from which you issued the "select * from kullanici@dg4odbc;" is, at that point, acting just like any other client trying to connect to a remote database.

          Thank you so much to everybody who reads and writes this...

          Best regards,
          Eren.

          Edited by: BeymenAdmin on Mar 21, 2013 8:52 AM
          • 2. Re: Access MySQL from Oracle Tnsping problem.
            damorgan
            I recommend you close this thread and open one in a forum dedicated to either Oracle Gateway products or MySQL. I doubt anyone here will be working with this combination of products.

            Look at other forum questions before asking yours to make sure you've chose the correct forum for your next posting.
            • 3. Re: Access MySQL from Oracle Tnsping problem.
              998365
              Dear Ed,

              Thanks your help, I found my fault where was in . SQLNET.ora has the prameter NAMES.DEFAULT_DOMAIN. I have changed identifier from dg4odbc to dg4odbc.local.domain in TNSNAMES.ora

              TNS problem was solved with your help however I still have problem to connect remote MySql DB

              SQL> select * from "user"@dg4odbc.local.domain
              2 ;
              select * from "user"@dg4odbc.local.domain
              *
              ERROR at line 1:
              ora-28546 connection initialization failed probable net8 admin error
              ORA-02063: preceding line from DG4ODBC
              • 4. Re: Access MySQL from Oracle Tnsping problem.
                EdStevens
                BeymenAdmin wrote:
                Dear Ed,

                Thanks your help, I found my fault where was in . SQLNET.ora has the prameter NAMES.DEFAULT_DOMAIN. I have changed identifier from dg4odbc to dg4odbc.local.domain in TNSNAMES.ora

                TNS problem was solved with your help however I still have problem to connect remote MySql DB

                SQL> select * from "user"@dg4odbc.local.domain
                2 ;
                select * from "user"@dg4odbc.local.domain
                *
                ERROR at line 1:
                ora-28546 connection initialization failed probable net8 admin error
                ORA-02063: preceding line from DG4ODBC
                At that point, you'd be better of in the [url https://forums.oracle.com/forums/forum.jspa?forumID=63]heterogeneous connectivity forum