TEC 5323 Advanced Database Technology

Trouble Shooting Cases

Case I: Error: ORA-12535 / TNS-12535



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.

Quick Checks:


Ensure that your Client and Server platforms and versions are supported and within Oracle's recommended guidelines. Make sure that any non-supported issues, such as Soft links, shared config files, etc., are removed before looking into this document.

If you are running Oracle Names Server, then setup a temporary tnsnames.ora file and edit the sqlnet.ora file to reflect (TNSNAMES) for the names.directory_path.

If you are running MTS (Multi Threaded Server), then try the same connection with (SERVER=DEDICATED) in the tnsnames.ora alias.

Diagnosing Common Errors:


Error: ORA-12535 / TNS-12535

Text: 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 furthe information.

1. Find out if you are going through any Firewalls or Routers. If you are, then see Note A below.

2. Try to "ping" the ip address or hostname from the client. Check the timing response and see if it is acceptable. If it is not, then you may need to increase the CONNECT_TIMEOUT_<listener_name> value in the listener.ora file on the Server. Doing this will correct the connectivity problem, but may be masking the real problem of the physical network.

(See the Sqlnet / Net8 Administrators guide for details on how to make these changes)

Note A

1a. If you are going through a Firewall, and your server platforms are UNIX, then you must ensure that the port being listened on is open, unless you are running the Firewall vendor's Proxy Server, in which case this will allow connectivity.

1b. If you are not running the Firewall vendor's Proxy, and the port is not open, then you will have a refusal from the outside into the Firewall zone.

2. If you are going through a Firewall, and your server platforms are NT, then you must ensure that the port being listened on is open as well as setting up a SQL*Net/Net8 Proxy, such as Net8 Connection Manager, if none is provided by the Firewall vendor.

The reason is that, even though the port is open, the TCP layer will REDIRECT the port to another port. This is due to Winsock2 workings under Microsoft.


Alternative Client Setup

If you cannot connect to oracle1 database server (Windows 2000 server with Oracle9i), you may try the following setting to connect to another database server (Unix Solaris with Oracle9i). The only difference is that you will need to use the following new parameters.

Net Service Name: unixorcl
Host Name: techoracle.eiu.edu
Service Name: unixorcl.techoracle.eiu.edu
Protocol: TCP/IP
Port Number: 1521

Once connected, you can also use scott/tiger to login.

Good luck.


Off Subject

There was this patient that went to a retail pharmacy and asked for a medication. The following conversations happened between him and the pharmacist:

Customer: Can I have that medication? (And he pointed at it over the counter) Pharmacist: Let me see if we still have some left (and the pharmacist went keyboarding away at a terminal), came back to the customer and said, “Sorry we are out of stock” Customer: But it is right thereee, the customer said and again pointed at the medication over the counter. Pharmacist: Point all you can! the system said it is out of stock!

--Contributed by Emmanuel Amadi (Spring 2003, who works at Carle Pharmacy)

Class Index

School of Technology
College of Business & Applied Sciences
Eastern Illinois University