3

One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason.

We tried checking iptables but we have no state-based rules.

We also have older servers on the same switch so we know it isn't a switch issue. My hypothesis: It's a client issue.

This is kind of a shot in the dark but can anyone suggest some troubleshooting steps to help out? I'll update the question with whatever details you may need to make a recommendation. Just ask in comments.

Update

Per comments:

  • I tested telneting to the Oracle DB server to port 1521. That worked.
  • Also, the initial test query works. However, after an hour, when I try the query again I get ORA-03135: connection lost contact. Full error below.
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 14848
Session ID: 1219 Serial number: 1551
  • Can you connect from that box by any other means? – dezso Nov 12 '12 at 20:32
  • 1
    The standard Oracle listener port is 1521. Try a straight telnet to it and see if it connects (from your question I've assumed that you try to be a sysadmin). If the Oracle listener is not on port 1521, ask the DBA for the output of lsnrctl status on the server and edit your question with the result. It's probably a firewall problem. – Philᵀᴹ Nov 13 '12 at 1:27
  • I could telnet to the port without an issue. I could also connect to the Oracle server and perform a query. However, after an hour I get "ORA-03135: connection lost contact". – Belmin Fernandez Nov 13 '12 at 13:03
  • Is there a way to see all the settings sql*plus has loaded? Or a default location for the configuration? – Belmin Fernandez Nov 13 '12 at 13:07
2

I had a similar problem after a firewall upgrade. Users could connect to the database just fine, but if their session was idle for some time, the connection would be terminated by firewall. Our solution was to add

SQLNET.EXPIRE_TIME = 10

to sqlnet.ora file. This keeps connections alive by probing them in 10 minute intervals.

| improve this answer | |
  • This seems to have solved the issue! Thank you! – Belmin Fernandez Nov 18 '12 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.