Tuesday, April 03, 2007

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

3 comments:

Anonymous said...

Hi there,
I absolutely love your website. You have a way of de-mystifying things. Your explanations are clear and concise and get right to the point - since most of us are so short on time anyway. Thanks for the wonderful info that you share with your readers.
Regards
Sundan

Byte64 said...

Thanks for the kind words Sundan, bear in mind that one of the objectives of this blog is in the first place to understand the things myself and it's not rare that i get back to it months later when i forget how i did a certain thing, that is why i like to get to the point in a short time too!

Cheers,
Flavio

Unknown said...

This page saved my butt. We needed to SSH tunnel to oracle 9i and above on i386/windows 2000 and it would not work. After debugging for 2-3 hours I discovered there was a disconnect between ssh and TNS. Hence setting this ENV var and rebooting my server did the trick!
What we don't know is what will happen when we put this into the production env with 500 regular TNS users (who do not need to tunnel). I've been doing some reading and I think there's a performance hit when you use. So we're exploring options and I have a feeling we will be sending a tar over to Oracle to verify the finer usage of this particular parameter.
However, you've made my life much easier as things are working well in our lab env.
Cheers,
-S

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles