Home » Infrastructure » Windows » ODBC-error 'ORA-12535: TNS:operation timed out' when trying to connect to Oracle 9.2.0
ODBC-error 'ORA-12535: TNS:operation timed out' when trying to connect to Oracle 9.2.0 [message #188415] Fri, 18 August 2006 08:12 Go to next message
ceschenbach
Messages: 2
Registered: August 2006
Junior Member
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 the problem.
Can anybody help me ?!
Any suggestions or tips?
Re: ODBC-error 'ORA-12535: TNS:operation timed out' when trying to connect to Oracle 9.2.0 [message #189872 is a reply to message #188415] Mon, 28 August 2006 04:24 Go to previous messageGo to next message
ceschenbach
Messages: 2
Registered: August 2006
Junior Member
In the meantime, I could solve the problem, so i post the solution here for all others...

The problem wasn't connected with odbc as first supposed.
The problem was that a firewall on the server blocked all ports except port 1521.
But communication with the listener will only be initiated through port 1521. Afterwards communication is switched to other not-predictable ports, so all answer-attempts were blocked by the firewall and therefore there was never ever any reaction from the oracle server till the timeout occured.

This can be solved by adding a registry entry on the server:
Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, an entry USE_SHARED_SOCKET (Type Reg_SZ) has to be added and set to TRUE.
Then all inside/outside connections will be made only via the predefined listener port (1521, if not changed otherwise).

Afterwards communication operated flawlessly.
Re: ODBC-error 'ORA-12535: TNS:operation timed out' when trying to connect to Oracle 9.2.0 [message #189881 is a reply to message #189872] Mon, 28 August 2006 05:36 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks for taking the time to respond with your solution. Often, a problem is solved without having any insight into what the problem/solution was. This may very well help others.
Previous Topic: 9.2.0.8
Next Topic: Using KEEP LAST in ASP
Goto Forum:
  


Current Time: Sat Dec 03 20:09:13 CST 2016

Total time taken to generate the page: 0.14872 seconds