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

Christoph Ebner von Eschenbach schreef:

> 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)
> )
> )

Try SID=mbdaten, instead of service_name. Service_name requires the complete
service name, as used while installing the database. It is usually SID.domain.company
of some sort.

> --------------------------------------------------------------
> 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)
> )
> )
>

Is host mbit the same as 217.69.83.130?
>
> - "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)
>
>

Try with SQL*Plus on your client!

And time outs are usually connected with firewalls - any firewalls active between
your client and the server? Think "personal firewalls" on the client, too! Received on Mon Aug 21 2006 - 04:30:47 CDT