ORA-12170 TNS Connect timeout occurred

Question

I was trying to connect to the database here in my laptop using Oracle Toad but I kept on having this error      ORA-12170  TNS Connect timeout occurred   What are the possible reasons why I kept on having this error    I accessed the same database yesterday and was able to accessed it

User · Answer

I was getting the same error while connecting my "hr" user of ORCLPDB which is a pluggable database.

First, get hostname and port number by typing a command lsnrctl status on windows command prompt. In my case, it was 127.0.0.1 with port number as 1521

Second, enter the below command with your hostname and port number:

sqlplus username/password@HostName:Port Number/PluggableDatabaseName.

For example:

sqlplus hr/[email protected]:1521/ORCLPDB.

User · Answer

Beside the oci dll there are a few  jar files  This gave me the idea to install Java  Then everything worked

User · Answer

TROUBLESHOOTING STEPS  Doc ID 730066 1   Connection Timeout errors ORA-3135 and ORA-3136 A connection timeout error can be issued when an attempt to connect to the database does not complete its connection and authentication phases within the time period allowed by the following  SQLNET INBOUND CONNECT TIMEOUT and or INBOUND CONNECT TIMEOUT  server-side parameters   Starting with Oracle 10 2  the default for these parameters is 60 seconds where in previous releases it was 0  meaning no timeout   On a timeout  the client program will receive the ORA-3135  or possibly TNS-3135  error      ORA-3135 connection lost contact   and the database will log the ORA-3136 error in its alert log          Sat May 10 02 21 38 2008       WARNING  inbound connection timed out  ORA-3136         Authentication SQL   When a database session is in the authentication phase  it will issue a sequence of SQL statements  The authentication is not complete until all these are parsed  executed  fetched completely  Some of the SQL statements in this list e g  on 10 2 are   select value  from props  where name    GLOBAL DB NAME   select privilege  level from sysauth  connect by grantee  prior privilege   and privilege  gt 0 start with grantee   1 and privilege  gt 0  select SYS CONTEXT  USERENV    SERVER HOST    SYS CONTEXT  USERENV    DB UNIQUE NAME    SYS CONTEXT  USERENV    INSTANCE NAME    SYS CONTEXT  USERENV    SERVICE NAME     INSTANCE NUMBER  STARTUP TIME  SYS CONTEXT  USERENV    DB DOMAIN    from v instance where INSTANCE NAME SYS CONTEXT  USERENV    INSTANCE NAME    select privilege  from sysauth  where  grantee   1 or grantee  1  and privilege  gt 0  ALTER SESSION SET NLS LANGUAGE   AMERICAN  NLS TERRITORY   AMERICA  NLS CURRENCY      NLS ISO CURRENCY   AMERICA  NLS NUMERIC CHARACTERS       NLS CALENDAR   GREGORIAN  NLS DATE FORMAT   DD-MON-RR  NLS DATE LANGUAGE   AMERICAN  NLS SORT   BINARY  TIME ZONE    02 00  NLS COMP   BINARY  NLS DUAL CURRENCY      NLS TIME FORMAT   HH MI SSXFF AM  NLS TIMESTAMP FORMAT   DD-MON-RR HH MI SSXFF AM  NLS TIME TZ FORMAT   HH MI SSXFF AM TZR  NLS TIMESTAMP TZ FORMAT   DD-MON-RR HH MI SSXFF AM TZR    NOTE  The list of SQL above is not complete and does not represent the ordering of the authentication SQL   Differences may also exist from release to release    Hangs during Authentication   The above SQL statements need to be Parsed  Executed and Fetched as happens for all SQL inside an Oracle Database  It follows that any problem encountered during these phases which appears as a hang or severe slow performance may result in a timeout   Symptoms of such hangs will be seen by the authenticating session as waits for        cursor  pin S wait on X       latch  row cache objects       row cache lock Other types of wait events are possible  this list may not be complete   The issue here is that the authenticating session is blocked waiting to get a shared resource which is held by another session inside the database  That blocker session is itself occupied in a long-running activity  or its own hang  which prevents it from releasing the shared resource needed by the authenticating session in a timely fashion  This results in the timeout being eventually reported to the authenticating session    Troubleshooting of Authentication hangs   In such situations  we need to find out the blocker process holding the shared resource needed by the authenticating session in order to see what is happening to it   Typical diagnostics used in such cases are the following    Three consecutive systemstate dumps at level 266 during the time that one or more authenticating sessions are blocked  It is likely that the blocking session will have caused timeouts to more than one connection attempt  Hence  systemstate dumps can be useful even when the time needed to generate them exceeds the period of a single timeout e g  60 sec             sqlplus -prelim    as sysdba           oradebug setmypid         oradebug unlimit         oradebug dump systemstate 266            wait 90 seconds         oradebug dump systemstate 266            wait 90 seconds         oradebug dump systemstate 266         quit     ASH reports covering e g  10-15 minutes of a time period during which several timeout errors were seen  If possible  Two consecutive queries on V LATCHHOLDER view for the case where the shared resource being waited for is a latch  select   from v latchholder  The systemstate dumps should help in identifying the blocker session   Level 266 will show us in what code it is executing which may help in locating any existing bug as the root cause    Examples of issues which can result in Authentication hangs   Unpublished Bug 6879763 shared pool simulator bug fixed by patch for unpublished Bug 6966286 see Note 563149 1  Unpublished Bug 7039896 workaround parameter   enable shared pool durations false see    Note 7039896 8 Other approaches to avoid the problem   In some cases  it may be possible to avoid problems with Authentication SQL by pinning such statements in the Shared Pool soon after the instance is started and they are freshly loaded  You can use the following artcile to advise on this  Document 726780 1 How to Pin a Cursor in the Shared Pool using DBMS SHARED POOL KEEP  Pinning will prevent them from being flushed out due to inactivity and aging and will therefore prevent them for needing to be reloaded in the future i e  needing to be reparsed and becoming susceptible to Authentication hang issues

