tnsconnect shows operation timeout

Question

Here is my use case:

1. We are using virtual machine. Couple of days ago I have created new Oracle database. I cloned this machine and it got different IP address.
2. Today I wanted to connect to the database using SQLPlus but I go connection timeout. I have tried tnsping command and here is the output of that command:

C:\Users\Administrator>tnsping clm

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-JAN-2 014 08:36:31

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO COL=TCP)(HOST=9.142.60.187)(PORT=1521))) TNS-12535: TNS:operation timed out

What is weird, that the current database address is different than 9.142.60.187. sqlnet.ora also does not contain any interesting data:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ? ADR_BASE = C:\app\Administrator\product\11.2.0\dbhome_1\log

How can I make my connection to sqlplus available? What is interesting, I am able to connect to the database using jdbc driver and it works fine.

Answer 1

Since tnsping clm is using EZConnect, your tnsnames.ora does not have an connection alias called CLM, but clm is being resolved as a host name to 9.142.60.187. It's using port 1521 by default as it has no instructions to use a specific port. The connection to that address, or at least that port, is then timing out - it might not exist, it might route somewhere unreachable, or it might be blocked by a firewall.

ping clm should show the same address, and will probably also time out. That host may exist in your hosts file, or is being resolved by DNS, possible with a default domain appended, which ping would also show. If it is the name of your old server, you might need to update your hosts file, which might magically make it start working.

If your database is actually called CLM then you can create an entry in your tnsnames.ora that points to the correct IP address for your listener, which your JDBC connection is already using. Or you can use the EZConnect format as your connect string; something like sqlplus user/password@//<your IP>:1521/<your service name>. You can use lsnrctl services to check the IP address, port and service name if you aren't sure.

If you're on the same host as the database then you don't need to do either; presumably you're currently trying sqlplus user/password@clm, but if ORACLE_SID is set you can just do sqlplus user/password and it will not trouble the listener.

Answer 2

Your sqlnet.ora is configured to first try to use TNSNAMES (tnsnames.ora in your TNS_ADMIN directed folder as defined in registry, environment variable or taken from default location) to resolve clm to a path through your network mapping SQL*Net to TCP/IP and possibly others.

From your tnsping that seems not to resolve. Therefore, SQL*Net tries the second one: EZCONNECT. See Oracle docs on that one.

Probably you are using somewhere an EZCONNECT connection string like user/pwd@//9.142.60.187 without service name and port. Or my knowledge of EZCONNECT is too simple; somehow it seems to translate clm into //9.142.60.187. That might be explained by the answer to the following question: Do you have a hostname clm in DNS or hosts file mapping to 9.142.60.187?

I do not recommend using EZCONNECT, I always like to have tnsnames.ora hard coded, specifying everything needed. EZCONNECT can work nonetheless.

Can you include IP address of clone in your sample?

Can you include outcome of ping clm and nslookup clm in your sample?

Answer 3

Change the ip address in (HOST=9.142.60.187) to the current ip address of server. May be in the JDBC driver you are using host name instead of ip address and that's why it is working. You can also try giving host name in (HOST=9.142.60.187), e.g if host name is ora_server then change it to (HOST=ora_server)