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