ORA-12170: TNS:Connect timeout occurred aka TNS-12170

If you are wondering why are you getting this error *and* all the following prerequisite conditions apply to you:

1. you are trying to connect to a remote database through the internet;
2. tnsping net_service_name is working telling you that the remote listener is up and running;
3. the remote database is running on a Windows platform;
4. server parameter USE_SHARED_SOCKET is not set or is set to FALSE;
5. your client is attempting to connect in dedicated server mode;
6. you get ORA-12170:TNS:Connect timeout occurred (or TNS-12170 on versions earlier than 10g), when you try to open a connection from SQL*Plus or from another client program;

then you might try setting USE_SHARED_SOCKET=TRUE and see if it works.
USE_SHARED_SOCKET can be either an environment variable or a windows registry value.
I preferred the former option and i defined it as a system variable, in My computer's properties/advanced tab/environmental variables.

If you look up ORA-12170 in the Oracle error message list for Oracle 10G, then you are confronted with an explanation where either network delays or a denial-of-service attacks are the most probable causes.
Well, in my humble opinion, is also very likely that you are trying to access a database behind a firewall and the only open port is 1521 (or a non-standard port if that's the case).

Opening port 1521 is not enough because the listener is only accepting initial requests through that port, but once the dedicated server process is started, the connection between client and server is moved to a different port, typically a randomly chosen port, if i am not wrong.

In view of this fact, you can't just open up all the ports of the firewall and here's where this USE_SHARED_SOCKET=TRUE comes in handy, because it forces the connection to occur on the same initial port.

See Appendix C of the Database Platform Guide for further information.


Hope it helps.



Updated April 12.
If the above scenario doesn't fit well to your case, you may want to read the official documentation, starting from this section of the Net Services Admininistrator's Guide (10G).

Updated October 12.
I am getting ORA-12170 also when i try to access Oracle XE running on my Windows laptop when i forget to start the OracleTNSListener service that i modified from "Automatic" to "Manual". After starting OracleTNSListener, you may get ORA-12514 until the Oracle database server registers with the listener (within 1-2 minutes typically).

---

ORA-12170: TNS:Connect timeout occurred

is the english message corresponding to the following translated versions:

ORA-12170: TNS: si è verificato il timeout della connessione
ORA-12170: TNS:Se ha producido un timeout de conexión
ORA-12170: TNS:S'ha superat el temps d'espera de la connexió
ORA-12170: TNS : délai de connexion dépassé
ORA-12170: TNS: Zeitüberschreitung bei Verbindung
ORA-12170: TNS:Προέκυψε τέλος χρόνου σύνδεσης
ORA-12170: TNS:Forbindelses-timeout opstod
ORA-12170: TNS: Tidsgränsen överskreds vid anslutning
ORA-12170: TNS:Det oppstod et tidsavbrudd for tilkoblingen
ORA-12170: TNS: Yhteyden aikakatkaisu
ORA-12170: TNS:Csatlakozási időtúllépés történt.
ORA-12170: TNS:A survenit o eroare la expirarea timpului alocat pentru conectare
ORA-12170: TNS: time-out van verbinding.
ORA-12170: TNS:Ocorreu timeout de conexão
ORA-12170: TNS:Ocorrência de tempo de espera esgotado da ligação
ORA-12170: TNS:Истекло время ожидания соединения
ORA-12170: TNS:Vyskytlo se odpojení z důvodu vypršení časového limitu
ORA-12170: TNS: Došlo k uplynutiu časového limitu pripojenia
ORA-12170: TNS:przekroczenie limitu czasu połączenia
ORA-12170: TNS: Bağlantı zaman aşımı oluştu