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

Scott wrote:
> Hi Cognos and Oracle gurus,

<snip>

> 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

<snip>

> Can anyone out there help us figure this one out??
>
> Thank you,
>
> Scott

Scott,

Have you resolved this issue? My experience is not with the Cognos product. When do these TNS errors occur:
* Immediately when the application is started/user logs into the
program
* When displaying reports

• While using the program - enter information into a field, press tab, and instead of information being retrieved, this error message is returned.
• At a middle tier - a server sitting between the client and the Oracle database.

Other questions:
* Has this error message only appeared on only a couple computers, and
never on others? Or, are all computers affected at the same time?
* What hardware and software platform is in use for the Oracle database
and the clients?
* Are the clients connecting through a VPN, or are multiple subnets
involved (you mentioned a router).
* Are there any firewalls between the client and server, or on the
client or server?
* What release of Oracle is in use? What release of the Oracle client
software is installed?

It is hard to say what you need to check based on the limited amount of information provided. Possible scenarios:
* Windows clients running NT 4, 95, 98, ME can connect to the database
without problem, but the Windows 2000, XP, and 2003 server clients cannot. This could be an indication that the wrong IP address is specified in the DNS server for the database. If the NT 4, 95, 98, ME clients cannot connect, but the Windows 2000 and above computers can connect, and you have no internal DNS server, this may be a sign that your Internet connection dropped temporarily and the ISP's DNS server could not be contacted to report that it does not know of your server's IP address. In this case, you could try hard coding the IP address for the server in the TNSNAMES.ORA file on each of the client computers.

• There is a VPN connection or some type of WAN link between the server and the client. The VPN or WAN link connection go down temporarily, and the client tries to connect to the database (or display a report). After a 40 second delay, or whatever the timeout is, the client reports ORA-12535: TNS:operation timed out.
• Something on the server is preventing the listener from responding. It could be another database on the server is having a bad day - listener tries to hand off a connection to the database that is having a bad day, and never returns from the handoff. If this happens, you will likely see many entries in the alert logs for the databases, and possibly several trace files.

Suggestions:
* Get specifics of when the errors occur.

• Locate the listener.log file on the server. It should be in the Oracle installation directory, buried in directories network\log. Examine the tail end of the log file for errors.
• Search inside the Oracle installation directory for files ending with .log or .trc. Examine these files to see if any problems are reported.
• Search the client computers for sqlnet.ora. Examine the files for clues.
• When this problem happens, run a packet capture utility (Ethereal/WireShark) on the client and server, and then try to reproduce the problem. This should tell you if the client and the server are seeing each other, and are able to exchange multiple packets.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 08 2006 - 19:04:49 CDT