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;
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
ORA-02063: preceding line from MYGATEWAY
Process ID: 16632
Session ID: 134 Serial number: 27
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.
TNSLSNR for Linux: Version 188.8.131.52.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
Version TNSLSNR for Linux: Version 184.108.40.206.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
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...
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?
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?
Yes, that could be a possible issue. I would suggest to add the LD_LIBRARY_PATH to the listener:
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?