Re: ORA-03135
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Sun, 19 Sep 2010 19:02:15 -0700 (PDT)
Tim
This is correct that Sql net has to be TNS and I have seen that two types of
error like TNS packet failure and then Lost connection. SOmetime it was only
lost connection. One thing is sure that after changing the expire_time=1 has
resolved the issue and even the SQL idle for more than is not losing
connection.
Again the value is changed to 10 min and back on the same error with 3135 or
TNS
Packet failure.
Sanjay
________________________________
From: Tim Gorman <tim@xxxxxxxxx>
To: smishra_97@xxxxxxxxx
Cc: Ethan Post <post.ethan@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Sent: Sat, September 18, 2010 10:56:31 AM
Subject: Re: ORA-03135
All,
Please stop guessing.
Think about it -- ORA-03135 and ORA-03136 are errors returned from the RDBMS
engine, not the SQL*Net listener component. If they were returned by the
listener, they would be "TNS-xxxxx" messages, not "ORA-nnnnn" messages. The
root-cause is in the RDBMS, so look for root cause within that layer.
What I've found really useful is to query the active session history (i.e.
either DBA_HIST_ACTIVE_SESS_HISTORY in the AWR if the errors occurred some time
ago or V$ACTIVE_SESSION_HISTORY if they occurred recently) and look at what the
database sessions are waiting upon.
Take the timestamps from the error messages, then just browse the appropriate
ASH view for that time period, including about 10-30 seconds prior to the error
messages, paying particular attention to columns like SAMPLE_TIME, EVENT, P1,
P2, P3, PROGRAM, MACHINE, etc. Usually I find a bewildering variety of wait
events occurring, indicating a wide variety of causes, anywhere from "enq: SQ -
contention" (i.e. contention on AUDSES$ sequence) to "library cache lock" (i.e.
shared pool contention due to under-allocated shared pool), to "cursor: pin S
wait on X" (i.e. weird application programming logic at connect-time), to "SGA:
allocation forcing component growth" (i.e. resizing SGA memory components).
The sources of these errors are so many and varied that you really need to
understand the root cause. It could be a tweak of Oracle initialization
parameters or it could be correction of toxic behavior by the application. It
is better to know first before expending time, effort, and credibility doing
anything... and that's where the active session history comes in...
Hope this helps...
Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal =>
P.O. Box 630791, Highlands Ranch CO 80163-0791 website =>
http://www.EvDBT.com/ email => Tim@xxxxxxxxx mobile => +1-303-885-4526
fax => +1-303-484-3608 Lost Data? => http://www.ora600.be/ for info
about
DUDE...
On 9/17/2010 6:18 PM, Sanjay Mishra wrote:
Ethan
>
>Did another test where I changed one RAC node expire_time was changed to 10,
>second to 5 and third to 1. Opened the Dedicated SQL plus connection with
>Instance_name to each of them and got this result
> 1. Setting of 10 failed after 1hr with same Connection Lost error
> 2. Setting of 5 also failed after 1 hr with same error
> 3. Setting of 1 still working
>
>Now point is that it might be somewhere some setting of Firewall or timeout or
>so which might be fitting in my env. BUT THE MAIN POINT now is CONS of using
>this SETTING of 1 minute. IS this be an issue to the environment where DB is
>having around 300-400 connections. Can a setting of 1 minute is going to be
>big
>issue in terms of networing or Resources. Any comments or suggestions
>
>Sanjay
>
>
>
________________________________
From: Ethan Post <post.ethan@xxxxxxxxx>
>To: smishra_97@xxxxxxxxx
>Cc: oracle-l@xxxxxxxxxxxxx
>Sent: Fri, September 17, 2010 6:42:43 PM
>Subject: Re: ORA-03135
>
>Let me know what you find, I am having the exact same issue. I don't think we
>have any special SQL*Net parms set. This only happens on our rac config.
>
>
>On Fri, Sep 17, 2010 at 5:32 PM, Sanjay Mishra <smishra_97@xxxxxxxxx> wrote:
>
>Hi
>>
>>I had a 11g Database Server which is AIX and is 3-node cluster. I am trying
>>to
>>make sqlplus connection from my PC and opened two windows like one to RAC
>>Service and another to Dedicated Connection to only one Node. I had idle_time
>>to
>>unlimited to the user used for connection to te database
>>
>>My both Sqlplus session was good for 30 minutes but after around 1hr +, I
>>tried
>>to check the sql, I got ORA-03135 on both. Can anyone point as what can be
>>the
>>issue.
>>
>>
>>Also sqlnet.expire_time is set to 10 on the Server. This is affecting the
>>application user also who are loading Data warehouse with batches and long
>>one
>>are failing. Any help is greatly appreciated. I provided some work done so
>>far.
>>Check there is no firewall between PC and Db server
>>
>>TIA
>>Sanjay
>>
>
>-- //www.freelists.org/webpage/oracle-l
Other related posts:
