| Remote DB access problems accross the WAN [message #67414] |
Thu, 08 July 2004 08:00 |
Gene
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
We are experincing a problem with accessing a remote DB across the WAN using SQL*Net 2.3. The Oracle Server is 7.3.2.
Oracle Consulting created a data replication application for us years ago that incorporates stored procs updating remote DBs across DB links. The stored procs are called via job queue registration.
I do not believe the system had been fully utilized and when we tried enabling replication across three remote DBs, at any one time only two will replicate. As I have been troubleshooting, the issue moved from on DB to another, so for me that removed any perceived notion that the problem could be related to a local firewall policy.
When the replication proc is running in a session after being kicked off by an SNPn process it just seems to get hung. Even using the 'alter system...' command to kill the session does not seem to clear it. The session is marked for kill, but exists and blocks subsequent job executions until the listener/server are bounced.
I have tried to reproduce the condition by executing the cmds using the SQL*Plus utlity. I was finally able to reproduce what I think is the error condition, but can not explain it.
Periodically, when accessing a table on one of the remote DBs I would get error ORA-12505 'listener could not resolve SID given connect descriptor'. While I do not know why this error was occuring, the stored procs could handle the error and would continue on. If anyone has ideas on this error, that would give me peace of mind.
The real problem was when a random 'select...' was performed against a remote DB table, control was never returned back to the SQL*Plus prompt. I left the cmd alone for over 90 min and it never returned control. Again, just as in the stored proc, I tried to kill the session, but it is just marked as killed. It will not terminate until the listener at the remote DB is bounced.
Any ideas why the query NEVER returns when accessing a remote DB?????
Through my research I have found refences to the CONNECT_TIMEOUT_LISTENER setting in the listener.ora file. Currently set to 10 sec. I am thinking of increasing this to 60 secs. Also found a reference to SQLNET.EXPIRE_TIME setting in the sqlnet.ora file. This is not currently set. I am thinking of setting it to the default of 10 minutes.
Could the absence of the setting in the sqlnet.ora file be causing this problem??
Any insight would be greatly appreciated.
Thanks - Gene
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
