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+ Work*** 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!

.