4 Replies Latest reply: May 10, 2013 12:04 PM by AlexVAV RSS

    Setup heterogeneous service to connect MySQL from Oracle

    AlexVAV
      Hello,

      I have got an issue with heterogeneous service configuration like

      SQL> select * from colour@mysql_local;
      select * from colour@mysql_local
      *
      ERROR at line 1:
      ORA-28546: connection initialization failed, probable Net8 admin error
      ORA-02063: preceding line from MYSQL_LOCAL

      If anybody can advise me what is wrong in conf or might be I missed something

      Thanks

      ----------------
      Environment:
      OS: Windows 7 Pro
      Oracle 11.2.0.3
      MySQL: 5.6

      Configuration:
      --
      System ODBC configured : MYSQL_LOCAL
      Tested connection - sucessful
      --
      Listener
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )
      (SID_DESC =
      (SID_NAME = MYSQL_LOCAL)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM=dg4odbc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = C:\app

      --
      Tnsnames.ora
      MYSQL_LOCAL =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SID = MYSQL_LOCAL)
      (HS = OK)
      )
      )

      --
      initdg4odbc.ora
      HS_FDS_CONNECT_INFO = mysql_local
      HS_FDS_TRACE_LEVEL = OFF


      --
      SQL> conn / as sysdba
      SQL> create public database link MYSQL_LOCAL connect to root identified by 12346 using 'MYSQL_LOCAL';


      C:\Windows\system32>lsnrctl status

      LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 08-MAY-2013 17:44:24

      Copyright (c) 1991, 2011, Oracle. All rights reserved.

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
      Start Date 08-MAY-2013 17:44:05
      Uptime 0 days 0 hr. 0 min. 18 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File C:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
      Listener Log File C:\app\diag\tnslsnr\D-9RGC8X1\listener\alert\log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "MYSQL_LOCAL" has 1 instance(s).
      Instance "MYSQL_LOCAL", status UNKNOWN, has 1 handler(s) for this service...
      Service "QA_IMS_MYSQL" has 1 instance(s).
      Instance "QA_IMS_MYSQL", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully

      C:\Windows\system32>tnsping mysql_local

      TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 08-MAY-2013 17:44:36

      Copyright (c) 1997, 2011, Oracle. All rights reserved.

      Used parameter files:
      C:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL_LOCAL) (HS = OK)))
      OK (0 msec)


      SQL> conn / as sysdba
      Connected.
      SQL> select * from colour@mysql_local;
      select * from colour@mysql_local
      *
      ERROR at line 1:
      ORA-28546: connection initialization failed, probable Net8 admin error
      ORA-02063: preceding line from MYSQL_LOCAL
        • 1. Re: Setup heterogeneous service to connect MySQL from Oracle
          Mkirtley-Oracle
          Alex,
          In the listener.ora you have -

          (SID_NAME = MYSQL_LOCAL)

          which is also referred to in the tnsnames.ora entry for the gateway.
          However, the initialisation file you have posted is called -
          initdg4odbc.ora

          Rename this to -

          initMYSQL_LOCAL.ora

          and try a select from a new SQLPLUS session.
          The SID in the listener.ora must have a matching gateway init<sid>.ora file.

          Regards,
          Mike
          • 2. Re: Setup heterogeneous service to connect MySQL from Oracle
            AlexVAV
            Hi Mike,

            Thank for the advice, but it didn't help...

            I've renamed initdg4odbc.ora to initMYSQL_LOCAL.ora and placed it into three different locations like ORACLE_HOME\dbs, ORACLE_HOME\database and ORACLE_HOME\hs. Then did a restart the listener and database (just in case) but got the same again:

            SQL> conn / as sysdba
            Connected.
            SQL> select * from colour@mysql_local;
            select * from colour@mysql_local
            *
            ERROR at line 1:
            ORA-28546: connection initialization failed, probable Net8 admin error
            ORA-02063: preceding line from MYSQL_LOCAL

            Any other ideas?
            • 3. Re: Setup heterogeneous service to connect MySQL from Oracle
              Mkirtley-Oracle
              Hi,
              The gateway file initMYSQL_LOCAL.ora should be placed in the directory -

              $ORACLE_HOME\hs\admin

              which is the expected location. In your setup this should be -

              C:\app\product\11.2.0\dbhome_1\hs\admin

              If the file is already in that location then make sure that in the listener.ora and tnsnames.ora that in each section every line except the first line starts with at least one space -

              SID_LIST_LISTENER =
              <space>(SID_LIST =
              <space>(SID_DESC =
              ...
              ...

              LISTENER =
              <space>(DESCRIPTION_LIST =
              ...
              ...

              Tnsnames.ora
              MYSQL_LOCAL =
              <space>(DESCRIPTION =
              <space>(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              ...
              ...

              However, there is also a problem with the tnsnames.ora file. You have -

              Tnsnames.ora
              MYSQL_LOCAL =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              (CONNECT_DATA =
              (SID = MYSQL_LOCAL)
              (HS = OK)
              ) <=== move this bracket
              )

              but it should be -

              Tnsnames.ora
              MYSQL_LOCAL =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              (CONNECT_DATA =
              (SID = MYSQL_LOCAL)
              ) <=== bracket moved here
              (HS = OK)
              )

              Of course, with spaces as shown above. They are often lost in the forums.
              You may also need to change -
              (HOST = localhost)
              to -
              (HOST = <actual host name>)
              but it depends on your setup.

              Try these changes and let me know what happens.

              Regards,
              Mike
              • 4. Re: Setup heterogeneous service to connect MySQL from Oracle
                AlexVAV
                Hi Mike,

                Done. Now it is fixed.
                Finally, two things have been changed.
                1) rename initdg4odbc.ora to initMYSQL_LOCAL.ora (the location I've mentioned before was correct ORACLE_HOME\hs\admin)
                2) the bracket in tnsnames.ora
                Tnsnames.ora
                MYSQL_LOCAL =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                (CONNECT_DATA =
                (SID = MYSQL_LOCAL)
                (HS = OK)
                ) <=== move this bracket
                )

                but it should be -

                Tnsnames.ora
                MYSQL_LOCAL =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                (CONNECT_DATA =
                (SID = MYSQL_LOCAL)
                ) <=== bracket moved here
                (HS = OK)
                )

                all other formatting was done properly from very beginning.

                Thank you very much for your help.

                Alexey.