User · Answer

open sqlnet ora    NAMES DIRECTORY PATH   TNSNAMES  EZCONNECT  SQLNET INBOUND CONNECT TIMEOUT 360 SQLNET RECV TIMEOUT 10 SQLNET SEND TIMEOUT 10   http   docs oracle com cd B19306 01 network 102 b14213 sqlnet htm

User · Answer

I have tried changing the tnsnames ora file putting the IP of the server instead of localhost or loopback address  it did not work  The firewall was blocking the requests  Please configure your firewall or turn it off not recommended   it would work

User · Answer

Issue because connection establishment or communication with a client failed to complete within the allotted time interval  This may be a result of network or system delays

User · Answer

It is because of conflicting SID  For example  in your Oracle12cBase app product 12 1 0 dbhome 1 NETWORK ADMIN tnsnames ora file  connection description for ORCL is this   ORCL      DESCRIPTION        ADDRESS    PROTOCOL   TCP  HOST   localhost  PORT   1521        CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   orcl              And  you are trying to connect using the connection string using same SID but different IP  username password  like this      sqlplus username password 192 168 130 52 1521 orcl   To resolve this  make changes in the tnsnames ora file   ORCL      DESCRIPTION        ADDRESS    PROTOCOL   TCP  HOST   192 168 130 52  PORT   1521        CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   orcl

User · Answer

Gathering the answers in the comments   The problem is that the Oracle service is running on a IP address  and the host is configured with another IP address   To see the IP address of the Oracle service  issue an lsnrctl status command and check the address reported  in this case is 127 0 0 1  the localhost     DESCRIPTION  ADDRESS  PROTOCOL tcp  HOST 127 0 0 1  PORT 1521      To see the host IP address  issue the ipconfig  under windows  or ifconfig  under linux  command   Howewer  in my installation  the Oracle service does not work if set on localhost address  I must set the real host IP address  for example 192 168 10 X    To avoid this problem in the future  do not use DHCP for assigning an IP address of the host  but use a static one

User · Answer

Check the FIREWALL  to allow the connection at the server from your client  By allowing Domain network or create rule

[oracle] ORA-12170: TNS:Connect timeout occurred

Examples related to oracle

Examples related to toad

Examples related to ora-12170