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.

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.

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)
    )
  )