Re: ODBC-error 'ORA-12535: TNS:operation timed out' when trying to connect to Oracle 9.2.0

Christoph Ebner von Eschenbach wrote:
> Hello,
>
> after 2 days of fruitless searching and trying, I'm nearly desperate now,
> so I'm posting my problem here with hope that someone can help...
>
> I'm trying to connect to an Oracle 9.2 database via TCP from Microsoft
> Access 2000 SP3.
> I'm using the ODBC-Drivers from Microsoft.
>
> I keep getting the following error:
> "[Microsoft][ODBC driver for Oracle]ORA-12535: TNS:operation timed out
> (#12535) [Microsoft][ODBC Driver Manager] Fehler bei SQLSetConnectAttr für
> Treiber (#0)"
>
>
> Here are the details:
>
> CLIENT-machine:
> Operating system: Microsoft Windows 2000 SP4
> ODBC-Driver: Microsoft ODBC for Oracle V2.575.1062.00
> Client-Database: Microsoft Access 2000 SP3
> Oracle: Oracle 9.2.0.1.0
>
> SERVER-machine:
> Operating system: Microsoft Windows Server 2003 SP1
> Oracle: Oracle 9.2.0.1.0
>
> --------------------------------------------------------------
> tnsnames.ora (CLIENT)
> ---------------------
> ORACLE.EMBITBIZ =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 217.69.83.130)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = MBDaten)
> )
> )
> --------------------------------------------------------------
> listener.ora (SERVER)
> ---------------------
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = embit)(PORT = 1521))
> )
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = c:\oracle\ora92)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = mbdaten)
> (ORACLE_HOME = c:\oracle\ora92)
> (SID_NAME = mbdaten)
> )
> )
>
> --------------------------------------------------------------
> sqlnet.ora (SERVER)
> ---------------------
>
> NAMES.DEFAULT_DOMAIN = biz
>
> SQLNET.AUTHENTICATION_SERVICES= (NTS)
>
> #NAMES.DIRECTORY_PATH= (TNSNAMES)
> NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
> --------------------------------------------------------------
>
>
> - I have installed Microsoft MDAC 2.8 SP1 on the client-PC
>
> - I have changed registry settings, as suggested by many websites to the
> following values:
>
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
> "OracleXaLib"="oraclient9.dll"
> "OracleSqlLib"="ORASQL9.DLL"
> "OracleOciLib"="oci.dll"
>
> - "tnsping ORACLE.EMBITBIZ" on the client-machine reports success:
>
> Adapter TNSNAMES zur Auflösung des Alias benutzt
> Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL
> = TCP)
> (HOST = 217.69.83.130)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED)
> (SERV
> ICE_NAME = MBDaten)))
> OK (40 ms)
>
> - The listener.log on the SERVER shows that the listener configuration
> should be OK, as the connection seems to be established correctly:
> 18-AUG-2006 13:04:18 *
> (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MBDaten)(CID=(PROGRAM=C:\WINNT\system32\odbcad32.exe)(HOST=MBINTERN)(USER=Administrator
> ))) * (ADDRESS=(PROTOCOL=tcp)(HOST=212.202.134.67)(PORT=58954)) *
> establish * MBDaten * 0
>
> - The user I'm using for Login is able to connect to the database (as
> tested with SQL+ Worksheet on the SERVER-machine)
>
>
> I suppose the problem to be connected with the ODBC-driver on the client
> machine.
>
> I don't know anything else to do or try to solve this problem.
> Can anybody help me ?! Any suggestions or tips?

Search Google (which you obviously didn't do). ora-12535 is usually caused by firewalls.
You can identify this by tracing the client connection and see the redirect to another tcp/ip port fail.
You can see the port is actually redirected in the listener.log.

How to enable tracing is in the documentation and I've also posted it here many, many times.

If it is firewall, I've posted the methods to address it posted here many, many times.

-- 
Sybrand Bakker
Senior Oracle DBA