8 Replies Latest reply on May 9, 2019 12:46 PM by mxallen-Oracle

    dg4odbc connect to mysql error ORA-28545

    Eggo

      Hi,

          I have Oracle heterogeneous gateways and mysql connector 5.1 installed on a Gateways server.  From isql test it works fine but when query from sqlplus after a dblink was created i got error below. Please help.

      Thank you very much,

       

      SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo;

      select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo

                                                   *

      ERROR at line 1:

      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 MYSQLDBAREPO

       

      Here is my odbcinst.ini, odbc.ini, listener.ora, initmysqldbarepo.ora and tnsnames.ora

      12cR2_home> cat /etc/odbcinst.ini

      # Example driver definitions

       

      # Driver from the postgresql-odbc package

      # Setup from the unixODBC package

      [PostgreSQL]

      Description     = ODBC for PostgreSQL

      Driver          = /usr/pgsql-11/lib/psqlodbc.so

      #Driver         = /usr/lib/psqlodbc.so

      #Setup          = /usr/lib/libodbcpsqlS.so

      #Driver64       = /usr/lib64/psqlodbc.so

      #Setup64        = /usr/lib64/libodbcpsqlS.so

      FileUsage       = 1

       

      # Driver from the mysql-connector-odbc package

      # Setup from the unixODBC package

      [MySQL]

      Description     = ODBC for MySQL

      #Driver         = /usr/lib/libmyodbc5.so

      #Setup          = /usr/lib/libodbcmyS.so

      Driver          = /usr/lib64/libmyodbc5.so

      Setup           = /usr/lib64/libodbcmyS.so

      FileUsage       = 1

      (GC92@bmw157:/users/gc92)

      12cR2_home> cat /etc/odbc.ini

      [pgipollnew]

      Description         = PostgreSQL connection to pgipollnew

      Driver              = /usr/pgsql-11/lib/psqlodbc.so

      Database            = pgipollnew

      Servername          = pgipollnew.domain

      UserName            = dblinker

      Password            = dblinkerPWD

      Port                = 5432

      Protocol            = 10.6

      ReadOnly            = No

      RowVersioning       = No

      ShowSystemTables    = No

      ConnSettings        =

       

      [mysqldbarepo]

      Description  = Connector/ODBC 5.1 Driver

      Driver       = /usr/lib64/libmyodbc5.so

      Database     = mysqldbarepo

      SERVER       = mysqldbarepo.domain

      PORT         = 3306

      USER         = dblinker

      Password     = dblinkerPWD

      OPTION       = 0

      SOCKET       =

       

      (ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/network/admin)

      ODBC_Gateway> cat listener.ora

      # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora

      # Generated by Oracle configuration tools.

       

      ADR_BASE_LISTENER = /u01/app/oracle/product/12.2.0/gateway

       

      INBOUND_CONNECT_TIMEOUT_= 0

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = bmw157.domain)(PORT = 1521))

          )

        )

      SID_LIST_LISTENER =

        (SID_LIST =

            (SID_DESC =

               (SID_NAME = pgipollnew)

               (ORACLE_HOME = /u01/app/oracle/product/12.2.0/gateway)

               (ENVS = "LD_LIBRARY_PATH=/usr/lib64:/usr/pgsql-11/lib:/u01/app/oracle/product/12.2.0/gateway/lib")

               (PROGRAM = dg4odbc)

            )

            (SID_DESC =

               (SID_NAME = mysqldbarepo)

               (ORACLE_HOME = /u01/app/oracle/product/12.2.0/gateway)

               (ENVS = "LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/12.2.0/gateway/lib")

               (PROGRAM = dg4odbc)

            )

        )

       

      (ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)

      ODBC_Gateway> cat initmysqldbarepo.ora

      # This is a sample agent init file that contains the HS parameters that are

      # needed for the Database Gateway for ODBC

       

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO = mysqldbarepo

      HS_FDS_TRACE_LEVEL = 0

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

       

      #

      # ODBC specific environment variables

      #

      set ODBCINI=/etc/odbc.ini

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set <envvar>=<value>

       

      (ORACLE@bmw156:/u01/app/oracle/product/12.2.0/db/network/admin)

      boomitst> cat tnsnames.ora

      # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora

      # Generated by Oracle configuration tools.

       

      LISTENER_BOOMITST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = bmw156.domain)(PORT = 1521))

       

      PGIPOLLNEW  =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = bmw167.domain)(PORT = 1521))

          (CONNECT_DATA = (SID = pgipollnew))

          (HS = OK)

        )

       

      MYSQLDBAREPO =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = bmw167.domain)(PORT = 1521))

          (CONNECT_DATA = (SID = mysqldbarepo))

          (HS = OK)

        )

       

      (ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)

      ODBC_Gateway> lsnrctl status

       

      LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-MAY-2019 11:30:51

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bmw157.domain)(PORT=1521)))

      STATUS of the LISTENER

      ------------------------

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production

      Start Date                07-MAY-2019 11:25:17

      Uptime                    0 days 0 hr. 5 min. 34 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   /u01/app/oracle/product/12.2.0/gateway/network/admin/listener.ora

      Listener Log File         /u01/app/oracle/product/12.2.0/gateway/diag/tnslsnr/bmw157/listener/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bmw157.domain)(PORT=1521)))

      Services Summary...

      Service "mysqldbarepo" has 1 instance(s).

        Instance "mysqldbarepo", status UNKNOWN, has 1 handler(s) for this service...

      Service "pgipollnew" has 1 instance(s).

        Instance "pgipollnew", status UNKNOWN, has 1 handler(s) for this service...

      The command completed successfully

       

      (ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)

      ODBC_Gateway> isql -v mysqldbarepo

      +---------------------------------------+

      | Connected!                            |

      |                                       |

      | sql-statement                         |

      | help [tablename]                      |

      | quit                                  |

      |                                       |

      +---------------------------------------+

      SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT

      +---------------------+

      | count(*)            |

      +---------------------+

      | 8469                |

      +---------------------+

      SQLRowCount returns 1

      1 rows fetched

        • 1. Re: dg4odbc connect to mysql error ORA-28545
          EdStevens

          You left out the very first link in the chain, and that is the definition of the db link.  Without that starting point, I wouldn't even attempt to try to thread my way through the rest of the stuff.

           

          You should find this useful -> https://edstevensdba.wordpress.com/2018/09/19/the-oracle-transparent-gateway/

          • 2. Re: dg4odbc connect to mysql error ORA-28545
            Choc Cac

            Check and do not find anything wrong with the configurations, the query works

            select * dual @ mysqldbarepo

             

             

             

            "but when query from sqlplus after a dblink was created i got error below. Please help."

             

             

            What query did you write you can share?

            • 3. Re: dg4odbc connect to mysql error ORA-28545
              Eggo

              SQL>  select db_link, username, host from dba_db_links where host like 'mysql%';

               

               

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              --------------------------------------------------------------------------------

              HOST

              --------------------------------------------------------------------------------

              MYSQLDBAREPO.DOMAIN

              cusys

              mysqldbarepo

              • 4. Re: dg4odbc connect to mysql error ORA-28545
                EdStevens

                Eggo wrote:

                 

                SQL> select db_link, username, host from dba_db_links where host like 'mysql%';

                 

                 

                DB_LINK

                --------------------------------------------------------------------------------

                USERNAME

                --------------------------------------------------------------------------------

                HOST

                --------------------------------------------------------------------------------

                MYSQLDBAREPO.CIT.CORNELL.EDU

                cusys

                mysqldbarepo

                So, your link references the tnsnames entry 'mysqldbarep'.  That entry looks like this:

                 

                MYSQLDBAREPO =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = bmw167.domain)(PORT = 1521))

                    (CONNECT_DATA = (SID = mysqldbarepo))

                    (HS = OK)

                  )

                ... and specifies that the request is to go to HOST = bmw167.domain.

                 

                But you show us a listener that reports as follows:

                 

                ODBC_Gateway> lsnrctl status

                 

                LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-MAY-2019 11:30:51

                 

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

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bmw157.domain)(PORT=1521)))

                STATUS of the LISTENER

                So the connection request is being sent to a listener on bmw167.domain, but you show us a listener that says he is on bmw157.domain.

                • 5. Re: dg4odbc connect to mysql error ORA-28545
                  Eggo

                  Okay, make the correct in tnsnames.ora as below and retest getting new error message/.

                   

                  MYSQLDBAREPO =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = bmw157.domain)(PORT = 1521))

                      (CONNECT_DATA = (SID = mysqldbarepo))

                      (HS = OK)

                    )

                   

                  SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo;

                  select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo

                                                               *

                  ERROR at line 1:

                  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

                  [

                  • 6. Re: dg4odbc connect to mysql error ORA-28545
                    EdStevens

                    Eggo wrote:

                     

                    Okay, make the correct in tnsnames.ora as below and retest getting new error message/.

                     

                    MYSQLDBAREPO =

                    (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = bmw157.domain)(PORT = 1521))

                    (CONNECT_DATA = (SID = mysqldbarepo))

                    (HS = OK)

                    )

                     

                    SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo;

                    select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo

                    *

                    ERROR at line 1:

                    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

                    [

                    That's it?  The "returned" message from the non-Oracle system is just a left bracket?

                     

                    Which server were the rest of the config files located?  157 or 167?

                    Did you trace the rest of the config, referencing the step-by-step walk-through I linked to?  While on that one we were going to SQL Dev, everything works exactly the same regardless of the brand of the target database.

                    • 7. Re: dg4odbc connect to mysql error ORA-28545
                      L. Fernigrini

                      Start with the simplest....

                       

                      SELECT * FROM dual@mysqldbarepo;

                      • 8. Re: dg4odbc connect to mysql error ORA-28545
                        mxallen-Oracle

                        For the bracket error, add the following to the DG4ODBC parameter file init<dg4odbc>.ora -

                         

                        HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

                         

                        Regards,

                        Matt