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.

Alric

Has your laptop been given a new IP address by DHCP, perhaps? What IP is the listener/connection using at the moment? – Overton

@AlexPoole hi, is it because yesterday i was connecting to a WIFI connection different from the WIFI connection i'm using now? – Alric

Probably, they usually use DHCP; is your DB connection going to localhost (127.0.0.1), or a real IP? If it's real you'll need to change it your current IP, not the one you had yesterday. You need to check what address your listener is using too though. It might have the same real (old) IP, or could be using you PC name, which might be working. Run lsnrctl status to see what it is doing now. – Overton

@AlexPoole Im connecting to a real IP(192.168.1.6) and in the lsnrctl status command i had these: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) does it mean i need to change the listener in listener.ora? – Alric

The address in the connection needs to match the listener address; so yes, change the connection to 127.0.0.1. – Overton

Answer 1

[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.

Sanborn

" lsnrctl status " did great for me. I found some other host. then, routed that host to 127.0.0.1, Issue solved. Thanks ! – Caplin

Answer 2

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/[email protected]: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)
    )
  )

Dratted

Answer 3

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#>0 start with grantee#=:1 and privilege#>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#>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:

1. 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.

Badger

Answer 4

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

TRY : 1- systemctl disable firewalld 2- systemctl stop firewalld

Eckardt

The oracle installer doesn't seem to add the firewall rule (or warn you that you need to do it manually), sick of oracle its just one error after another... – Bekelja

Answer 5

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.

Seger

Answer 6

If you have just installed 21c on Oracle Linux 8 within a virtual machine.(Not for production)

Follow the steps given below:

1. Stop the listener first

    ./lsnrctl stop
   

2. Open listener.ora file and replace the HOST value with the IP Address #Location --> /opt/oracle/homes/OraDBHome21cEE/network/admin/listener.ora

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.130)(PORT =1521))
   

3. Restart the listener

    ./lsnrctl start
   

4. Now switch to root user and run the following commands one by one to disable the firewall

   sudo systemctl stop firewalld
   
   sudo systemctl disable firewalld
   
   sudo systemctl status firewalld
   

5. Minimize the virtual machine and connect from your host machine (or any other machine) using Toad or any other database browser tool.

If still not working then you can stop the database and restart

./sqlplus / as sysdba

shutdown immediate;

startup;

Queston

Answer 7

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

Coleville

Please improve your answer by adding some context and explanation. Don't just paste some code. – Coarctate

Altought this is really useful! This extends the timeout in the client. – Deborahdeborath

Answer 8

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.

Fidelity

Answer 9

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.

Casaubon