This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 4, 2012 1:34 AM by kgronau RSS

Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene

indytoatl Newbie
Currently Being Moderated
When I run the select statement below I get an error message that the connection was lost. When I run SQL profiler on the SQL db I can see the account login and logout.
Any ideas what may be the issue here?

Oracle 11g - running on Windows 7
SQL 2008 R2 - running on Windows 2008 R2
CentOS Linux - Gateway only

SQL> select * from my_table@mygateway;
ERROR:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=My_Linux_Svr.Domain.org)(PORT=1521))

(CONNECT_DATA=(SID=dg4msql)))
ORA-02063: preceding line from MYGATEWAY
Process ID: 16632
Session ID: 134 Serial number: 27



no rows selected

SQL>
  • 1. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    is it happening when you try to select from any table or just from a dedicated table?
    Please provide a gateway trace level 255 (can be set in the gateway init file by changing HS_FDS_TRACE_LEVEL to 255), then open a new SQL*Plus session and select from the table. Post the trace file generated in the log directory. Please do not forget to switch off gateway tracing again once you created the trace file.
  • 2. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    I get the same error from any table.

    I used the default instance and listener names for the gateway - d4gmsql.
    My path is /home/oracle/product/11.2.0/gateway

    I modified the initd4msql.ora file as follows:

    [root@localhost admin]# cat initdg4msql.ora
    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server

    HS_FDS_CONNECT_INFO=[drw-sql01].utility
    # alternate connect format is hostname/serverinstance/databasename
    HS_FDS_TRACE_LEVEL=255
    HS_FDS_TRACE_FILE_NAME=
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER

    I then restarted the listener as follows:
    lsnrctl stop
    lsnrctl start

    [root@localhost admin]# lsnrctl start

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-NOV-2012 09:59:59

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

    Starting /home/oracle/product/11.2.0/gateway/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    System parameter file is /home/oracle/product/11.2.0/gateway/network/admin/listener.ora
    Log messages written to /home/oracle/product/11.2.0/gateway/log/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.109.4.200)(PORT=1521)))

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=10.109.4.200)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 27-NOV-2012 09:59:59
    Uptime 0 days 0 hr. 0 min. 0 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /home/oracle/product/11.2.0/gateway/network/admin/listener.ora
    Listener Log File /home/oracle/product/11.2.0/gateway/log/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.109.4.200)(PORT=1521)))
    Services Summary...
    Service "dg4msql" has 1 instance(s).
    Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully


    I ran the query again from a new session but I do not see a trace file. Where should I be looking?
  • 3. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    Please remove the parameter HS_FDS_TRACE_FILE_NAME=. Then start again a new session, execute the select.
    The gateway trace is located in /home/oracle/product/11.2.0/gateway/dg4msql/log
  • 4. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    I removed that line and restarted the listener services but still no trace file. I also started a new session. Any ideas? I do see trace files from 11/6/2012 but I don't remember turning it on or off.
  • 5. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    Could you please post the gateway init file?
  • 6. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server

    #
    # HS init parameters
    #
    #HS_FDS_CONNECT_INFO=[My_Linux_Srv]:1433//utility
    # alternate connect format is hostname/serverinstance/databasename
    #HS_FDS_TRACE_LEVEL=ON
    #HS_FDS_TRACE_FILE_NAME= /home/tracefile.log
    #HS_FDS_RECOVERY_ACCOUNT=RECOVER
    #HS_FDS_RECOVERY_PWD=RECOVER


    HS_FDS_CONNECT_INFO=[My_linux_Srv].utility
    # alternate connect format is hostname/serverinstance/databasename
    HS_FDS_TRACE_LEVEL=255
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER
  • 7. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    looks good. Could you please comment out the second HS_FDS_CONNECT_INFO=[My_linux_Srv].utility as well?
    I want to check if we're really using that init file and you should then get a different error message.
    BTW, does a "select user from dual@<database link using the gateway>" work?
  • 8. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    I commented out the connection string line and the same error occurred. I also ran your query with the same error. How do I force it to use the right init file? start database ->Path?
  • 9. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    Please post the gateway listener file. the tnsnames.ora and the create database link statement (do not forget to remove UID and PWD).
  • 10. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    I wasn't able to retreive the db link statement. I can just drop and re-create a new one. This should be a public link for testing.

    Gateway Server: My_Linux_Svr
    Gateway SID: dg4msql
    SQL Server: My_SQL_Svr
    SQL Database: Utility
    Oracle Database Wrkstn: My_Windows7
    Oracle SID: Adventur

    [root@localhost admin]# cat initd4gmsql.ora
    #HS_FDS_CONNECT_INFO=[My_Linux_Srv].utility
    # alternate connect format is hostname/serverinstance/databasename
    HS_FDS_TRACE_LEVEL=255
    HS_FDS_TRACE_FILE_NAME=
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER


    [root@localhost admin]# cat listner.ora
    LISTENER =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=10.109.4.200)(PORT=1521))
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=dg4msql)
    (ORACLE_HOME=/home/oracle/product/11.2.0/gateway)
    (PROGRAM=dg4msql)
    )
    )

    CONNECT_TIMEOUT_LISTENER = 0



    [root@localhost admin]# cat tnsnames.ora
    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=dg4msql)
    (ORACLE_HOME=/home/oracle/product/11.2.0/gateway)
    (PROGRAM=dg4msql)
    )
    )
  • 11. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    the tnsnames entry you copied is not correct as it looks like your listener SID - maybe copy/paste issue. Can you please check again?
  • 12. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    Sorry, you were correct. I put in the right tnsnames info and I also found my create db link statement. See below


    [root@localhost admin]# cat tnsnames.ora

    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=TCP)
    (HOST=host_10.109.4.200)
    (PORT=1521)
    )
    (CONNECT_DATA=
    (SID=dg4msql))
    (HS=OK))

    )


    CREATE PUBLIC DATABASE LINK mygateway CONNECT TO "oracle" IDENTIFIED BY "password" USING ’dg4msql’;
  • 13. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    indytoatl Newbie
    Currently Being Moderated
    The one thing I didn't do was add the second line below because it caused a login issue on CentOS after I reboot last time.
    Could that be the issue?

    $ PATH=$ORACLE_HOME/bin:$PATH;export PATH
    $ LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
  • 14. Re: Oracle Gateway to SQL server - ORA-28511: lost RPC connection to heterogene
    kgronau Guru
    Currently Being Moderated
    Yes, that could be a possible issue. I would suggest to add the LD_LIBRARY_PATH to the listener:
    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=dg4msql)
    (ORACLE_HOME=/home/oracle/product/11.2.0/gateway)
    (ENV="LD_LIBRARY_PATH=/home/oracle/product/11.2.0/gateway/dg4msql/driver/lib:/home/oracle/product/11.2.0/gateway/lib")
    (PROGRAM=dg4msql)
    )
    )

    Then stop and start the listener (do NOT reload it).

    And just a question regarding the tnsnames.ora file. You use (HOST=host_10.109.4.200) in this file, but the listener uses only (HOST=10.109.4.200). Is that a typo, or is the host address resolved correctly?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points