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

From: sybrandb <sybrandb_at_yahoo.com>
Date: 22 Jun 2006 02:00:20 -0700
Message-ID: <1150966819.973137.292610@c74g2000cwc.googlegroups.com>

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

ORA-12535 is a network or firewall issue. PERIOD. There is nothing to investigate at the application side, there is everything to investigate at the network side like

- is there any firewall in play
- is there any Network Address Translation into play
- have the correct ports been opened in the firewall
- is Oracle using random return ports (by default Oracle always uses 2

tcp/ip ports, the first one fixed, and configured in tnsnames.ora, the actual communication takes place on a radom port. If that is a problem at your site you need to fix that)
- You need to run netstat -r to identify which ports are actually used.

Maybe you should consider using a sniffer to identify further problems.

My experience is, that apart from stubborn network and/or firewall administrators involved, the issue can always be resolved. I have posted the usual methods to resolve this many times, and I am aware of the fact most current posters here do not make any attempt to research the archives at Google. Yet I am not going to post them again. They are easy to find, and they are also documented in the Net administrators manual. The small number of people responding to queries should stop rewarding laziness. If this will make this forum go extinct, so be it.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Thu Jun 22 2006 - 04:00:20 CDT