Error ORA-12170: TNS:Connect timeout occurred

Error ORA-12170: TNS:Connect timeout occurred

December 12, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Error ORA-12170: TNS:Connect timeout occurred can have many different reasons as one of them could be that Oracle database connection is defined incorrectly in file tnsnames.ora or the Oracle connection port is closed by firewall. We will go through in this page how to find out the reason of this error.

You got the ORA-12170: TNS:Connect timeout occurred error using your development tool and the best practice would be to use in this case Oracle SQLPLUS tool to avoid your tool conflict problems and possible configuration errors. Try to connect to your Oracle database via the command prompt as the following example shows:

$ sqlplus <username>@<database>

This Oracle example is using database name MYDEV1 and username is scott. Running the following command will open your default Oracle sqlplus connection. If you do have installed more then one Oracle database in the same server use the working database path in front of the sqlplus command.

$ sqlplus scott@MYDEV1

When the following error appears instead of the “SQL>” prompt then the error is not only cause by your SQL Development tool otherwise seeing your the SQL prompt check your TNS files and default ORACLE_HOME path declared in your SQL Development tool settings.

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

When you end up not seeing the SQL prompt then the next step would be to use Oracle TNSPING and check if the Oracle listener can reach to the database. You can execute Oracle tnsping as following:

tnsping <database>

$ tnsping MYDEV1

If you did get the same result as in this tutorial guide then your tnsping didn’t reach to the Oracle database either. The following step would be to check the host itself. Otherwise when the Oracle tnsping did pass successfully then check again your connection details and try to use the IP-numbers instead of host names.

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

The tnsping failed and we need to get to know if the host name is correct and for that we are going to use the ping command. This example is using ip number 1.0.0.1 . Also you can use the host name but the IP number would confirm better that the host is available in network.

ping <hostname>
$ ping 1.0.0.1

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

When the ping returns 0% loss then the host is available otherwise check your computer connectivity or if the host is not shut down.

This example continues with the “0% loss” condition. Now we do know the connection problem does not appear because the host is unreachable. The ping does see the host and to next we should start to check the computer ports. The ports can be disabled by some firewall program or by a network router that has default settings on.

To check ports you can either use the telnet command or use some telnet tool. We are using the Putty tool and on the configuration screen you will need to set your host name or IP address and to the port location your database connection port. You can find it in the tnsnames.ora file or the Oracle tnsping shows it too. And on the Putty Configuration screen press the “Open” button.

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

You can use your telnet command as well the command looks following:

telnet <hostname> <port>

The command with the same parameters above looks as

$ telnet 1.0.0.1 1521

The telnet connection may take some time depending on the network speed and the server configuration, just keep waiting and don’t cancel the connection. The connection should end finally one of the following messages:

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

The Network error: Connection timed out error means that network port in this case 1521 is disabled and your computer cannot connect it. You need to go through the all previous steps or this error can be caused with some configuration problem above.

When your telnet connection will end with message: Server unexpectedly closed network connection then the port is open and your computer gets connected via the port.

dba oracle dba database management system oracle performance tuning oracle database oracle database stock market basics linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

If you pass this steps with the Server unexpectedly closed network connection message and you still get the ORA-12170: TNS:Connect timeout occurred error then there is something wrong with your database and your connection is fine. To check you database error you need to log on to the same host were your database is sitting and log on as admin. This example wouldn’t go through the database problem but you did define the location where the problem lays.

Otherwise when you did manage to get the connection it is always good to confirm the Oracle database which one you just connected to make sure it is the correct one. The following Oracle command will return the connected database name.

select SYS_CONTEXT('USERENV','DB_NAME') from dual;

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*