Re: ORA-12535: TNS:operation timed out errors for over a year now...

Scott wrote:
> Hi Cognos and Oracle gurus,
>
> First off, I'm a network guy. I don't speak cognos or oracle. Speak
> servers of any flavour on any switch and I'll understand all but sql
> stuff boggles my small brain.
>
> Problem:
>
> For over a year our business analysts have been complaining of returned
> TNS errors. They are convinced it is a network issue. I have done
> everything from rebuilding the servers to tweaking the routers and
> switches. I am convinced that it is an application issue.
>
> This is a typical error we receive on a daily basis.
>
> DS-DBMS-E306: UDA driver error connecting to 'CMIS'.
> [PROGRESS - 06:25:26] pid 2164 DimBuild Node 25
> 'D_INVENTORY_LOCATION'; reported the following:
> DS-DBMS-E402: UDA driver reported the following:
> DMS-E-GENERAL, A general exception has occurred during operation
> 'attach database'.
> ORA-12535: TNS:operation timed out
>
> here's another:
>
> PowerPlay Transformer(7.1.513.0 ) Wed Jun 07 06:33:53 2006
> LogFileDirectory=\\servername\corprpt\Projects\Logs\
> ModelSaveDirectory=\\servername\corprpt\Projects\Source\Model\
> DataSourceDirectory=C:\Program Files\Cognos\cer3\bin\
> CubeSaveDirectory=\\servername\corprpt\Projects\Source\Cube\Stage\
> DataWorkDirectory=d:\temp\;d:\temp1\
> ModelWorkDirectory=d:\temp\
> MaxTransactionNum=500000
> ReadCacheSize=32768
> WriteCacheSize=65536
> Temporary UDA directory=C:\WINNT\Temp
> 06-06-07 06:34:17 AM 2 00000000 DMS-E-GENERAL, A
> general exception has occurred during operation 'attach database'.
> 06-06-07 06:34:17 AM 2 00000000 ORA-12535:
> TNS:operation timed out
> 06-06-07 06:34:17 AM 2 00000000
> PowerPlay Transformer Wed Jun 07 06:34:19 2006
>
> I have edited the actual name of our servers with "servername".
>
> Can anyone out there help us figure this one out??
>
> Thank you,
>
> Scott

In addition to the other excellent advice you've received, it could be something like the processes are not disconnecting cleanly, hogging ports, so at random times sqlnet can't get a port and times out. I've seen certain specific versions of certain unix servers fix this problem simply by reducing tcp timeouts - the root cause in one case was poor application design that doesn't properly handle people closing the app on their pc uncleanly, but try to get the vendor to fix it...

We might be able to help more if you give the platforms and versions of all tiers (OS, Oracle, etc). Those backslashes look windowish, the pid looks unixish... no wait I see \\servername... maybe you need to make all those local...

Also, ask the DBA if he is using MTS. There might be a file called tnsnames.ora on the clients that can give a clue as to which server is being used. It is also possible that certain options in the clients sqlnet.ora affect how the attach is being made, so let us know what is in that.

This may be version dependent:

$ oerr ora 12535
12535, 00000, "TNS:operation timed out"
// *Cause: The requested connection could not be completed within the timeout
// period specified by the CONNECT_TIMEOUT parameter in listener.ora. This

// error arises from the tnslsnr.
// *Action: Either reconfigure CONNECT_TIMEOUT to be 0, which means
// wait indefinitely, or reconfigure CONNECT_TIMEOUT to be some higher
// value. Or, if the timeout is unacceptably long, turn on tracing
// for further information.

You should also tell your dba to give you access to metalink.oracle.com, and see Note: 119706.1among others:

"+ The TNS-12535 or ORA-12535 error is normally a timeout error associated
  with Firewalls or slow Networks.
+ It can also be an incorrect listener.ora parameter setting for the   CONNECT_TIMEOUT_<listener_name> value specified. + In essence, the ORA-12535/TNS-12535 is a timing issue between the client and
  server."

See Note:68652.1 if you have NT servers.

jg

-- 
@home.com is bogus.
http://www.banthisfilth.com/