ORA-12170: TNS:Connect timeout occurred
  Home
 

Error ORA-12170: TNS:Connect timeout occurred can have many different reasons as Oracle database connection is defined incorrectly in file tnsnames.ora or the Oracle connection port is closed by firewall. The online tech support will go through in this page how to find out the problem reason.

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 errors. Try to connect to Oracle database via the command prompt as the following example:

sqlplus <username>@<database>

In this online tech support example we are using database name MYDEV1 and username is scott. Running the following command will open Oracle sqlplus connection.

sqlplus scott@MYDEV1

When the following error appears insted of “SQL>” prompt then the error is not only cause by your SQL Development tool.

online tech support computer help computer technician computer problems computer oracle database oracle linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

The second step would be to use Oracle TNSPING and check if the Oracle listener can reach to the database. You can execute Oracle tnsping in the following way:

tnsping <database>

tnsping MYDEV1

When you get the same result as the online tech support guide then the following is to check the host itself. Otherwise when Oracle tnsping did pass successfully then check again your connection details.

online tech support computer help computer technician computer problems computer oracle database oracle linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

The following would be to get to know if the host is correct and for that we are using ping command. The online tech support example is using ip-address 1.0.0.1 . You can use host name as well, but IP numbers would give us more secure that the host is available in network.

ping <hostname>
$ ping 1.0.0.1

online tech support computer help computer technician computer problems computer oracle database oracle 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 computer is available otherwise check your computer connectivity.

Online tech support continues with the “0% loss” condition. Now we know the computer problems are not based on computer and the next thing is to check ports. The ports can be disabled by some firewall program or by network router default settings.

To check the port you can either use telnet command or use some telnet tool. The online tech support is using Putty tool and in the configuration window you need to set your host name or IP and to the port location your database connection port. You can find it in the tnsnames.ora file or Oracle tnsping shows it too. And in the Putty Configuration window press the “Open” button.

online tech support computer help computer technician computer problems computer oracle database oracle 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 then the command looks follow:

telnet <hostname> <port>

It looks for the online tech support example 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:

online tech support computer help computer technician computer problems computer oracle database oracle linux server linux ORA-12170: TNS:Connect timeout occurred oracle sqlplus oracle tnsping oracle putty TNS-12535: TNS:operation timed out

The Connection timed out error means after going through all previous steps that network port in this case 1521 is disabled and your computer cannot connect it.

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

online tech support computer help computer technician computer problems computer oracle database oracle 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 then there is something wrong with the database and your connection is fine. You would need to work out the problem on your database side.

Otherwise once you managed to get the connection it is always good to confirm the Oracle database which one you just connected to make sure this is the correct one. The following online tech support help command will return the connected database name.

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



See Also:
Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*