TNS-12535 / ORA-12535 on Connection to Database

1-

Symptoms

Oracle Net connections to database fail with error code TNS-12535 / ORA-12535: TNS:operation timed out. Oracle Net client trace can show large delay upon resolving TNS listener host

Cause
Operating system error connection timed out. The TNS listener is unable to service the requests quickly enough. The TNS listener has a queue (backlog) of connection requests that it needs to service. If it is unable to service these quick enough, they will start to timeout.

Solution

The size of the TNS listener queue can be influenced by setting parameter QUEUESIZE in the listener.ora file.   To specify a queue size for a listener, enter a value to the QUEUESIZE parameter at the end of any listening address:

LISTENER =
  (ADDRESS =(PROTOCOL = TCP)(HOST=SAMPLE.COM)(PORT = 1521)(QUEUESIZE=99)) 

Default LISTENER.ORA queue sizes

Solaris default QUEUESIZE is 5
Aix default QUEUESIZE is 1024
Linux Default QUEUESIZE is 1024
Windows default QUEUESIZE is 50

Ref: 214122.1

 

2-

Brief notes:

- In essence, the ORA-12535/TNS-12535 is a timing issue between the client and server.
- The TNS-12535 or ORA-12535 error is commonly a timeout error associated with Firewalls or slow Networks.
- It can also be due to an incorrect "timeout" parameter setting for the following files:
listener.ora -->
CONNECT_TIMEOUT_ (8.1.x and lower only)
or
INBOUND_CONNECT_TIMEOUT_ (9.2 and above)
sqlnet.ora -->
SQLNET.INBOUND_CONNECT_TIMEOUT (9.2 and up). 

Ref: 125351.1

3-  TNSPING command fails with the TNS-12535 error:

Cause 

Personal Firewall enabled on Client system.

Solution

To implement the solution, please execute the following steps:

Add the Oracle Client side applications to the Windows Firewall Exception List.

Click Start, Control Panel, and double click 'Windows Firewall'  Next, select the 'Exceptions Tab'
Use the 'Add Program...' dialog to add the programs to the Exception List.
If unable to navigate to the 'Windows Firewall' configurations window, you may be able to manually access the Window Firewall in Windows XP SP2 window by:
Clicking Start, click Run, type Firewall.cpl, and then click OK  Then from there you can edit the programs from the Exceptions Tab.

Ref: 341216.1


4-  Connect as SYSDBA Fails with TNS-12535 ,Can not start database

cause: Log files were being written from the ORASRV_BEQ_.COM file and the 32k version limit had been reached

fix:
Delete the log files.