Re: ORA-3135 Fixed by PC Restart
From: Charlotte Hammond <charlottejanehammond_at_yahoo.com>
Date: Thu, 3 Oct 2013 09:34:59 -0700 (PDT)
Message-ID: <1380818099.82527.YahooMailNeo_at_web140006.mail.bf1.yahoo.com>
Hi Tim / List,
Thanks for your hints - I've got my SQL*Plus environment sorted now and it's producing the following error when the connection becomes impossible: ORA-28547: connection to server failed, probable Oracle Net admin error. This occurs at the same time as ORA-03135 from SQL*Developer.
Unfortunately we're not licenced to run ASH on this database but I enabled 10046 tracing for the whole database (not a problem as it's lightly loaded) and I don't see any trace files for the failing connections which suggests that it's not even getting as far as the database.
I'm still inclined to think there's a problem on the client side. Today when we started getting the ORA-03135s (and ORA-28547s) we did not restart the PC as we usually do. After about 3 hours the problem went away and we could connect again, so whatever is breaking seems to be transient. During these 3 hours we could continue to connect to the database from other PCs suggesting no issues with the network, listener etc.
Any other thoughts?!
Many thanks,
Charlotte
----- Forwarded Message -----
From: Tim Gorman <tim_at_xxxxxxxxx>
To: oracle-l_at_xxxxxxxxxxxxx
Date: Wed, 02 Oct 2013 15:32:51 -0600
Charlotte,
The ORA-1041 indicates that there is something wrong with your Oracle client-side installation, or there is something wrong with the environment, because that error means that SQL*Plus cannot find the language-specific file to translate the error number (i.e. "-3135") into the corresponding descriptive text (i.e. "connection lost contact" in English).
Which likely means that SQL*Plus is indeed experiencing the ORA-3135 as well, but not finding the ".msg" file is an additional problem on top of that.
In the past, I've used a clue contained within MOS Note #730066.1 (entitled "/Diagnosis of ORA-3135/3136 Timeouts When The Fault Is In The Database/") and queried the V$ACTIVE_SESSION_HISTORY view for the 20 seconds preceding the occurrance of the ORA-3135 error up to 5 seconds afterwards. The exact time of the ORA-3135 error is easy to obtain because a corresponding ORA-3136 error is logged to the database alert.log file. While querying V$ACTIVE_SESSION_HISTORY, I look for wait-events (i.e. column EVENT) in the class of "Concurrency" or "Cluster" or anything other than "ON CPU" or "User I/O". A good starting query might be something like... select event, count(*) cnt
from gv$active_session_history
group by event
having count(*) > 10
order by cnt desc;
This might begin to point you towards the root cause, if it resides in the database. Please feel free to share the results of this query with the list, if possible?
Thanks!
-Tim
On 10/2/2013 3:12 PM, Charlotte Hammond wrote:
>>> To: oracle-l_at_xxxxxxxxxxxxx
>>> Date: Wed, 02 Oct 2013 13:21:18 -0600
>>>
>>>
>>> Charlotte,
>>>
>>> Is this ever happening when connecting using SQL*Plus from the PC in
>>> question? Or is it only from an application? If so, what is the
>>> application using for connectivity? Is it ODBC, JDBC, or a straight-out
>>> Oracle OCI/TNS connection?
>>>
>>> The reason that the ORA-3135 happens is because there are a few
>>> preliminary SQL statements that
>>> is
>>> statements might overrun the configured timeout. Some of the causes of
>>> the ORA-3135 originate within the database, but there is no reason why
>>> it couldn't also be the client-side PC that is not responding
>>> appropriately and promptly. Hence the questions about the nature of the
>>> client program.
>>>
>>> Thanks!
>>>
>>>
>>>
>>> ________________________________
>>> From: Charlotte Hammond <charlottejanehammond_at_xxxxxxxxx>
>>> To: ORACLE-L <oracle-l_at_xxxxxxxxxxxxx>
>>> Sent: Wednesday, October 2, 2013 8:07 PM
>>> Subject: ORA-3135 Fixed by PC Restart
>>>
>>>
>>>
>>> Hi All,
>>>
>>> I've got a problem with a Windows 7 PC client (11.2.0.1) which after a
>>> period of time (days/hours) starts throwing ORA-03135 (after a hang of
>>> about 20 seconds) when I try to connect to a particular database
>>> (11.2.0.3). Restarting the PC always fixes the problem for a while but is
>>> not a convenient workaround.
>>>
>>> It only happens on this one PC and against one target database. (i.e.
>>> other PC's can connect to the problem DB, and also this PC can connect
>>> happily to other databases). I've traced the client and listener but not
>>> seen anything helpful yet- just a 20 second gap in times on the client side
>>> before it fails, and a listener trace that stops dead on the disconnect.
>>> I've tried changing the CONNECT_TIMEOUT to no avail and gone through the
>>> Oracle MOS doc on ORA-03135 troubleshooting.
>>>
>>> Just wondering if this sounded familiar to anyone? Just seems the fact
>>> that rebooting the PC fixes things suggests the problem lies there. Both
>>> PC and Server are very, very lightly loaded. (A bit vague, I know, but just
>>> looking for guidance on where to look: the client or the server? Could it
>>> be a firewall issue? - there is a firewall in the path - but don't know why
>>> it would be intermittently failing to connect and the network guys aren't
>>> very interested so I'd need something more than a vague guess to get
>>> traction from them. It's quite difficult to diagnose/fix when the problem
>>> is not readily reproducible).
>>>
>>> Many thanks,
>>> Charlotte
>>>
>>>
Date: Thu, 3 Oct 2013 09:34:59 -0700 (PDT)
Message-ID: <1380818099.82527.YahooMailNeo_at_web140006.mail.bf1.yahoo.com>
Hi Tim / List,
Thanks for your hints - I've got my SQL*Plus environment sorted now and it's producing the following error when the connection becomes impossible: ORA-28547: connection to server failed, probable Oracle Net admin error. This occurs at the same time as ORA-03135 from SQL*Developer.
Unfortunately we're not licenced to run ASH on this database but I enabled 10046 tracing for the whole database (not a problem as it's lightly loaded) and I don't see any trace files for the failing connections which suggests that it's not even getting as far as the database.
I'm still inclined to think there's a problem on the client side. Today when we started getting the ORA-03135s (and ORA-28547s) we did not restart the PC as we usually do. After about 3 hours the problem went away and we could connect again, so whatever is breaking seems to be transient. During these 3 hours we could continue to connect to the database from other PCs suggesting no issues with the network, listener etc.
Any other thoughts?!
Many thanks,
Charlotte
----- Forwarded Message -----
From: Tim Gorman <tim_at_xxxxxxxxx>
To: oracle-l_at_xxxxxxxxxxxxx
Date: Wed, 02 Oct 2013 15:32:51 -0600
Charlotte,
The ORA-1041 indicates that there is something wrong with your Oracle client-side installation, or there is something wrong with the environment, because that error means that SQL*Plus cannot find the language-specific file to translate the error number (i.e. "-3135") into the corresponding descriptive text (i.e. "connection lost contact" in English).
Which likely means that SQL*Plus is indeed experiencing the ORA-3135 as well, but not finding the ".msg" file is an additional problem on top of that.
In the past, I've used a clue contained within MOS Note #730066.1 (entitled "/Diagnosis of ORA-3135/3136 Timeouts When The Fault Is In The Database/") and queried the V$ACTIVE_SESSION_HISTORY view for the 20 seconds preceding the occurrance of the ORA-3135 error up to 5 seconds afterwards. The exact time of the ORA-3135 error is easy to obtain because a corresponding ORA-3136 error is logged to the database alert.log file. While querying V$ACTIVE_SESSION_HISTORY, I look for wait-events (i.e. column EVENT) in the class of "Concurrency" or "Cluster" or anything other than "ON CPU" or "User I/O". A good starting query might be something like... select event, count(*) cnt
from gv$active_session_history
group by event
having count(*) > 10
order by cnt desc;
This might begin to point you towards the root cause, if it resides in the database. Please feel free to share the results of this query with the list, if possible?
Thanks!
-Tim
On 10/2/2013 3:12 PM, Charlotte Hammond wrote:
> Hi Tim / List, > > I'm getting ORA-03135 from JDBC (SQL*Developer v.3.0). Using SQL*Plus > instead returns (when it fails) ORA-01041: internal error hostdef extension > doesn't exist. The errors occur at the same time on SQL*Developer and > SQL*Plus so these appear to be different manifestations of the same problem. > > I've discounted a load problem on the database as other PC clients are not > having this problem, the server is practically idle, and the session fails to > connect after just 20 seconds, not the CONNECT_TIMEOUT value I specified (of > several minutes). This, along with the fact that a PC restart fixes it, is > why I think the problem is on the client side. But equally the client can > still connect to all other databases fine while having a problem with just > this one. Which leaves me confused...! > > Charlotte > > > ----- Forwarded Message ----- > > From: Tim Gorman <tim_at_xxxxxxxxx>
>>> To: oracle-l_at_xxxxxxxxxxxxx
>>> Date: Wed, 02 Oct 2013 13:21:18 -0600
>>>
>>>
>>> Charlotte,
>>>
>>> Is this ever happening when connecting using SQL*Plus from the PC in
>>> question? Or is it only from an application? If so, what is the
>>> application using for connectivity? Is it ODBC, JDBC, or a straight-out
>>> Oracle OCI/TNS connection?
>>>
>>> The reason that the ORA-3135 happens is because there are a few
>>> preliminary SQL statements that
> execute before the database connection
>>> is
> considered "established", and delays in processing those SQL
>>> statements might overrun the configured timeout. Some of the causes of
>>> the ORA-3135 originate within the database, but there is no reason why
>>> it couldn't also be the client-side PC that is not responding
>>> appropriately and promptly. Hence the questions about the nature of the
>>> client program.
>>>
>>> Thanks!
>>>
>>>
>>>
>>> ________________________________
>>> From: Charlotte Hammond <charlottejanehammond_at_xxxxxxxxx>
>>> To: ORACLE-L <oracle-l_at_xxxxxxxxxxxxx>
>>> Sent: Wednesday, October 2, 2013 8:07 PM
>>> Subject: ORA-3135 Fixed by PC Restart
>>>
>>>
>>>
>>> Hi All,
>>>
>>> I've got a problem with a Windows 7 PC client (11.2.0.1) which after a
>>> period of time (days/hours) starts throwing ORA-03135 (after a hang of
>>> about 20 seconds) when I try to connect to a particular database
>>> (11.2.0.3). Restarting the PC always fixes the problem for a while but is
>>> not a convenient workaround.
>>>
>>> It only happens on this one PC and against one target database. (i.e.
>>> other PC's can connect to the problem DB, and also this PC can connect
>>> happily to other databases). I've traced the client and listener but not
>>> seen anything helpful yet- just a 20 second gap in times on the client side
>>> before it fails, and a listener trace that stops dead on the disconnect.
>>> I've tried changing the CONNECT_TIMEOUT to no avail and gone through the
>>> Oracle MOS doc on ORA-03135 troubleshooting.
>>>
>>> Just wondering if this sounded familiar to anyone? Just seems the fact
>>> that rebooting the PC fixes things suggests the problem lies there. Both
>>> PC and Server are very, very lightly loaded. (A bit vague, I know, but just
>>> looking for guidance on where to look: the client or the server? Could it
>>> be a firewall issue? - there is a firewall in the path - but don't know why
>>> it would be intermittently failing to connect and the network guys aren't
>>> very interested so I'd need something more than a vague guess to get
>>> traction from them. It's quite difficult to diagnose/fix when the problem
>>> is not readily reproducible).
>>>
>>> Many thanks,
>>> Charlotte
>>>
>>>
> -- > http://www.freelists.org/webpage/oracle-l > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 03 2013 - 18:34:59 CEST